Data Modeling

Entities, keys and the shape of a relationship

Modelling starts with one question asked repeatedly: what are the things this product talks about, and what is true of each one 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.

A key is the promise that a row can be identified. There are two kinds and the choice matters more than it looks. 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 an identifier the database made up, meaningless outside the system. Natural keys read well and save a join, and they have one failure mode that recurs everywhere: business identifiers change. People change email addresses, companies get re-registered, a country reissues its identifier format. When that value is also the key, every row that referenced it has to change with it.

The workable default is a surrogate primary key plus a unique constraint on the natural one — the identity is stable, and the business rule is still enforced. Two details are worth deciding rather than inheriting. A sequential integer is compact and fast but leaks how many records exist and how fast they grow, which matters if the value appears in a URL. A random identifier hides that and lets a client generate an id before the row exists, at the cost of size and, for fully random ones, index locality; the time-ordered variants exist precisely to get both.

Each normal form removes one specific anomaly

Normalisation has a reputation as academic ceremony, and that reputation comes entirely from being taught as a ladder to climb rather than as three named problems to avoid. Each form removes one specific way that 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.

First normal form says each cell holds one value and there are no repeating groups. A tags column containing "urgent, billing, vip" looks harmless and cannot be filtered, indexed, counted or renamed — every operation on it becomes string manipulation, and "billing" matches "rebilling" unless you are careful in a way nobody stays careful. The fix is a row per value. 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 which breaks the day somebody has four.

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 — of which one 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 they move, 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 distinction is whether you can say what it bought. Copying a value to avoid a join is worth it when the read is hot, the value rarely changes and something keeps the copy honest — a trigger, a scheduled reconciliation, or the fact that the copy is supposed to be frozen. A stored total on an order is the classic legitimate case: it is not a cached sum of the lines, it is the amount the customer agreed to, and it must not change when a price does. The illegitimate case looks identical and has no mechanism keeping it true.

Types and nulls are modelling decisions

A type is the cheapest constraint there is, and choosing it well makes whole categories of bug unrepresentable. The reliable instinct is to pick the narrowest type that can hold every legal value and no illegal one, and to resist the temptation to store everything as text because text always fits. Text always fits, and it also accepts "N/A", "unknown", " 12,50 " and an empty string, all of which somebody will eventually put there.

Null deserves its own paragraph because it is not a value — it is the absence of one, and it propagates. A comparison with null is neither true nor false but unknown, so a row with a null in the column never matches = x and never matches <> x either; a sum skips it, a count of that column skips it, and a unique constraint in most engines lets you have any number of nulls. None of that is a bug, and all of it surprises people at least once.

So 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 at the moment the row is created. Everything else is a modelling problem in disguise. A group of columns that are null together — cancelled_at, cancelled_by, cancellation_reason — is a state that belongs in its own table or its own enumerated status. And a column that is null because it applies to only some kinds of row means you have two entities sharing a table.

How this shows up in real delivery

The request that catches most schemas out is "what did this look like in March". A table that stores the current state answers questions about now and nothing else, and the history is not recoverable afterwards — it was never written down. Decide early which entities need history, because retrofitting it means an apology rather than a migration. The cheap version is an append-only log of changes beside the current row. The full version stores a validity period on each version and asks every query which moment it means, which is more honest and more work.

Two kinds of date get conflated and should not be: when something happened in the world, and when your system learned about it. A payment made on Friday and imported on Monday has both, and every reconciliation, report and audit question depends on knowing which one is being asked about. Storing only one of them is the reason a monthly total changes after the month closed and nobody can explain why.

Soft deletes are worth naming as a decision too, because they are usually adopted 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 policy — rather than in every query. And remember that a soft delete is not a deletion in the legal sense, so a system with a real erasure obligation needs a genuine one anyway.

Finally, treat the schema as source code that other people read. Names are the interface: status on three tables meaning three different sets of values is a daily cost, and a column called flag is a mystery in 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 every one of them reversible or explicitly marked as not.

Where it degrades

• A schema shaped around the current screen, which has to be migrated the first time the screen changes.
• A comma-separated list in a column, which no index, filter or count can work with.
• A natural key used as the primary key, until the business changes the value it is made of.
• Money in a floating-point column, which is a rounding incident waiting for enough transactions.
• Timestamps without a zone, which are ambiguous twice a year and wrong across regions.
• Nullable columns added by default, so every read path carries a branch nobody tested.
• A denormalised copy with nothing keeping it honest, which diverges silently and is trusted anyway.
• One table serving two entities, distinguished by a type column and a set of columns that are null.
• Only the current state stored, so the first question about last quarter has no answer at all.

When to use it

Related pages

• DevelopmentSQLSQL is the language you use to ask a relational database for something, and the first thing to understand is that a relational database is not a place to put data. It is a component that enforces guarantees your application cannot enforce on its own: a schema the engine checks on every write, transactions that either happen completely or not at all, and a planner that decides how your query actually runs. This page covers what the planner does with an index, what each isolation level really permits, why constraints belong in the database, and the few mistakes that turn a fast query slow at scale.
• DevelopmentNoSQLNoSQL is an umbrella term that names four different kinds of database by what they are not, which is why the choice is so often made badly. What they share is more useful than what they lack: data is placed on a node by a key you choose, so that key decides which queries are cheap, which are expensive and which are impossible. This page covers the four families, what a partition key really commits you to, what "eventually consistent" means for your users, and when a relational database is still the right answer.
• DevelopmentAPI DesignAPI design is the work of shaping the surface other people build against. The useful frame is a single line: above it is the contract, which consumers depend on and you cannot change alone; below it is implementation, which you can rewrite on a Tuesday. Most of what makes an API painful to live with is a decision that ended up on the wrong side of that line.