A database stores data. A model decides what counts as an entity, what relationships exist, and what invariants are guaranteed by the shape itself. Eighty percent of database cost over a system’s life is determined by the model chosen in the first week. Indexing, query plans, and tuning paper over a bad model — they do not fix one.

This topic covers the decisions that survive migration: normalisation, denormalisation, and the dimensional shape that powers analytics.

What Modeling Decides, Up Front

Three coupled choices, made in the first week, dictate the next three years:

1. Entities and identity — what is a “row”? Is a user identified by email, by an opaque id, or by a tenant-scoped composite? Identity decides every join later.
2. Relationship cardinality — one-to-many → foreign key; many-to-many → junction table; one-to-one → either a column or a separate table with shared PK. The cardinality decides the join shape and the cost of writes.
3. Integrity invariants — which fields can be null, which transitions are legal, which keys must be unique. The model expresses these as constraints; the application otherwise has to enforce them piecemeal.

A model that makes these choices deliberately is a contract. A model that drifts is a garbage heap the team is afraid to touch.

Normal Forms as Integrity Guarantees

Normalisation is the discipline of removing redundancy so that each fact lives in exactly one place. Each normal form closes one class of anomaly:

The property normalisation buys: a fact changes in one row only. Update a customer’s address once; no orphan copy sits in another table with contradictory state. The cost: more joins at read time, and a write cost for every relationship that needs to follow the keys.

Denormalization With Intent

Denormalisation adds redundancy back, deliberately, to trade write cost for read cost. It is not the enemy of normalisation; it is normalisation’s companion — chosen when the read access pattern is severe enough to justify the integrity overhead.

Rule of thumb: normalise the model first, denormalise second — only where measurement shows a hot read path. Denormalising before measurement is premature uneconomy.

Dimensional Modeling (Star Schema)

The above is transactional (OLTP) modelling: protect integrity, accept join cost. Analytics (OLAP) inverts the trade — protect read speed, accept redundancy. The shape is the star schema: one large fact table of measurable events, surrounded by dimension tables of descriptive attributes.

            dim_customer
                  |
            dim_time ← fact_sales → dim_product
                  |
            dim_store

• Fact — what happened (quantity, sale amount). Narrow, deep (billions of rows).
• Dimension — descriptive context (customer segment, product line, store region). Wide, shallow (typically thousands to millions of rows).
• Grain — what one fact row means (“one sale line item”, not “one aggregate”). Choosing the grain is the single most consequential decision.

The shape trades redundancy for read speed: every analytical query is a JOIN-the-dimension-then-GROUP BY pattern, and column-store engines (ClickHouse, Druid, BigQuery) make these scans cheap. The opposite — a normalised “snowflake” — saves space but costs more joins, which dominates analytics latency.

Snowflake vs Star

The 3NF-friendly snowflake is rarely worth the join cost in modern column stores. The standard analytical shape is the star.

Schema as Contract

The schema is a public API for every application that touches the database. Three habits pay off:

• Version every migration. Even a non-breaking change adds a row to a schema_migrations table; you cannot know the live schema otherwise.
• Document constraints in the schema, not just the application. A UNIQUE (tenant_id, email) constraint is the documentation that this pair is identity — not a guess the application has to share.
• Treat nulls as promise. A nullable column is a contract the application must honour. Where the null is impossible in practice, NOT NULL makes the model say so.

The application can lie. A constrained schema cannot.

Practice Trajectory

1. Model a tiny domain — Orders, Customers, Products, Addresses — at 3NF, then ask: where would measurement justify a denormalised column?
2. Take the same domain and produce its star-schema equivalent: identify the fact, the grain, the dimensions. Compare query shapes between the two.
3. Find a model you know with a nullable column that the application fills in 100% of the time. Add the NOT NULL constraint; trace what migrations and tests need to change.
4. Pick an analytical query in a system you use; decompose it as the star-shaped join it actually is. Notice the row-to-column ratio.
5. Take a “table that holds two things” (a users table that quietly also holds inactive sessions). Draw 2NF and 3NF reforms; describe which anomalies the current shape suffers.

When It’s the Right Tool