Database normalisation in practice: model each fact once

Normalisation is about the ownership of facts

Normalisation is a way of arranging relational data so that each fact has a clear owner. A customer email belongs to a customer, an order date belongs to an order, and the quantity purchased belongs to one order line. When those facts are repeated in several rows, the database can represent contradictory versions of the same reality.

The purpose is not to create as many tables as possible. It is to reduce insertion, update and deletion anomalies while allowing the database to enforce meaningful relationships. A schema is easier to change when a developer can say where a fact is stored and which key determines it.

Normal forms provide a vocabulary for testing that structure. They are useful reasoning tools, not a substitute for understanding identity, history and the queries the application must support.

• Insertion anomaly: one fact cannot be recorded until an unrelated fact exists
• Update anomaly: one logical change requires several rows to stay in agreement
• Deletion anomaly: removing one fact accidentally removes the only record of another

Begin with keys and functional dependencies

A functional dependency states that one set of attributes determines another. CustomerId → CustomerEmail means that for a given customer identifier there is one current email value in the model. The left side is called the determinant.

Candidate keys are minimal sets of attributes that uniquely identify a row. One candidate key is selected as the primary key, but the other candidate keys still matter and normally need unique constraints. A generated id does not make natural dependencies disappear.

Write the important dependencies before moving columns between tables. If ProductId determines ProductName, repeating the name on every order line creates a copied fact. If an order line records the price actually agreed at purchase, that price may instead be determined by the order line itself and should not be replaced whenever the product’s current price changes.

order_item(
  order_id,
  order_created_at,
  customer_id,
  customer_email,
  product_id,
  product_name,
  unit_price_paid,
  quantity
)

Candidate key: (order_id, product_id)

order_id   → order_created_at, customer_id
customer_id → customer_email
product_id → product_name
(order_id, product_id) → unit_price_paid, quantity

First normal form removes repeating groups

In first normal form, each row is identified by a key and each column holds one value from its declared domain. Repeating columns such as product_1, product_2 and product_3 make the maximum collection size part of the schema and force queries to inspect several columns for the same relationship.

Comma-separated identifiers in a text column create the same problem in disguise. The database cannot apply a foreign key to each embedded value, and ordinary joins, uniqueness and indexing become awkward.

Atomic does not mean a value can never contain internal structure. A timestamp and a postal address string can each be treated as one value for a particular model. PostgreSQL arrays and JSON are legitimate types, but using them moves parts of the data outside ordinary relational constraints. Make that trade-off intentionally rather than calling every nested value a first-normal-form violation.

-- Avoid a fixed collection encoded in columns:
-- order_id, product_1, product_2, product_3

CREATE TABLE order_line (
  order_id uuid NOT NULL REFERENCES sales_order(id),
  line_number integer NOT NULL CHECK (line_number > 0),
  product_id uuid NOT NULL REFERENCES product(id),
  quantity integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, line_number)
);

Second normal form removes partial dependency on a composite key

Second normal form applies when a candidate key contains more than one attribute. Every non-key attribute should depend on the whole candidate key, not only part of it.

In the wide order-item table, order_created_at depends only on order_id and product_name depends only on product_id. Repeating either value for every item creates update anomalies. Moving order facts into sales_order and product facts into product gives each dependency a relation where its determinant is a key.

A table with a single-column candidate key cannot have a partial dependency on that key, so it is already in second normal form if it satisfies first normal form. Adding a synthetic id can hide the composite business key from view; preserve the natural uniqueness constraint where duplicate relationships are not valid.

CREATE TABLE product (
  id uuid PRIMARY KEY,
  name text NOT NULL,
  current_price numeric(18, 2) NOT NULL CHECK (current_price >= 0)
);

CREATE TABLE sales_order (
  id uuid PRIMARY KEY,
  customer_id uuid NOT NULL REFERENCES customer(id),
  created_at timestamptz NOT NULL
);

CREATE TABLE order_line (
  order_id uuid NOT NULL REFERENCES sales_order(id),
  line_number integer NOT NULL,
  product_id uuid NOT NULL REFERENCES product(id),
  unit_price_paid numeric(18, 2) NOT NULL CHECK (unit_price_paid >= 0),
  quantity integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, line_number)
);

Third normal form removes dependencies through non-key attributes

Third normal form addresses transitive dependencies. If order_id determines customer_id and customer_id determines customer_email, then storing customer_email on the order makes it depend on the order through another non-key attribute.

The repeated email can disagree with the customer row and requires every open order to be updated when the customer changes it. Store the current email with the customer and join it when the application needs the current customer details.

This reasoning depends on meaning. If the order must preserve the billing email used when it was placed, that value is a historical fact about the order rather than a copy of the customer’s current email. Naming it billing_email_at_purchase makes the different dependency explicit.

CREATE TABLE customer (
  id uuid PRIMARY KEY,
  email text NOT NULL UNIQUE
);

CREATE TABLE sales_order (
  id uuid PRIMARY KEY,
  customer_id uuid NOT NULL REFERENCES customer(id),
  billing_email_at_purchase text NOT NULL,
  created_at timestamptz NOT NULL
);

-- customer.email is the current contact detail.
-- billing_email_at_purchase is part of the historical order.

BCNF catches determinants that are not candidate keys

Boyce–Codd normal form is stricter than third normal form: for every non-trivial functional dependency, the determinant should be a superkey. It matters in less common schemas where candidate keys overlap and a determinant can satisfy the technical rule for third normal form without identifying the complete row.

Most application schemas gain more from confidently reaching third normal form than from applying BCNF mechanically. If a table records several independent relationships—such as which instructor can teach a subject and which instructor is assigned to a course—separate those facts rather than forcing them into one relation whose constraints are hard to express.

The useful question remains concrete: can two rows assert conflicting values for a fact that should have one owner? If so, identify its determinant and decide whether it belongs in another relation.

Many-to-many relationships deserve an explicit table

A student can join many courses and a course can contain many students. Neither foreign key belongs as a list inside the other row when the relationship needs relational constraints or carries facts of its own.

A junction table gives the relationship a key and a place for attributes such as enrolment time or status. Those values depend on the student-course relationship, not on the student or course independently.

Choose the key from the relationship’s semantics. A composite primary key prevents duplicate membership. A generated enrolment id may be useful when other records refer to one enrolment, but it should not silently permit duplicate active enrolments if the domain forbids them.

CREATE TABLE enrolment (
  student_id uuid NOT NULL REFERENCES student(id),
  course_id uuid NOT NULL REFERENCES course(id),
  enrolled_at timestamptz NOT NULL DEFAULT now(),
  status text NOT NULL CHECK (status IN ('active', 'completed', 'withdrawn')),
  PRIMARY KEY (student_id, course_id)
);

Normalisation does not dictate the application API

A normalised persistence model can be joined into a response shaped for one use case. The HTTP client does not need to receive four database tables merely because the data is stored across four relations.

Keep query behaviour visible. Loading an order and then issuing one product query for every line is an application access problem, not proof that the schema should duplicate product data. Use an intentional join or a bounded set-based query and map the rows into the application result.

Repository boundaries should reflect useful operations while preserving important database behaviour such as transaction ownership, missing rows and uniqueness. An abstraction that hides every join can make performance harder to understand without making the model simpler.

SELECT
  o.id AS order_id,
  o.created_at,
  c.id AS customer_id,
  c.email AS current_customer_email,
  l.line_number,
  p.name AS product_name,
  l.unit_price_paid,
  l.quantity
FROM sales_order o
JOIN customer c ON c.id = o.customer_id
JOIN order_line l ON l.order_id = o.id
JOIN product p ON p.id = l.product_id
WHERE o.id = $1
ORDER BY l.line_number;

Denormalise only when the copied fact has a policy

Denormalisation can be appropriate for a measured read bottleneck, reporting workload, immutable snapshot or precomputed summary. It deliberately introduces duplication, so the design must state which representation is authoritative and how the copy is refreshed.

Adding customer_email to an order summary table might avoid an expensive reporting join, but it raises specific questions: should historical summaries change when the email changes, what happens if refresh fails, and can consumers tolerate stale data?

Measure before denormalising. Appropriate indexes, a corrected query or a smaller result set often fixes the actual problem without adding synchronisation. When duplication is justified, name it as a cache, snapshot or projection so later engineers do not mistake it for another source of truth.

• Name the authoritative relation
• Define whether the copy is current, eventually consistent or historical
• Make refresh and failure behaviour observable
• Test reconciliation or rebuilding
• Record the query evidence that justified the extra complexity

Normalising an existing schema is a compatibility change

Splitting a live table affects application reads, writes, migrations and rollback. Add the new relations and constraints in stages, backfill them from existing data, and detect contradictions before requiring the new model.

During transition, one application version may need to write both shapes or read the new shape with a controlled fallback. Keep that compatibility period short and observable. Dual writes can fail independently, so they need reconciliation rather than an assumption that both always succeeded.

Validate row counts, missing relationships and uniqueness before making foreign keys or not-null constraints mandatory. Remove the old columns only after every supported application version has stopped depending on them and rollback no longer requires the old representation.

Normalisation succeeds when the resulting ownership is clearer in the database and in application code. Reaching a named normal form is useful evidence, but the real result is fewer contradictory states and a model whose history and constraints match the domain.

SELECT customer_id, count(DISTINCT customer_email) AS email_versions
FROM legacy_order_item
GROUP BY customer_id
HAVING count(DISTINCT customer_email) > 1;

-- Resolve contradictions before adding a unique customer row.
-- A migration cannot infer which repeated value is authoritative.