From ER model to database schema
Normalization has a reputation for being academic, which it earned by being taught as five numbered rules instead of as one question asked repeatedly: does this fact belong here?
11 min readER - crow's foot3 of 3
01The table everyone starts with
This is not a straw man - it is the shape a spreadsheet has, and a spreadsheet is where most schemas begin. It is worth being precise about what is actually wrong with it, because "it is not normalized" explains nothing:
- You cannot store a customer who has not ordered. The customer only exists as columns on an order.
- Correcting an email means updating every row that customer appears on - and missing one leaves the database holding two different answers.
- Deleting the last order deletes the customer. Information vanishes as a side effect of something unrelated.
product_namesholds a list. Now every query that needs one product is parsing a string, and no index can help.totalcan disagree with the lines it is meant to be the sum of.
Those five have names - insertion, update, and deletion anomalies, a repeating group, and a derived value - and normalization is simply the procedure that removes them.
02Choose the keys first
Everything downstream depends on this, so it comes before the tables do. A primary key must be unique, never null, and never change. The third condition is the one that eliminates most candidates.
| Element | Notation | What it means |
|---|---|---|
| Surrogate key | bigint or uuid | Generated, meaningless, stable forever. The default. A sequential integer is compact and index-friendly; a UUID can be generated by the client and does not leak volume. |
| Natural key | ISBN, IBAN, country code | Meaningful, and only safe when a standards body guarantees it will not change. A short list of these genuinely qualify. |
| Composite key | two or more columns | Right for junction tables, where the pair of foreign keys is the identity and also the uniqueness constraint you wanted anyway. |
| Business key | order number, SKU | Human-facing, must be unique, and should be a UNIQUE constraint rather than the primary key - so it can be corrected when it turns out to have a typo in it. |
03Normalization, in three steps
There are six normal forms and you need three. Each is a single question about a table, and the answer "no" tells you which column to move where.
| Element | Notation | What it means |
|---|---|---|
| First normal form | no repeating groups | Every column holds one value. No comma-separated lists, no product_1 / product_2 / product_3. Split the repeating part into its own table. |
| Second normal form | no partial dependencies | Every non-key column depends on the whole key. Only ever an issue with composite keys: a product name on an (order_id, product_id) table depends on half the key, so it belongs on products. |
| Third normal form | no transitive dependencies | No non-key column depends on another non-key column. A customer_email on an order depends on the customer, not on the order. Move it to customers. |
The old summary is still the best one: every non-key column depends on the key, the whole key, and nothing but the key.
Run the flat table through them. 1NF splits product_names and quantities into an order_lines table, one row per product. 2NFnotices that a product's name and price depend only on the product, so products appears. 3NF notices that customer name and email depend on the customer rather than the order, so customers appears. Four tables, which is the hero diagram - and no step required judgement, only the question.
04Derived values and denormalization
The total column in the flat table is a different problem from the other four: it is not badly placed, it is redundant. It can be computed from the order lines, so it can disagree with them, and eventually will.
Three legitimate answers, in order of preference: compute it in the query; compute it in a view; store it and have the database maintain it - a generated column, a materialized view, or a trigger. What is not legitimate is storing it and having application code remember to update it, which is the version that produces invoices that do not add up.
Reach for it when
- A measured read pattern is too slow and the join is provably the cause
- The value is a point-in-time snapshot, not a derivation - the price when ordered
- A reporting table fed by a scheduled job, clearly named as such
- The database can maintain the copy itself, so it cannot drift
Reach for something else when
- It might be slow later - measure first, and it usually is not
- Application code has to remember to keep the copy in step
- The duplicate is the source of truth for something else too
- You are denormalizing the transactional schema to serve a dashboard
05Modelling inheritance
An ER model can express a supertype with subtypes; a relational database has no such construct, so the physical model has to pick one of three layouts. All three are in wide use and the choice is a genuine trade.
| Element | Notation | What it means |
|---|---|---|
| Single table | one table, a type column | Every subtype's columns in one table, most of them null. Simple queries, no joins, and the database cannot enforce "a cheque payment must have a sort code". |
| Table per subtype | one table each, sharing a key | A parent table with the common columns and a child table per subtype, keyed to it. Constraints work properly; every query joins. |
| Table per concrete type | fully separate tables | No shared table at all. Fast and clean per subtype, and querying "all payments" means a union that grows every time a subtype is added. |
A rough rule: few subtypes with mostly-shared columns favours single table; many subtypes with divergent columns and real constraints favours table per subtype; and subtypes that are never queried together favour separate tables. The class diagram for the same domain will usually have picked inheritance without facing any of this, which is why the two models are allowed to differ here.
06Getting to DDL
A logical ER model maps onto DDL almost mechanically, which is the payoff for having drawn it properly:
| Element | Notation | What it means |
|---|---|---|
| Entity | CREATE TABLE | One table. Plural table name, singular entity name - pick one and hold. |
| Primary key | PRIMARY KEY | Implies not-null and unique, and creates the index. |
| Relationship | REFERENCES | A foreign key on the many side. Add the index yourself - most databases do not create one for a foreign key, and its absence makes deletes crawl. |
| Mandatory end | NOT NULL | The inner bar in crow's foot is exactly this constraint. |
| One-to-one | UNIQUE on the FK | Otherwise it is a one-to-many that happens to have one row so far. |
| Junction entity | composite PRIMARY KEY | Both foreign keys together. This is what prevents the same pair being recorded twice. |
Two things the diagram cannot tell you and the DDL must decide: what happens on delete (CASCADE for an identifying relationship, RESTRICT for almost everything else), and which columns get indexes beyond the keys. Both are decisions about behaviour and load, not about the model, and both are worth writing down next to the schema rather than discovering later in a slow query log.
07Common mistakes
- Normalizing past the point of usefulness. 3NF is the destination for a transactional schema. Splitting a table because a column might repeat one day buys nothing and costs a join forever.
- No foreign keys, "for performance". The cost is one index lookup on write; the benefit is that orphan rows become impossible. Almost never the right trade.
- Nullable columns standing in for a missing table. Six columns that are only populated for one kind of row are a subtype wanting its own table.
statusas free text. Constrain it - a check constraint, an enum, or a lookup table - or it will containshipped,ShippedandSHIPPEDwithin a year.- Timestamps without a time zone. Correct exactly once, in one office, until the first server moves.
- The diagram left behind after the first migration. A schema diagram that disagrees with the database is worse than none, because people trust it.
If the cardinality symbols in the diagrams above need decoding, they are covered in crow's foot notation; the shape of the model itself is in ER diagrams.
In one line each
- 01Pick the keys before the tables; unique, not null, and never changing.
- 02Every non-key column depends on the key, the whole key, and nothing but the key.
- 031NF splits repeating groups, 2NF fixes partial keys, 3NF moves misplaced facts.
- 04Denormalize on measurement, and only where the database maintains the copy.
- 05A historical price is not a duplicate - it is a different fact.
- 06Crow's foot maps onto DDL almost mechanically; index your foreign keys.
Related reading