Relational Modelling

Definition

Relational modelling expresses data as tables of rows with typed columns, related by keys, with invariants enforced by the database rather than by application code. Normalisation removes duplication so a fact lives in exactly one place.

Why it remains the default

The database is the only component that sees every write. Invariants enforced in application code are enforced only by the code paths that remember to enforce them — and there is always a batch job, an admin tool, or a migration script that does not. A foreign key, a unique index, a check constraint and a transaction are the cheapest correctness mechanisms available, and giving them up should require a reason.

Second, the query planner means you do not have to know your access patterns in advance. This is the property people undervalue until they lose it: a product will need queries nobody anticipated, and a relational schema answers them with an index rather than a redesign.

Design decisions that matter

• Natural or surrogate keys. Surrogate keys (an opaque ID) almost always win, because natural keys change — email addresses, tax identifiers, product codes all do.
• How far to normalise. Third normal form as a default, denormalising deliberately and measurably when a specific read is too slow. Denormalising first is the common error.
• Where nulls are permitted. A nullable column is a statement that the fact may not exist; if it always exists, forbid null and the class of bugs disappears.
• Enumerations as constraints, not conventions. A status column with a check constraint or a lookup table cannot hold a value the application never intended.
• Temporal data. Deciding early whether you need history — an effective-dated row, an audit table — because retrofitting it means reconstructing a past you did not record.

Industry example

Shopify's core commerce data sits in relational stores, and the interesting decision is what they did when one instance was no longer enough. Rather than moving to a distributed database, the database was partitioned into independent pods, each a complete self-contained relational system serving a subset of merchants.

That preserves everything valuable about the relational model — transactions, joins, constraints — within the boundary where it matters, because a merchant's data is naturally self-contained. It is a reminder that "relational does not scale" is usually shorthand for "one relational instance does not scale", and that the answer may be more instances rather than a different model.

Failure scenarios

• Constraints implemented only in the application, then violated by a data fix or a job.
• Premature denormalisation, creating update anomalies for a read that was never slow.
• Entity-attribute-value schemas built for flexibility, which defeat the planner, forbid constraints, and make every query a self-join.
• Storing structured JSON in a column and querying inside it for data that has a stable shape — the flexibility is bought by giving up the type system and the planner.

Trade-offs

Bought: enforced correctness, ad-hoc query flexibility, mature tooling, and a model most engineers already understand. Sold: horizontal write scaling beyond one machine without deliberate partitioning, and schema changes that must be planned as migrations rather than being implicit.

Interview question

"When would you store a JSON document inside a relational column rather than modelling it as tables, and what do you give up?"