Data Modeling

Sixteen questions for every table — the answer that holds up, and what the wrong one costs

Nobody opens a page about data modelling for the theory. They open it because a schema is about to be written, or because one already hurts: a report that cannot be built, a migration that takes a weekend, a number nobody can explain. Almost all of that pain goes back to a question that was never asked of some table. Here are sixteen of them, with the answer that holds up and what the wrong answer costs a year later. Later sections cite them by number.

From sentences to tables — entities, keys and relationships

A model is usually drawn three times, at three levels of detail, and many schema arguments are two people talking about different levels. The split goes back to the ANSI/SPARC committee’s three-schema architecture of 1975. The diagram most people draw at the first level comes from Peter Chen’s entity-relationship model of 1976.

Modelling starts with one question asked over and over: what are the things this product talks about, and what is true of each of them exactly once? A customer has one email address and many orders. An order has one total and many lines. Getting this list right is most of the work, and the reliable way to find it is to write down the sentences the business actually says out loud. "An invoice can cover several deliveries" is a many-to-many relationship stated in plain language, and it will still be true after three rewrites of the application. Row 1 is the same test applied to one table: if you cannot say what one row is in a sentence, the table holds two things.

The diagram uses crow’s-foot notation, which Gordon Everest introduced in 1976 and most tools draw today. Each end of a line says how many of that entity can stand there. A bar means one, a circle means zero, and the three-pronged foot means many, so a circle with a foot reads "zero or more" and two bars read "exactly one". Read it as a sentence, from one box along the line to the marks at the far end: a customer places zero or more orders; an order belongs to exactly one customer. The order line in the middle is row 5 made visible. An order holds many products and a product appears on many orders, and the table that links them turned out to be an entity of its own, with a quantity and a price.

A key is the promise that a row can be identified, and there are two kinds. A natural key is a value the business already uses — an email address, a tax number, an ISBN. A surrogate key is a number or identifier the database made up, meaningless outside the system. Natural keys read well and save a join, and they share one failure that recurs everywhere: business identifiers change. People change email addresses. Companies re-register. Books went from ten-digit to thirteen-digit ISBNs on 1 January 2007, and every system that used the ISBN as its key had to change every reference to every book. The workable default is row 2: a surrogate primary key, plus a unique constraint on the natural one, so the identity is stable and the business rule is still enforced.

Four shapes look flexible and are not. Bill Karwin named them in "SQL Antipatterns" (2010), and every long-lived schema seems to grow at least one:

Each normal form removes one specific anomaly

Normalisation has a reputation as academic ceremony, and the reputation comes entirely from being taught as a ladder to climb rather than as named problems to avoid. Edgar Codd defined the first normal form in 1970, in the paper that founded the relational model, and the second and third in 1971. Each one removes a specific way data goes wrong when the same fact is stored in more than one place. Learn the anomaly and the rule follows. Learn the rule alone and you will apply it where it does not help.

The anomalies come in three kinds, and one wide order table shows all of them. An update anomaly: a fact stored in many rows is changed in some of them — a product is renamed on three hundred order lines and missed on the three hundred and first. An insertion anomaly: a fact cannot be recorded until an unrelated one exists — a new product cannot be added until somebody orders it, because products only live on order lines. A deletion anomaly: removing one fact removes another — delete the last order of a product and its name and price go with it.

First normal form says each cell holds one value and there are no repeating groups. A tags column holding "urgent, billing, vip" looks harmless and cannot be filtered, indexed, counted or renamed: every operation on it becomes string manipulation. The same rule rules out the other shape of repeating group, phone_1, phone_2, phone_3, which is a list pretending to be columns and breaks the day somebody has four. The fix in both cases is a row per value.

Second normal form matters only when a table is keyed by more than one column, and it says no field may depend on just part of that key. A line-item table keyed by order and product should not carry the product name. The name depends on the product alone, so it is repeated on every order that ever included it, and renaming the product means updating thousands of rows, one of which will be missed. Third normal form extends the same logic to fields that depend on another non-key field. A customer’s city stored on the order depends on the customer, not on the order, so when the customer moves, the old orders quietly claim they were placed from the new city.

Denormalisation is then a deliberate, reversible optimisation rather than a failure to normalise, and the difference is whether you can say what it bought and what keeps it true (row 12). Some copies are not even redundancy, because they record something different from the source. A price on an order line is the price the customer paid, not the product’s price, and it must not change when the catalogue does (row 13). The illegitimate copy looks identical and has no mechanism keeping it honest:

Types, nulls and absence — the cheapest constraints there are

A type is the cheapest constraint there is, and a well-chosen one makes whole categories of bug impossible to store. The reliable instinct is to pick the narrowest type that can hold every legal value and no illegal one, and to resist storing everything as text because text always fits. Text does always fit. It also accepts "N/A", "unknown", " 12,50 " and an empty string, and somebody will eventually put each of them there.

The name row deserves a sentence of its own. In 2010 Patrick McKenzie published "Falsehoods Programmers Believe About Names", a list of forty assumptions that real names break: that everyone has exactly one first and one last name, that names do not change, that they fit in a fixed number of characters, that they are written in one script. Each of those assumptions is a column definition somewhere. A model that stores the name as the person writes it, and separately how they want to be addressed, survives all forty.

NULLNULLSQL’s marker for “unknown”The SQL marker for a value that is missing or unknown — not zero and not an empty string. A comparison with NULL is neither true nor false but unknown, so a row holding NULL matches neither “= x” nor “<> x”, and only IS NULL finds it.Full article →All terms needs its own paragraph because it is not a value. It is the absence of one, and it spreads. A comparison with NULL is neither true nor false but unknown, so a row with NULL in a column matches neither = x nor <> x; a sum skips it; and a unique constraint in most engines accepts any number of NULLs. The SQL page has the full set of surprises. For the model, the point is row 9: make each nullable column a decision with a reason. There are only two good ones — the value is genuinely optional in the business, or it is not known yet when the row is created. A group of columns that are NULL together — cancelled_at, cancelled_by, cancellation_reason — is a state that belongs in its own table or status. And a column that is NULL because it applies to only some kinds of row means two entities share a table, which is row 1 again.

Time and history — what it was, and when you knew

The request that catches most schemas out is "what did this look like in March". A table that stores only the current state answers questions about now and nothing else, and the history cannot be recovered afterwards, because it was never written down. That is why rows 14–16 are the only ones in the table that no later migration can fix. Decide early which entities need history. There are five ways to keep it, and they answer different questions:

Two kinds of date are routinely confused, and row 15 is about keeping them apart: when something happened in the world, and when your system learned about it. Richard Snodgrass’s 1999 book on time-oriented applications in SQL calls them valid time and transaction time. A payment made on Friday and imported on Monday has both. Every reconciliation, report and audit question depends on knowing which of them is being asked about. Storing only one is the reason a monthly total changes after the month has closed and nobody can explain why.

The diagram follows one payment through a week. A customer paid 102 on Friday 3 October. The payment reached the system on Monday the 6th, keyed in as 120, and on Wednesday the 8th somebody corrected the typo. Three reports for that Friday give three answers. Run on Sunday, it has no payment, because the system did not know yet. Run on Tuesday, it shows 120. Run on Thursday, it shows 102. All three were right about what the system knew when they ran. A model that stores both times can reproduce each of them on demand, which is what an auditor asks for. A model that overwrites can only ever give the last one, and has no way to explain why last week’s report said something else.

Deletion is the third time question (row 16), and it is usually decided by reflex. A deleted_at column keeps history and makes an accidental deletion recoverable, and it charges a tax on every query in the system forever: one missed filter shows removed data to a user. If you use it, enforce it in one place — a view, a repository, a row-level policy — rather than in every query. A soft delete is also not a deletion in the legal sense. The GDPRGDPRGeneral Data Protection Regulation — EU personal-data lawThe European regulation governing personal data: what you may collect, on what legal basis, how long you may keep it, and what a person can demand you do with theirs. It applies by where the people are, not by where the company is, which is why it reaches software built anywhere.All terms gives people the right to have their personal data erased (Article 17) and limits how long it may be kept (Article 5). So decide per entity: a hard delete for what nobody needs; a soft delete for user mistakes; anonymisation — keep the order, remove the person — where the business needs the record but not the identity; and a retention period after which even soft-deleted rows go. Backups count as well. An erasure that leaves the data in last month’s backup needs a documented answer for the day that backup is restored.

Models built to be read — star schemas, grain, and the schema as code

Everything above is for a system of record, where what matters most is that every write is correct. A model built to answer questions across millions of rows makes the opposite trade: it repeats data freely, so reads are simple and fast. The most widely used way to build one is Ralph Kimball’s dimensional modelling, from "The Data Warehouse Toolkit" (1996). It has four steps. Choose the business process — sales, shipments, payments. Declare the grain. Identify the dimensions, the things you filter and group by. Identify the facts, the numbers you add up. The second step is the one that matters most and is skipped most often.

The grain is the sentence that says what one row of the fact table is: one product on one receipt, one account’s balance at the end of one day. It is row 1 of the sixteen, applied to analytics. Every number in the fact table must be true at exactly that grain. Mixing grains double-counts — a daily total stored beside line items gets summed together with them. Spreading a coarse number over a finer grain is worse, because it invents data. An annual figure divided by twenty-six looks exactly like fortnightly income, and it is not. The Robodebt case below is that sentence written into a government programme.

The schema as code that other people read

Whichever shape you choose, treat the schema as source code that other people read. Names are the interface. A status column on three tables meaning three different sets of values costs something every day, and a column called flag is a mystery within eighteen months. Pick one convention for singular or plural table names, one for timestamps, one for foreign keys, and write it down, so the tenth table matches the first. Keep migrations in the repository, reviewed like any other change, and make each one reversible or explicitly marked as not. The SQL page covers how to change a live schema without locking it; the rule here is about meaning.

Never give an old column a new meaning. A column whose meaning changed in place is read by code, reports and downstream consumers that still believe the old meaning, and none of them fails loudly. The Risk Management page tells the most expensive version of this: Knight Capital reused a flag for a new feature in 2012, one server still ran the code that read the old meaning, and the firm lost about $460 million in 45 minutes. When a meaning changes, add a new column with a new name, move the readers over, and drop the old one — and remember that the readers now include whoever consumes your tables through change data capturechange data capturestreaming changes from a database logStreaming every insert, update and delete from a database’s own change log to other systems, as tools such as Debezium do. It keeps a second copy in sync without the application writing twice, at the price of coupling every consumer to the source’s table layout.All terms, who depends on their shape as much as your own code does.

Five cases, and what each one was missing

None of these needed a clever attacker or an exotic bug. Each is one modelling decision, made by default, meeting the world.

Basecamp is the case for row 3. At 7:21 a.m. Central time on Thursday 8 November 2018, Basecamp 3 stopped accepting changes. Its events table records almost every action in the product, from posting a message to applauding a comment, and its key was a 32-bit integer that had just reached 2,147,483,647. Every action that tried to record an event failed, so the whole product went read-only. Changing the column to a 64-bit integer on a table that large was estimated at an hour and forty minutes, and in all the product was read-only for almost five hours, until 12:11 p.m. The detail David Heinemeier Hansson put in the postmortempost-mortema written review after a failureA written review held after something went wrong, covering what happened, why, and what changes so it does not happen the same way. The word "blameless" in front of it is not politeness: an engineer who expects to be blamed reports less, and a review that produces a lesson rather than a change to a system has produced nothing.All terms himself is the sharpest one. Ruby on Rails, which Basecamp created, had made 64-bit keys the default in version 5.1 in 2017, and the team had been working on this very table in the days before — they had misjudged how fast the headroom was running out. A key’s type is a capacity limit, and capacity limits need an alert long before they need a migration.

The gene-name case shows a type chosen by a tool rather than by anyone. In 2016 Mark Ziemann, Yotam Eren and Assam El-Osta scanned the supplementary spreadsheets of published genomics papers and found gene names silently converted in 987 files from 704 articles — about a fifth of the papers that shipped gene lists in Excel. SEPT2 had become 2 September, MARCH1 had become 1 March, and some identifiers had become floating-point numbers. The spreadsheet guessed a type from what the value looked like, and guessed wrong. A follow-up in 2021 found the share had risen to 30.9%. The fix that finally worked came from the other side. In 2020 the HUGO Gene Nomenclature Committee renamed 27 genes — SEPT1 became SEPTIN1, MARCH1 became MARCHF1 — partly so that spreadsheets would leave them alone. A whole scientific field changed its natural identifiers because tools kept mistyping them. That is rows 2 and 6–10 at once: an identifier that looks like a number or a date is text, and natural identifiers do change.

Where it degrades

Data models rarely fail on the day they are designed. They fail the first time the world does something the model did not allow for. Almost every such failure is one of these:

• A schema shaped around the current screen, which has to be migrated the first time the screen changes.
• A table that holds two entities, told apart by a type column and a set of columns that are NULL (row 1).
• A natural key used as the primary key, until the business changes the value it is made of (row 2).
• A 32-bit key on a table that counts events, with no alert on how much headroom is left (row 3).
• Money in a floating-point column, and timestamps without a zone (rows 6–7).
• Nullable columns by default, and stand-in values that mean “unknown” to some readers and nothing to others (row 9).
• A denormalised copy with nothing keeping it honest, which drifts silently and is trusted anyway (row 12).
• Only the current state stored, so the first question about last quarter has no answer (row 14).
• A fact table with no declared grain, so totals double-count or spread one number over rows it never described.
• An old column given a new meaning, read by code and reports that still believe the old one.

The honest limit is that a model can only enforce what someone understood. Normal forms remove redundancy, constraints refuse impossible rows and types refuse impossible values, but none of them can tell that a correct-looking number was invented, or that "one year" was computed by hand, or that a value two systems both call NULL means two different things. Those are decisions about meaning, made by people who know the business, and the schema is only where they are written down. A model that nobody can explain in sentences is not a model yet, however normalised it is.

The practical check takes an afternoon. For the ten largest tables, write one sentence saying what a row is; any table that needs two sentences is a finding. List every 32-bit key and its current maximum against 2,147,483,647. Search the schema for float columns that hold money and timestamps without a zone. Count the nullable columns and ask, for each, which of the two good reasons applies. And ask the finance team what question about the past they could not answer last quarter.

When to use it

Related pages

• DevelopmentSQLSQL is the language you use to ask a relational database for data — and to rely on guarantees your application cannot provide on its own. You describe the result, a planner picks the route, and transactions decide what concurrent requests can see. The page opens with sixteen surprises — wrong rows, slow queries, lost updates — with the rule behind each and the fix, then covers how a query is evaluated, the planner and indexes, transactions and isolation, constraints and schema changes, running the engine in production, five documented cases, and where it degrades.
• DevelopmentNoSQLNoSQL is the umbrella name for databases that do not store relational tables queried with SQL: key-value, document, wide-column and graph stores. Most of them place each record on a node by a key you choose, and that key decides which queries are cheap, which are expensive and which are impossible. The page opens with sixteen things a relational database did for you and what you do instead, then covers the four families, choosing the partition key, replicas and conflicts, modelling for access patterns, running one in production, five documented cases, and where it degrades.
• DevelopmentAPI DesignAPI design is the work of deciding what other programs may send to yours, what comes back, and which of those promises you will keep for years. Anything a consumer can observe becomes part of the promise, and you cannot take it back alone. The page opens with sixteen decisions every HTTP API makes, the default to take and what changing each later costs, then covers what the contract includes, methods, status codes and errors, behaviour under retries and load, who may do what, changing a published API, five documented cases, and where it degrades.