Data modelling and normalisation

A data model decides which questions are easy to answer and which mistakes are impossible to make. Good models make invalid data hard to store.

Entities, keys and relationships

Start with the things your domain talks about (customers, orders, products) and the relationships between them.

• A primary key uniquely identifies each row. Prefer a surrogate key (an auto-increment id or UUID) over a natural one such as an email address, which can change.
• A foreign key references a primary key in another table and lets the database reject orphans, such as an order for a customer who doesn’t exist.

Relationships come in three shapes:

• One-to-many: a customer has many orders. Put customer_id on orders.
• Many-to-many: an order has many products, and a product is in many orders. Use a join table, order_items(order_id, product_id, quantity, unit_price).
• One-to-one: rare; often a sign that two tables could be one, or that optional data was split out on purpose.

Normalisation: one fact in one place

Normalisation removes redundancy so that each fact is stored exactly once. Consider this table:

Ana’s city is stored twice and already disagrees. That is an update anomaly: changing a fact means finding every copy.

• First normal form (1NF): every column holds one atomic value. No comma-separated lists of tags in a single column.
• Second normal form (2NF): every non-key column depends on the whole key, not part of a composite key.
• Third normal form (3NF): non-key columns depend only on the key, not on other non-key columns. customer_city depends on the customer, not on the order, so it belongs in customers.

A common summary: every column should depend on “the key, the whole key, and nothing but the key”.

Store history as data, not as updates

Some values must be frozen at the moment of an event. An order must keep the price paid, even after the product’s price changes. So order_items.unit_price is not redundant with products.price: they are different facts. Distinguish “current value” from “value at the time”.

Denormalise on purpose

Normalised models are best for writing correct data. Reads may need joins across many tables, and at large scale or in analytics that can be slow. Denormalising means deliberately storing a derived or duplicated value:

• a cached comment_count on posts;
• a wide reporting table that pre-joins orders, customers and products;
• a search index that copies text from several tables.

Every copy needs an owner and an update path: a trigger, the same transaction as the write, or a background job that can be rerun. Document which table is the source of truth.

Types and constraints are part of the model

Choose precise types: timestamp with time zone for moments in time, integer cents or numeric for money (never floating point), and enums or check constraints for a fixed set of states. Add NOT NULL, UNIQUE and CHECK constraints for the rules that must always hold. Application code has bugs and gets bypassed by scripts and migrations; constraints in the database always apply.

Analytics models

Data warehouses often use a star schema: a central fact table of events (sales with quantity and amount) surrounded by dimension tables describing them (date, customer, product, store). It is deliberately denormalised for fast aggregation, and it is built from the normalised operational database, not written to directly by the application.