Modeling data means deciding, before creating a single table, which pieces of information a system must keep, how they relate to one another and which rules they obey. This lesson is for anyone who can already read a few SQL queries and wants to design a simple relational model, then justify it. It applies to every engine, PostgreSQL as well as MySQL. The running example is an incident tracker for a server fleet. You will see entities and their cardinalities, keys, the first three normal forms, when to denormalize wisely, then what changes between the logical model and the physical model.
What you will learn
- Break a requirement down into entities, attributes and relationships, with their cardinalities
- Read and produce an entity-relationship diagram
- Choose a primary key and say what a foreign key guarantees
- Spot a 1NF, 2NF or 3NF violation and fix it
- Decide to denormalize, knowing the price of that choice
- Tell which parts of a model depend on the engine
What are the prerequisites, and where is the SQL written?
The SQL training (in French) is the prerequisite: you know what a table, a row and a column are, and you have already read a schema with Reading the schema of a PostgreSQL database (in French). The schema of the servers and alerts tables you read there is the result of the same reasoning, on the same infrastructure domain, with English identifiers. This lesson writes no SQL syntax: it teaches how to design a model, while the SQL training teaches how to write the tables, notably in Constraints and schema evolution in SQL (in French).
What is a data model, and why not start with the tables?
A data model describes the information a system keeps, its links and its rules, independently of any storage. In a relational database (see Relational databases, in French), this information is organized in tables where each row describes one thing and each column one of its properties. The model answers "what must be kept, and how does it hang together?" before "how do I write it?".
Creating the tables first is tempting, but a design mistake is paid for afterwards in every query, every screen and every migration (a structure change on a database that is already populated). Evolving a full table is an operation that locks, that fails if the data violates the rule being added, and that the lesson Constraints and schema evolution in SQL (in French) measures. A model reviewed before the first record costs a discussion; the same flaw discovered in production costs a migration.
How do you spot entities, attributes and relationships?
Start from a sentence of the requirement: "each incident affects a server, is handled by a technician and may degrade several applications". The nouns point to candidate entities, the properties become attributes, the verbs become relationships.
- An entity is a category of things you want to track, with its own existence and several occurrences: the server, the incident, the technician.
- An attribute is a property of an entity: a server's hostname, an incident's opening date.
- A relationship (or association) is a link between entities: a technician handles incidents.
A test tells an entity from an attribute: if the thing has its own properties or is shared by several others, it is an entity. A server's "site" is just a text as long as you only keep its name; as soon as you need its city or address, several servers attach to it and it becomes a separate entity.
Which cardinalities between two entities?
The cardinality says how many occurrences on one side attach to one occurrence on the other. It is asked as two questions, one per direction, and its minimum matters as much as its maximum: "how many servers does a site host?", then "how many sites host a server?". A server without incidents is normal (minimum zero), an incident without a server is not (minimum one).
| Type | Fleet example | Where the link is stored |
|---|---|---|
| one-to-one | a server and its remote management card (BMC, the baseboard management controller, which lets you administer it without being on site) | a foreign key on one side, made unique |
| one-to-many | a site hosts servers; a server suffers incidents | a foreign key on the "many" side |
| many-to-many | an incident affects several applications, an application suffers several incidents | a junction table |
Two terms come up constantly and deserve a definition before going on. A primary key is the column, or group of columns, that identifies each row of a table. A foreign key is a column that repeats the primary key of another table to designate one of its rows: this is how the engine remembers a link. The section on keys covers them in detail.
The one-to-one is rare: it isolates sensitive or rarely read columns. If two entities are always born and die together, a single table is usually enough.
Why does a many-to-many relationship require a junction table?
A many-to-many relationship fits in neither of the two tables: writing the list of applications in one cell, or repeating columns application_1, application_2, violates the first normal form, defined below. The junction table solves this cleanly: one row per pair (incident, application), with a composite primary key made of both columns, each also being a foreign key.
It is therefore not a pointless complication: it is the shape that can be queried in both directions and that holds the attributes of the link itself, such as the severity of the impact, which belongs neither to the incident nor to the application.
| incident | application | severity |
|---|---|---|
| 101 | nginx | major |
| 101 | php-fpm | minor |
| 103 | postgresql | major |
How do you read and produce an entity-relationship diagram?
An entity-relationship diagram draws entities as boxes, relationships as lines and their cardinalities as labels. It is read relationship by relationship, out loud, in both directions: "a site hosts zero to N servers; a server depends on exactly one site". Each label in the diagram gives the minimum and maximum on the "many" side, such as "0 to N": a site may host no server, and an incident may be opened before we know which applications it affects. The "one" side is read in the foreign key, always filled in in this model: each server has exactly one site.
Several notations exist (Merise, crow's foot, UML) and say the same thing; the diagram on this page writes the relationship and its bounds in plain words, and consistency matters more than the notation: pick one and stick to it.
In the diagram below, each box is a table with its columns; the PK and FK markers flag primary keys and foreign keys. Each line goes from the "one" side to the "N" side. Look at the impacts table: its first two columns are both PK and FK, it is the junction table between incidents and applications.
To produce one, proceed in four steps:
- List the nouns of the requirement and keep those that have their own properties: these are the entities.
- List their attributes and find the one that identifies each occurrence.
- Ask the two cardinality questions for each verb linking two entities.
- Translate: foreign key on the "many" side, junction table for each many-to-many.
Which keys to choose: primary, candidate, foreign?
A candidate key is a minimal set of columns that identifies each row: for a server, the hostname or the serial number will do. One is chosen as the primary key, which must be unique, never empty and stable over time. The foreign key, defined above, is what materializes a one-to-many relationship.
The next diagram shows the mechanics on another domain, customers and their orders. Look at the client_id column of commandes: it is the foreign key, and it can only contain values of the primary key id of clients.
Natural key or surrogate key?
A natural key comes from the real world (the hostname), a surrogate key (or technical key) is a generated number that means nothing. Neither wins everywhere: the real trade-off is in this table, on stability and readability.
| Natural key (hostname) | Surrogate key (generated identifier) | |
|---|---|---|
| Readable in related tables | yes | no, a join is needed |
| If the server is renamed | all foreign keys must be updated | nothing changes |
| Size | variable, often large | small and fixed |
| Specific risk | the business rule of uniqueness may change | duplicates slip through if uniqueness of the name is not also enforced |
Common practice combines both: a surrogate key as primary key for its stability, and a uniqueness constraint on the natural key to keep the business rule. The surrogate key does not excuse you from looking for what really identifies a thing.
What is referential integrity for?
Referential integrity is the guarantee, enforced by the engine, that each foreign key value designates an existing row (or stays empty when the relationship is optional). It only holds if the foreign key is declared to the database: without a declaration, the column is just a number that nothing checks.
Without it, orphan rows appear: an incident that points to a server removed from the inventory. Inner joins (in French) then drop these rows without an error, and reporting figures become wrong without any alert going off. A check in the application is not enough: an import script, a second application or two concurrent sessions bypass it, whereas the engine is the mandatory checkpoint.
One modeling decision remains: what to do with the dependent rows when the referenced row is deleted? The three common answers are refuse, delete in cascade or set to empty. For the incident history, we refuse: deleting a server must not erase its trace. How to write this choice is detailed in Constraints and schema evolution in SQL (in French).
What is a normal form?
A normal form is a design rule that forbids one specific type of redundancy; normalization means splitting tables until they comply. Redundancy is the same fact written in several places, and it causes three anomalies: update (a forgotten copy), insertion (impossible to record one fact without another) and deletion (a fact lost when erasing another). The forms are cumulative: 2NF assumes 1NF, 3NF assumes 2NF.
One reading tool serves all three: a column has a (so-called functional) dependency on another if knowing the second determines the first; the number of an incident thus determines its server. Each column must depend on "the key, the whole key and nothing but the key". 1NF checks the first point, 2NF the second, 3NF the third. Other forms exist (BCNF, 4NF) for rare cases; the first three are enough for an ordinary model.
All the examples start from this flat tracking table, where each row is an incident:
| num_incident | serveur | site | ville_site | applications | technicien | equipe |
|---|---|---|---|---|---|---|
| 101 | web-01 | DC-Lyon | Lyon | nginx, php-fpm | Awa Diallo | Systems |
| 102 | web-01 | DC-Lyon | Lyon | nginx | Awa Diallo | Systems |
| 103 | db-02 | DC-Paris | Paris | postgresql | Karim Benali | Databases |
1NF: one value per cell
The first normal form requires an atomic value per cell (no list), no group of repeated columns (such as application_1, application_2, application_3, which set an arbitrary limit) and identifiable rows. The applications column violates it with "nginx, php-fpm". Consequence: "which incidents affected php-fpm?" forces you to dig through text, "nginx, php-fpm" and "php-fpm, nginx" differ for the engine, and nothing guarantees that each name designates a real application.
The fix gives one row per pair (incident, application), and the composite key becomes (num_incident, application). For each name to designate a real application, these names are also stored in an applications table that the pair references; the table keeps the name in plain text to stay readable:
| num_incident | application | serveur | site | ville_site | technicien | equipe |
|---|---|---|---|---|---|---|
| 101 | nginx | web-01 | DC-Lyon | Lyon | Awa Diallo | Systems |
| 101 | php-fpm | web-01 | DC-Lyon | Lyon | Awa Diallo | Systems |
| 102 | nginx | web-01 | DC-Lyon | Lyon | Awa Diallo | Systems |
| 103 | postgresql | db-02 | DC-Paris | Paris | Karim Benali | Databases |
2NF: the whole key
The second normal form requires every non-key column to depend on the whole key; it only comes into play with a composite key, made of several columns. Here serveur, site and technicien depend on num_incident alone, that is, on part of the key: this is a partial dependency. Incident 101 repeats web-01 on two rows; fixing only one would leave an incident on two servers at once. Two other anomalies hide here: you cannot record an incident that affected no application, since the application is part of the key, and erasing the last application of incident 101 would also lose its server and its technician. The insertion anomaly cannot be worked around: no application-side discipline can record a fact that the structure refuses.
You separate what depends on the incident (serveur, site, ville_site, technicien, equipe) from the list of affected applications, which keeps its key pair. A table whose key is a single column complies with 2NF as soon as it complies with 1NF. The incidents table becomes:
| num_incident | serveur | site | ville_site | technicien | equipe |
|---|---|---|---|---|---|
| 101 | web-01 | DC-Lyon | Lyon | Awa Diallo | Systems |
| 102 | web-01 | DC-Lyon | Lyon | Awa Diallo | Systems |
| 103 | db-02 | DC-Paris | Paris | Karim Benali | Databases |
3NF: nothing but the key
The third normal form forbids a non-key column from depending on another non-key column, which is called a transitive dependency. Here serveur determines site then ville_site, and technicien determines equipe: these facts concern the server and the technician, not the incident. All three anomalies appear:
- update: Awa Diallo moves to the Network team, and each of her rows must be corrected, otherwise they contradict each other;
- insertion: impossible to record the new server db-03 in DC-Paris as long as no incident concerns it;
- deletion: erasing incident 103 makes the fact that db-02 is in Paris disappear.
Each fact is written only once by splitting into incidents, servers, sites, technicians, applications and impacts. Look at the diagram: on the left the flat table, on the right the result, and on each arrow the normal form(s) that caused the target table to be split off.
These are the six tables of the entity-relationship diagram above, which only adds the incident's opening date and the impact's severity. The columns change names along the way: num_incident becomes the identifier of incidents, serveur the nom of servers, site and ville_site the nom and the ville of sites, and each foreign key (site_id, serveur_id, technicien_id) replaces the copied value.
Should you always aim for 3NF? Denormalization as a decision
No: 3NF is the default starting point, because it guarantees that a fact exists only once, but denormalizing, that is, deliberately reintroducing redundancy, remains a legitimate choice. It is neither a fault nor a trick: it is a trade-off you decide on knowing both sides of the scale.
| What denormalization buys | What it costs |
|---|---|
| Fewer joins, a more direct read | Every copy must be updated everywhere, otherwise the values diverge |
| Precomputed values (number of incidents per server) | Heavier writes, code or triggers (routines run by the engine on every write) to keep them in sync |
| Simpler reporting queries | The return of update, insertion and deletion anomalies |
One case is not a denormalization: the technician's team at the time of the incident is a historical fact, distinct from their current team. Storing it in the incident copies nothing, it keeps a different fact.
Denormalize when reads dominate and a measured join is the bottleneck, never on principle. Try a suitable index first, that is, a side structure that speeds up searching on a column (see Indexes and EXPLAIN, in French), or a materialized view, which stores the result of a query and which some engines offer. If you do denormalize, name the source of truth, the copy and the resynchronization mechanism; updating both in the same transaction, committed entirely or not at all, prevents one copy from changing without the other (see Transactions and concurrency, in French). NoSQL databases (in French) push this logic further: few or no joins, data stored for the intended read.
Logical or physical model: what depends on the engine?
A model is built at three levels, from the most abstract to the closest to the engine. The conceptual model says which entities and relationships exist, in the vocabulary of the business. The logical model turns them into normalized tables, columns and keys, without choosing an engine. The physical model adds everything that depends on the chosen engine.
| Level | Contains | Engine-dependent? |
|---|---|---|
| Conceptual | entities, relationships, cardinalities | no |
| Logical | tables, columns, keys, normal forms | no |
| Physical | exact types, indexes, partitions, storage | yes |
The entity-relationship diagram of this lesson is a logical model: the types it shows (int, varchar) are indicative. Moving to the physical level means settling points specific to the engine: the exact type of a date with time zone, how to generate the identifier, the index types available, partitioning (cutting a big table into pieces) and storage. To choose the engine, see Choosing a relational engine.
An index, seen above, speeds up reads but every write must also update it: it has a write cost. So you index what queries search for, foreign key columns included (PostgreSQL 18, MySQL 8.4): MySQL (InnoDB) creates one only if no usable index already exists on that column, PostgreSQL never creates one on the referencing column (the primary key of the referenced table is always indexed). The how-to is detailed in Indexes and EXPLAIN (in French).
Key takeaways
- A model is designed before the tables: a design mistake is paid for in every query and every migration.
- The nouns of the requirement give the entities, the verbs the relationships; two cardinality questions, one per direction.
- A many-to-many goes through a junction table, which also holds the attributes of the link.
- A surrogate key brings stability; the natural key keeps its uniqueness constraint.
- Referential integrity prevents orphan rows, but only if the foreign key is declared to the engine.
- To spot a violation: 1NF one value per cell, 2NF the whole key, 3NF nothing but the key.
- Denormalizing is a trade-off: simpler reads against consistency and costlier writes, to be decided on measurements.
- The logical model does not depend on the engine; types, indexes and partitions belong to the physical one.
Knowledge check
Ten questions on turning an attribute into an entity, the minimum of a cardinality, the junction table, the choice between a natural key and a surrogate key, what referential integrity guarantees, the three normal forms, the price of denormalization and the border between the logical and the physical model.
Knowledge check
Check what you have learned with this interactive quiz
How it works
- The timer starts when you click Start
- Multiple choice, true/false and short answer questions
- You can move between questions
- Detailed results are shown at the end
Starts the quiz and the timer
Review
(0/0)Score by domain
A domain groups several guides and gets several questions, so its score is more reliable than a per-topic score, which often rests on a single question.
Summary
Strengths
No topic passed at 80% on at least two questions yet.
Weak areas
No topic below the pass mark.
Recommended next
Hints before you try again?
Continue the course: Transactions: what ACID and BASE promise
A new full quiz with random questions
Work again only on the questions you missed