khalil Drissi Cms

Tag: database design

  • Database design and normalization (and when to denormalize)

    Database design and normalization (and when to denormalize)

    I have inherited enough broken schemas to have strong opinions. The worst outages I have dealt with were almost never about a missing index or a slow disk. They were about a data model that lied. A column that meant three different things depending on the row. A “status” field that was secretly a free-text dumping ground. A foreign key that existed in someone’s head but never in the database. Good design is the cheapest insurance you will ever buy, and you buy it before you write a single query.

    This post is about how I actually approach normalization, what the normal forms buy you in practice, and the handful of situations where I deliberately denormalize. If you want the requirements-gathering side of this, I wrote that up separately in my data modeling methodology guide. This one is about the schema itself.

    What normalization actually protects you from

    People talk about normalization like it is an academic exercise. It is not. Every normal form exists to prevent a specific class of bug that will eventually wake you up at 3am. Strip away the formal language and the goal is simple. Store each fact exactly once, in the place where it belongs, so that there is no way for two copies of the same fact to disagree.

    When the same piece of data lives in two places, those two places will drift apart. Not maybe. They will. Someone updates the customer’s email in one table and forgets the other. A batch job touches half the rows. Now you have two truths and no way to know which one is correct. Normalization removes the second copy so the contradiction becomes impossible rather than merely unlikely.

    The normal forms, the way I think about them

    I do not walk around quoting the formal definitions, but I do keep their intent in my head when I sketch tables.

    • First normal form means no repeating groups and no multi-valued columns. If you find yourself naming columns phone1, phone2, phone3, you have a separate table waiting to be born. A comma-separated list in a varchar is the same crime wearing a disguise.
    • Second normal form means every non-key column depends on the whole primary key, not just part of it. This only bites you with composite keys, but when it bites it leaves a mark.
    • Third normal form means non-key columns depend on the key and nothing but the key. If a column depends on another non-key column, it belongs in its own table. The classic example is storing a city and its postal code together when one determines the other.

    In day to day work, if I hit third normal form I am usually in good shape. Boyce-Codd and the higher forms matter for specific overlapping-key situations, but third normal form catches the vast majority of real modeling mistakes I see in code review.

    A concrete example

    Say we are storing orders. The naive version crams everything into one wide table, repeating the customer name and email on every single order row. Here is the normalized version I would actually ship.

    -- Customers own their own facts, once
CREATE TABLE customers (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       CITEXT NOT NULL UNIQUE,
    full_name   TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Orders reference the customer, they do not copy it
CREATE TABLE orders (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  BIGINT NOT NULL REFERENCES customers(id),
    status       TEXT NOT NULL
                 CHECK (status IN ('pending','paid','shipped','cancelled')),
    placed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Line items are their own grain: one row per product per order
CREATE TABLE order_items (
    order_id     BIGINT NOT NULL REFERENCES orders(id),
    product_id   BIGINT NOT NULL REFERENCES products(id),
    quantity     INT NOT NULL CHECK (quantity > 0),
    unit_price   NUMERIC(12,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

    Notice a few choices that are not strictly about normal forms but travel with good design. The status column has a CHECK constraint so the database itself enforces the allowed values. The unit_price lives on the line item, not on the product, because the price at the moment of sale is a different fact from the current price. That distinction is the kind of thing normalization forces you to think about. Is this the current value or the value as it was? They are not the same fact and they do not belong in the same column.

    Constraints are part of the design, not decoration

    A schema without constraints is a suggestion. I push as much invariant enforcement into the database as I reasonably can, because application code is the wrong place to guarantee data integrity. There will always be a second writer eventually. A migration script, an admin tool, a coworker poking around in a psql session. The database is the only layer all of them share.

    So I use NOT NULL aggressively, foreign keys without apology, UNIQUE constraints on anything that should be unique, and CHECK constraints for value ranges and enumerations. If you only take one habit from this post, make it this one. Most of the “mysterious bad data” I have debugged would have been impossible with a constraint that took thirty seconds to write. I go deeper on this in my notes on schema best practices.

    When I denormalize on purpose

    Now the heresy. I denormalize regularly, and I do not feel bad about it, because denormalization done with intent is an optimization, not a mistake. The trick is that you only denormalize after you understand the access pattern, never before. Premature denormalization is just a data model with extra bugs.

    Here are the cases where I reach for it.

    • Read-heavy aggregates. If a dashboard reads an order total a thousand times for every one time the order changes, computing that total on every read is wasteful. I will store a cached total column and keep it current with a trigger or in the same transaction as the write.
    • Reporting and analytics tables. Transactional normalization and analytical query patterns pull in opposite directions. A wide, denormalized table or a star schema can turn a brutal eight-way join into a single scan. I keep these separate from the source of truth and rebuild them from it.
    • Expensive joins on the hot path. Sometimes a join is genuinely the bottleneck even after indexing. Copying one frequently-read column to avoid a join can be worth it, as long as you own the update path.

    The non-negotiable rule with every one of these is that the normalized version remains the source of truth. The denormalized copy is derived, disposable, and rebuildable. The moment you have two independent sources of truth you are back to the original sin that normalization existed to prevent.

    How I keep denormalization safe

    If I store a derived value, I make the derivation explicit and I make it automatic. A cached column gets updated in the same transaction as its source, or by a trigger, never by a hopeful comment that says “remember to update this.” A materialized view gets a documented refresh schedule. A reporting table gets rebuilt by a job I can run on demand and verify against the source.

    I also write a check, even a slow one I run nightly, that compares the derived value against a fresh computation and screams if they disagree. Drift is the failure mode of denormalization, and the only defense is to detect it early. Once you can prove the copy matches the source, the performance win comes with a clear conscience. When the copies do start to disagree, it is almost always because a query plan changed or an index disappeared, which is exactly the territory I cover in my indexing deep dive.

    The order I do things in

    My default sequence has not changed in years. Normalize first, to third normal form, with real constraints. Get it correct and let it be correct. Then measure. Only when a specific, measured access pattern demands it do I introduce a denormalized copy, and only as a derived artifact with an enforced update path. Correctness first, then speed, and never speed bought with a lie in the data.

    That order matters because it is far easier to denormalize a clean model than to clean up a model that was muddy from birth. Start strict. Loosen deliberately. Your future self, paged at 3am, will thank you.

  • A practical data modeling methodology, from requirements to schema

    A practical data modeling methodology, from requirements to schema

    Almost every truly painful schema I have had to live with started the same way. Someone opened a migration file and started typing CREATE TABLE before anyone had agreed on what the data actually was. The tables came first and the understanding came later, which is exactly backwards. A schema is the last artifact in modeling, not the first. By the time I write SQL, the hard thinking is already done.

    This is the methodology I use to get from a fuzzy feature request to a schema I am willing to sign my name to. It is not heavy. It does not need special tooling. It mostly needs you to slow down for an afternoon so you can move fast for the next two years. For the design principles that govern the final schema, pair this with my post on normalization and when to denormalize.

    Step one, gather the nouns and the rules

    I start by reading or listening to how the people who actually do the work describe it. Not the engineers. The people in the domain. I am hunting for two things. The nouns, which become candidate entities, and the rules, which become constraints and relationships.

    When a logistics coordinator says “a shipment can have many parcels but every parcel belongs to exactly one shipment,” they have just handed me a one-to-many relationship and a NOT NULL foreign key, for free, in plain language. The domain experts are doing the modeling already. My job is to write it down faithfully and notice when their sentences contradict each other.

    I keep a running glossary as I go. The single most underrated modeling tool is an agreed definition of each term. When two people use the word “account” to mean two different things, you will not find out until production, unless you forced the definition early.

    Step two, find the entities and their identity

    From the nouns, I pull out the real entities. The test I apply is identity. Does this thing have an existence of its own that I need to refer to over time? A customer does. An order does. A line on an order does. The color “blue” usually does not, it is an attribute, until the day the business needs a color catalog with its own rules, at which point it earns entity status.

    For every entity I ask one question immediately. What makes a row unique? Sometimes there is a natural key, like an ISO country code. More often there is not, and I add a surrogate key, a generated identity column with no business meaning. I lean toward surrogate keys for most entities because natural keys have a nasty habit of changing, and a primary key that changes is a primary key that ruins your week.

    Step three, map the relationships

    Now I connect the entities, and I am precise about cardinality because cardinality is where the schema is decided.

    • One to many is the common case. The “many” side carries a foreign key pointing back to the “one” side. An order has many items, so order_items carries the order_id.
    • Many to many always becomes a junction table. There is no other honest way to represent it. Students and courses meet in an enrollments table that carries both foreign keys.
    • One to one is rare and deserves suspicion. Usually it means you either have one entity that you split for no reason, or you have an optional extension that genuinely belongs in its own table. I make myself justify every one-to-one.

    For each relationship I also pin down the participation rules. Is the foreign key mandatory or optional? What should happen on delete? These are not afterthoughts. ON DELETE behavior is a real business decision dressed up as a technical one, and the business should get a vote.

    Step four, attributes and the grain question

    With entities and relationships in place, I attach attributes, and for each one I ask what it really is. Is it atomic, or is it secretly several facts crammed together? A “name” field that everyone wants to search by first and last name is two columns pretending to be one. An address is almost always several.

    The most important question at this stage is grain. What does one row in this table represent, exactly, in one sentence? If I cannot say it cleanly, the table is confused and the queries will be too. “One row per order” is a clear grain. “One row per order, except sometimes per shipment” is a future incident report.

    Step five, write the schema and let the database help

    Only now do I write SQL, and at this point it almost writes itself, because the thinking is finished. Here is the kind of thing that falls out of the steps above for a simple course enrollment domain.

    -- One row per student
CREATE TABLE students (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       CITEXT NOT NULL UNIQUE,
    full_name   TEXT NOT NULL,
    enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE
);

-- One row per course offering
CREATE TABLE courses (
    id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code      TEXT NOT NULL UNIQUE,     -- natural key, stable per catalog
    title     TEXT NOT NULL,
    capacity  INT NOT NULL CHECK (capacity > 0)
);

-- The junction table: one row per student per course
CREATE TABLE enrollments (
    student_id BIGINT NOT NULL REFERENCES students(id) ON DELETE CASCADE,
    course_id  BIGINT NOT NULL REFERENCES courses(id)  ON DELETE RESTRICT,
    grade      TEXT CHECK (grade IN ('A','B','C','D','F') OR grade IS NULL),
    PRIMARY KEY (student_id, course_id)
);

    Look at how much of the model is now enforced by the database rather than left to hope. The many-to-many becomes a composite primary key, which also prevents a student from enrolling twice in the same course without any application code. The two different ON DELETE choices encode a real rule: deleting a student removes their enrollments, but you cannot delete a course that still has students in it.

    Step six, validate against the queries you will run

    A model is only as good as the questions it can answer. Before I call it done, I take the five or ten queries the application will actually run most often and I write them against the schema on paper. If a common question requires a tortured five-table join or a subquery nested three deep, the model is fighting the workload and I go back a step.

    This is also where access patterns start to inform indexing, though I keep that as a separate concern. Get the model honest first, then make it fast. I walk through the performance side in detail in my database indexing deep dive.

    Step seven, plan for change

    No model survives contact with a roadmap unchanged, so I design for evolution from the start. I avoid columns that mean different things in different rows. I prefer adding a nullable column or a new table over overloading an existing one. I keep a migration discipline where every schema change is a versioned, reviewable file, never a manual edit to a live database.

    The mindset that has served me best is this. Modeling is the act of writing down what is true about the world, carefully enough that the database can enforce it. The SQL is just the transcript. Do the thinking first, write the schema last, and validate it against the real questions, and you end up with tables that feel boring in the best possible way. Boring schemas do not page you.