Database design for beginners: three ideas that prevent pain

The usual introduction to database design opens with first, second and third normal form, and it is a strange way to teach something that is mostly common sense wearing formal clothes. Underneath the vocabulary there are three ideas. They can be stated in a sentence each, and almost every painful schema I have seen broke one of them early and paid for it slowly.

One fact belongs in one place

If a customer's phone number is stored on the customer row and also copied onto every order they placed, you do not have one phone number. You have as many as they have orders, and they will disagree the first time one is updated. There is no clever query that resolves that, because the database has no way to know which copy is the true one.

The rule is that a fact is stored once and referred to everywhere else. This is what most of normalisation is actually about, and stating it this way makes the exceptions easier to reason about later, because you can ask what specifically you are trading away.

The failure mode is familiar to anyone who has watched a spreadsheet grow past its useful life, where the same customer name appears in four columns with three spellings. We wrote about the wider version of that in what to keep and what to drop in an Excel to web app migration.

Every row needs an identity the database enforces

A row needs a way to be pointed at that will not change and cannot be duplicated. PostgreSQL describes it precisely: "A primary key constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null." That is from the PostgreSQL documentation on constraints, and every serious database says a version of the same thing.

The common beginner mistake is using something real as the identifier: an email address, a phone number, a national ID, an invoice reference from another system. Real things change. People change email addresses, companies reissue references, and a value that was unique when you designed the table turns out not to be once a second branch opens. Use an identifier that means nothing and never changes, and keep the email as an ordinary column with a unique constraint on it if it should be unique.

Let the database refuse bad data

The third idea is the one that separates a schema that holds up from one that slowly fills with nonsense: the database should reject data that cannot be true, rather than trusting that the application will never send it.

This is not a matter of taste. Applications get rewritten, scripts get run by hand, an import gets done at midnight during an incident, and a second service eventually starts writing to the same tables. Every one of those bypasses the validation in your form. The constraint in the database is the only rule that all of them are subject to.

Foreign keys are the ones most often skipped, usually because they make test data annoying to set up. PostgreSQL is explicit about what you are giving up: "A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables." Skip it and the integrity is not maintained by anything, it is merely hoped for.

Pick types that mean what you think they mean

Beyond structure, the single most expensive small decision is storing money in a floating point column. The PostgreSQL documentation on numeric types states the rule and the reason: numeric "is especially recommended for storing monetary amounts and other quantities where exactness is required", while for the floating point types "comparing two floating-point values for equality might not always work as expected", because some values "are stored as approximations".

The symptom arrives much later than the decision, as a report that is off by a few piastres and an afternoon spent proving the arithmetic was right all along. The same care applies to dates: store an instant with its time zone rather than a local time and a guess about where the user was, or you will spend a day on it during the first daylight saving change.

The normal forms, in plain language

Having done the ideas, the vocabulary is now short. First normal form: one value per cell, no comma separated lists stuffed into a column. Second: if a table has a compound key, every other column must depend on the whole key rather than half of it. Third: no column should depend on another ordinary column instead of on the key, which is why a stored total that could be calculated from its parts is a liability.

If the three ideas above are in place, you will usually be in third normal form without having aimed at it. That is the honest relationship between the theory and the practice, and it is worth saying because the theory is often taught as though it were the starting point rather than a description of what careful design produces.

When to break these rules on purpose

Deliberate duplication is legitimate in two cases. The first is a value that must be frozen in history: the price on an order line is the price at the time of the order, not a live lookup to the product table, because the product price will change and the invoice must not. That looks like duplication and is actually a different fact.

The second is a genuine, measured read performance problem, where a calculated total is stored because recalculating it is too slow at the volume you actually have. The condition is measured. Storing derived values because it feels faster, before anything is slow, buys nothing and adds a value that can now disagree with its own inputs. A dashboard is the usual place this argument arrives, and we set out what it is reasonable to expect there in what real means in a real time dashboard.

Honest limits

This is enough to design a schema for a business system with tens of tables, which covers most internal tools, most stock and order systems, and most of what a small company will ever need. It is not enough for a data warehouse, where deliberate denormalisation is the standard approach rather than an exception, and it is not enough for very high write volumes, where the constraints above start to have a measurable cost.

It is also worth saying that a schema is not a system. Getting the tables right removes a category of future pain, and it removes none of the work of deciding what the software should do. That decision comes first, and if it has not been made yet, scoping the project before anyone writes code is the thing to do before opening a schema designer. For a worked example of both together, when a stock spreadsheet starts costing more than it saves walks through one.