Database Normalization Explained with a Real Schema (1NF to BCNF)

Normalization is the process of splitting one wide table into several narrow ones so that every fact is stored exactly once. This article starts with the spreadsheet a veterinary clinic might keep, applies the normal forms one at a time in plain words, and ends with the seven tables of the classic PetClinic schema. It closes with the cases where breaking the rules on purpose is the right call.

The spreadsheet#

Before there was a database, the clinic had one sheet with a row per visit:

Three rows and already three problems. The owner's city is stored three times and disagrees with itself. The vet's specialties are a comma-separated list. And if Samantha never visits again, the fact that she is a cat disappears with her last row. Normalization is the systematic way of finding and removing exactly these problems.

Functional dependencies in one paragraph#

Every normal form is stated in terms of functional dependencies, which sound worse than they are. Column B depends on column A when knowing A tells you B: the pet's name tells you its type, the owner tells you the city, the visit tells you the date. Write it as pet -> pet_type. A key is a column, or a set of columns, that every other column depends on. The normal forms simply say which dependencies are allowed to exist inside one table.

First normal form: atomic values, no repeating groups#

1NF requires that every cell hold one value and every row be identifiable by a key. The vet_specialties cell with "surgery, dentistry" breaks it, and so would columns named pet1, pet2, pet3. The fix is a row per value, which means either duplicating the visit row per specialty or, better, taking the specialties out to their own table right away. In practice teams go straight to that second option, because a repeated group almost always hides a separate entity.

CREATE TABLE specialties
(
    id   INT         NOT NULL,
    name VARCHAR(80) NOT NULL,
    CONSTRAINT pk_specialties PRIMARY KEY (id)
);

Second normal form: nothing depends on part of the key#

2NF applies to tables whose key has more than one column. Suppose we key the visit sheet by (owner, pet, visit_date). The pet's type depends on pet alone, and the owner's city depends on owner alone; both are partial dependencies on the composite key. Every column that depends on only part of the key moves out to a table keyed by that part:

CREATE TABLE owners
(
    id         INT          NOT NULL,
    first_name VARCHAR(50)  NOT NULL,
    last_name  VARCHAR(50)  NOT NULL,
    address    VARCHAR(255),
    city       VARCHAR(80),
    telephone  VARCHAR(20),
    CONSTRAINT pk_owners PRIMARY KEY (id)
);

CREATE TABLE pets
(
    id         INT         NOT NULL,
    name       VARCHAR(50) NOT NULL,
    birth_date DATE,
    type_id    INT         NOT NULL,
    owner_id   INT         NOT NULL,
    CONSTRAINT pk_pets PRIMARY KEY (id),
    CONSTRAINT fk_pets_types FOREIGN KEY (type_id) REFERENCES types (id),
    CONSTRAINT fk_pets_owners FOREIGN KEY (owner_id) REFERENCES owners (id)
);

Notice that the split also introduced surrogate keys. Two owners called Jean Coleman are now two rows with different ids, and Samantha's row survives even when she has no visits, which fixes the third problem from the spreadsheet.

Third normal form: nothing depends on a non-key column#

3NF forbids transitive dependencies: a non-key column that depends on another non-key column rather than on the key. If pets carried both type_id and type_name, then type_name would depend on type_id, not on the pet. Same with an owner_city column on visits. Each such column moves to the table whose key it really depends on, and what stays behind is the foreign key. This is where the types table comes from, and why visits knows only the pet_id:

CREATE TABLE visits
(
    id          INT NOT NULL,
    pet_id      INT NOT NULL,
    visit_date  DATE,
    description VARCHAR(255),
    CONSTRAINT pk_visits PRIMARY KEY (id),
    CONSTRAINT fk_visits_pets FOREIGN KEY (pet_id) REFERENCES pets (id)
);

A common summary of 3NF is that every non-key column depends on the key, the whole key, and nothing but the key. If you can say that about every table, you are done for almost every business application.

Boyce-Codd normal form: every determinant is a key#

BCNF closes a gap that 3NF leaves open when a table has overlapping candidate keys. Take a table that records which vet covers which specialty at which site, keyed by (vet, specialty), but where each specialty is covered by exactly one vet per site, so (specialty, site) -> vet also holds. A column that is part of a key now depends on something that is not a key. The table is in 3NF and still allows contradictions. The cure is the same as always: split along the dependency. In PetClinic the vet-to-specialty relation is a plain many-to-many with no extra rules, so a join table is enough:

CREATE TABLE vet_specialties
(
    vet_id       INT NOT NULL,
    specialty_id INT NOT NULL,
    CONSTRAINT pk_vet_specialties PRIMARY KEY (vet_id, specialty_id),
    CONSTRAINT fk_vet_specialties_vets FOREIGN KEY (vet_id) REFERENCES vets (id),
    CONSTRAINT fk_vet_specialties_specialties FOREIGN KEY (specialty_id) REFERENCES specialties (id)
);

Fourth and fifth normal form deal with independent multi-valued facts stored in one table, such as a vet's specialties and a vet's working days side by side. The rule of thumb that covers them is to keep one relationship per join table, which the design above already does. See designing join tables for the mechanics.

The result#

Every table has a single-purpose key, every foreign key points at the table that owns the fact, and the only repeated values left are the ids themselves. The spreadsheet's three rows now touch six tables, and a change of address is one UPDATE on one row.

When to denormalize on purpose#

Normal forms optimize for correctness of writes. Some workloads are dominated by reads that would otherwise join five tables on every request, and for those a controlled amount of redundancy is a legitimate design choice, provided three conditions hold: the redundant value has one owner, there is a mechanism that keeps the copies in sync, and the decision is written down.

• Counters and totals such as orders.total_cents or owners.pet_count, maintained by the application in the same transaction or by a trigger.
• Snapshots that must not change later: order_items.unit_price_cents copies the product price at the time of purchase, which is not redundancy at all but history.
• Read models, materialized views and reporting tables rebuilt from the normalized source on a schedule.
• Document columns (jsonb, JSON) for genuinely schemaless attributes, kept out of anything you filter or join on.

What does not qualify is denormalizing because a join is assumed to be slow. Measure first; a foreign key with an index on it makes most of these joins cheap.

Where SchemaDrawer fits#

Normal forms are easier to check on a picture than in a directory of migration files. SchemaDrawer runs the migrations of 22 tools in Docker against the PostgreSQL, MySQL, MariaDB, SQL Server, Oracle or SQLite version you choose and draws the resulting tables, columns, primary keys and foreign keys as an SVG. A table with no incoming or outgoing arrows, a column that repeats a name from another table, or a join table that has grown extra columns are the visual signatures of the problems above. The diagram in this article is its output for the Flyway version of PetClinic, which you can find on the examples page.

Key takeaways#

• 1NF: one value per cell, a key per row. Repeating groups are hidden entities.
• 2NF: with a composite key, no column may depend on part of it.
• 3NF: no column may depend on another non-key column. Key, whole key, nothing but the key.
• BCNF: every column that determines another must be a candidate key.
• Denormalize only with a single owner, a sync mechanism and a written reason.