Normalization and Denormalization by Invariant

Schema design is often reduced to a contest between two slogans: normalize to avoid duplication, or denormalize to avoid joins. Neither is a useful starting point. A schema exists to preserve facts while many writers, readers, retries, migrations, and failures act on them. Its central question is therefore: which stored value owns each business invariant, and how can the database reject states that violate it?

Normalization helps by giving one kind of fact one authoritative representation. Denormalization helps when a second representation serves a measured access pattern, provided its derivation and repair protocol are explicit. The apparent opposition disappears once both choices are evaluated by invariants. Duplicate bytes are not automatically dangerous, and a schema with no repeated text is not automatically correct. Ambiguous authority is the real hazard.

This article develops that method through an order schema. The example distinguishes current catalog facts from historical sale facts, derives table boundaries from dependencies, and then adds selected projections without pretending they maintain themselves.

Begin With Facts and Invariants #

Before drawing tables, write statements the system must keep true. Some describe identity, some relationships, and some calculations:

• A product SKU identifies one catalog product.
• A customer’s email is unique among active customer accounts.
• Every order belongs to exactly one customer.
• Every order line refers to one order and records the price agreed for that sale.
• An order’s payable total equals its line amounts plus charges minus discounts under a specified rounding rule.
• Changing a product’s current catalog price must not rewrite completed orders.

These sentences expose different ownership needs. products.current_price is a mutable catalog fact. order_lines.unit_price is an immutable sale fact established when an order is accepted. The values may initially be equal, but they do not mean the same thing. Storing both is not a normalization failure because neither is a redundant copy of the other.

An invariant is a predicate that must hold for every committed state. If $S$ is a database state and $I$ is the rule, a valid transaction must preserve

The important qualifier is “committed.” Temporary disagreement inside one transaction can be harmless. Disagreement visible between independent commits may be unacceptable for a balance, tolerable for a search index, or expected for an analytics projection. Schema design must name that tolerance instead of inheriting it accidentally.

Classify each fact along four axes:

This inventory is more durable than beginning with nouns. A giant customers table can contain customer identity, current preferences, latest order totals, and marketing statistics simply because all of them mention a customer. Their change rules are different, so their schema boundaries should be different too.

Use Dependencies to Find Authoritative Boundaries #

A functional dependency $X \rightarrow Y$ says that two rows agreeing on attributes $X$ must agree on attributes $Y$. If sku uniquely identifies a product, then

Suppose an early order export stores this relation:

OrderRow(
  order_id, ordered_at,
  customer_id, customer_email,
  product_id, product_name,
  quantity, unit_price
)

Its apparent key is (order_id, product_id) only if a product may occur once per order. Yet order_id -> ordered_at, customer_id; customer_id -> customer_email; and product_id -> product_name. Attributes depend on only part of the composite key, and some depend transitively through another non-key attribute. Updating an email or product name now requires finding every historical order row. Missing one produces two answers to a question that should have one.

Normal forms are diagnostic tools for these ambiguities:

• First normal form requires values to be represented as rows and columns rather than hidden repeating groups.
• Second normal form removes dependencies on only part of a composite key.
• Third normal form removes non-key attributes that depend transitively on a key through another non-key attribute.
• Boyce-Codd normal form requires every determinant in a nontrivial dependency to be a candidate key.

The names matter less than the reasoning: if an attribute changes because of an event owned by some other entity, storing it here may create a second authority. Decompose the relation until each ordinary update can target one owner without scanning unrelated facts.

Decomposition has two correctness tests. It should be lossless, meaning joining the decomposed relations cannot invent or lose valid rows. It should also preserve important dependencies where practical, so the database can enforce them without an expensive multi-table assertion. BCNF can sacrifice dependency preservation in unusual overlapping-key designs; third normal form is sometimes the more operable compromise. Normalization is not a ritual of splitting until every table has three columns.

Work an Order Schema From the Invariants #

The export can become four authoritative relations. A generated line identifier avoids assuming a product occurs only once in an order; promotions or fulfillment splits may make repeated products legitimate.

CREATE TABLE customers (
  customer_id UUID PRIMARY KEY,
  email TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('active', 'closed'))
);

CREATE UNIQUE INDEX active_customer_email
  ON customers (lower(email))
  WHERE status = 'active';

CREATE TABLE products (
  product_id UUID PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  product_name TEXT NOT NULL,
  current_price_cents BIGINT NOT NULL CHECK (current_price_cents >= 0)
);

CREATE TABLE orders (
  order_id UUID PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES customers(customer_id),
  ordered_at TIMESTAMPTZ NOT NULL,
  currency CHAR(3) NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('pending', 'accepted', 'cancelled'))
);

CREATE TABLE order_lines (
  line_id UUID PRIMARY KEY,
  order_id UUID NOT NULL REFERENCES orders(order_id) ON DELETE RESTRICT,
  product_id UUID NOT NULL REFERENCES products(product_id),
  product_name_at_sale TEXT NOT NULL,
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price_cents BIGINT NOT NULL CHECK (unit_price_cents >= 0)
);

CREATE INDEX order_lines_by_order ON order_lines (order_id, line_id);

product_name_at_sale deserves scrutiny. If invoices must display the exact description accepted by the buyer, it is a historical fact, like unit_price_cents, and should not change when the catalog is edited. If historical invoices should always display the latest catalog wording, the column is redundant and should be joined from products. The business invariant decides; a visual resemblance between values does not.

Consider order O-701 containing two units of product P-9 at 1,250 cents and one unit of P-4 at 700 cents. The line subtotal is

Tomorrow, P-9.current_price_cents becomes 1,400. A historical order query still uses the sale price and returns 3,200 cents. A new cart reads 1,400. This is not stale data: the two columns answer different questions.

Now suppose the order table also stores customer_email. Unlike the sale price, its meaning is unclear. Is it the customer’s current login email, the notification address captured at checkout, or invoice evidence? If current, it duplicates customer authority and can drift. If captured evidence, rename it billing_email_at_sale, define when it freezes, and treat it as an order fact. Precise names prevent accidental synchronization between values that should diverge.

Put Enforcement Near the Data #

A prose invariant is only a wish until every writer must obey it. Prefer declarative database constraints for rules expressible over one row, one key, or one referential edge. NOT NULL, CHECK, UNIQUE, exclusion constraints, and foreign keys protect against application bugs, repair scripts, imports, and future services that bypass today’s validation layer.

Constraints should match semantics exactly. A global unique index on customer email would prevent a closed account’s address from being reused if reuse is allowed; the partial index above expresses uniqueness only for active accounts. Case normalization must be specified too. lower(email) is illustrative, but real identity rules need a deliberate canonicalization policy rather than whatever comparison happens to be convenient.

Cross-row totals are harder. A constraint on orders cannot normally query all order_lines, and line writes may arrive in several statements. Options include:

1. Calculate the total at read time from authoritative lines.
2. Update lines and a stored total through one database transaction and one controlled write path.
3. Store the accepted monetary breakdown as immutable facts, including rounding and discount allocation, rather than repeatedly deriving it from changing rules.

Triggers can centralize maintenance, but hidden trigger chains complicate bulk loads, lock analysis, and debugging. Stored procedures or a tightly scoped command handler make the transaction visible but require permissions that prevent alternate writes. Choose one enforcement boundary and test that it cannot be bypassed. “The application usually updates both” is not a boundary.

Transactions cannot rescue an incorrectly assigned owner. If two independent tables both claim to be the canonical email, atomically updating them only narrows the opportunity for drift; it does not explain conflict resolution when an import updates one or when rules change. First establish authority, then make the maintenance mechanism preserve it.

Denormalize as a Maintained Projection #

Denormalization is justified when a concrete read path cannot meet its objective from normalized facts at acceptable cost or complexity. Examples include a precomputed order subtotal, a customer dashboard’s last-order timestamp, a search document, or a warehouse dimension optimized for analysis. Each should be described as a projection from authoritative inputs, not as another source of truth.

A useful projection contract records:

For the worked order, orders.subtotal_cents = 3200 is redundant because lines determine it. It can still be valuable when almost every request lists many orders without their lines. The safe version makes the dependency explicit:

BEGIN;

INSERT INTO order_lines (
  line_id, order_id, product_id, product_name_at_sale,
  quantity, unit_price_cents
) VALUES
  ('00000000-0000-0000-0000-000000000091', :order_id, :product_9, :name_9, 2, 1250),
  ('00000000-0000-0000-0000-000000000041', :order_id, :product_4, :name_4, 1, 700);

UPDATE orders
SET subtotal_cents = (
  SELECT COALESCE(SUM(quantity * unit_price_cents), 0)
  FROM order_lines
  WHERE order_id = :order_id
)
WHERE order_id = :order_id;

COMMIT;

This sketch assumes all line mutations use the same transaction protocol. For a high-write aggregate, incrementing a total may reduce repeated scans, but retries and line updates make delta arithmetic subtle. Recomputing from bounded source rows is often easier to prove. For immutable accepted orders, calculate once at the transition to accepted and then reject further line changes.

An asynchronous projection changes the contract. A customer summary updated through an outbox may lag briefly, so APIs must not use it to authorize a refund or enforce a credit limit. Include source versions or event positions so duplicate and out-of-order deliveries cannot move the projection backward. Keep a rebuild path; if the only way to repair a projection is to replay years of unversioned events perfectly, it is not operationally disposable.

Choose Consistency by Consequence #

Not every duplicate requires same-transaction maintenance. The consequence of staleness determines the protocol.

Synchronous maintenance fits values used in the same invariant: balances, inventory reservations, accepted order totals, and uniqueness claims. Keep the transaction small and lock rows in a stable order. The cost is more write work and possible contention, but readers never observe a committed mismatch.

Asynchronous maintenance fits search, recommendations, analytics, and dashboard summaries where bounded lag is visible and acceptable. Use a transactional outbox so the source change and intent to update the projection commit together. Consumers should be idempotent, partition ordering by the owning key, expose lag, and tolerate replay.

Read-time derivation fits inexpensive calculations or values rarely requested. It avoids write amplification and drift but may add joins, aggregation, or repeated computation. Indexes and query plans should be measured before copying columns merely to remove a join; relational databases are designed to join indexed keys.

Periodic batch refresh fits coarse analytics whose contract already names a reporting cutoff. It is unsuitable when users interpret the value as current. Labeling a nightly number “live” is a product bug, not a database tuning choice.

Duplication multiplies write obligations. One source update may invalidate a cache, search document, summary table, export, and warehouse row. The read benefit must pay for that write amplification, failure handling, monitoring, and schema evolution. A denormalized field without a named owner and repair command is deferred corruption work.

Recognize Failure Modes and Tradeoffs #

Normalization can be taken too far. Decomposing attributes that share identity and lifecycle into one-to-one tables adds joins, locks, migration steps, and code without removing a real dependency anomaly. Generic entity-attribute-value designs avoid migrations by sacrificing types, constraints, discoverability, and predictable plans. A normalized operational model should remain legible to the people changing it.

Denormalization has its own recurring failures:

• Dual authority: two APIs update two copies under different rules.
• Unbounded fan-out: changing one product name rewrites millions of rows that never needed a snapshot.
• Retry-sensitive deltas: an at-least-once event increments a summary twice.
• Out-of-order regression: an older event overwrites a newer projection value.
• Semantic collision: a historical snapshot is later treated as current state.
• Opaque staleness: consumers cannot tell whether a value reflects the latest source version.
• Irrebuildable state: projection logic changed, but no source version or migration path exists.

There are also legitimate performance tradeoffs. Joins can become expensive with wide fan-out, poor cardinality estimates, missing indexes, or remote data boundaries. Duplicating a small, stable label may simplify a latency-critical read. Conversely, denormalization can make writes slower, enlarge indexes, reduce cache density, and turn a one-row correction into a fleet of repair jobs. Compare the complete workload, not the query that motivated the proposal in isolation.

A useful decision record states the invariant, normalized query plan, measured constraint, chosen projection, freshness target, write path, rebuild procedure, and removal condition. That turns denormalization into a reversible engineering choice rather than schema folklore.

Verify Invariants and Evolve Safely #

Test schemas at three levels. First, test constraints directly: duplicate active emails fail, missing parents fail, negative quantities fail, and completed order lines cannot change. Include concurrent transactions; two individually valid requests can race on uniqueness, stock, or state transitions.

Second, test derivations with generated cases. For any set of legal lines, the stored subtotal should equal a trusted calculation using the exact monetary and rounding rules. Exercise zero lines, maximum values, repeated products, discounts, cancellations, and retrying the same command. Compare source and projection after every generated operation sequence.

Third, reconcile in operation. A bounded query can detect drift without trusting the projection:

SELECT o.order_id,
       o.subtotal_cents AS stored,
       COALESCE(SUM(ol.quantity * ol.unit_price_cents), 0) AS derived
FROM orders AS o
LEFT JOIN order_lines AS ol ON ol.order_id = o.order_id
WHERE o.order_id >= :start_id AND o.order_id < :end_id
GROUP BY o.order_id, o.subtotal_cents
HAVING o.subtotal_cents <> COALESCE(SUM(ol.quantity * ol.unit_price_cents), 0);

Run reconciliation in bounded key ranges, record mismatch count and age, and repair from authoritative lines. Sampling can monitor huge data sets, but every range should eventually be covered when the invariant is consequential. Alert on projection lag and consumer failures before users discover stale summaries.

Schema evolution follows the same ownership discipline. Add a nullable projection column, deploy code that can read without it, backfill in restartable batches, begin maintaining it for new writes, verify parity, and only then make reads depend on it. Removing duplication reverses the sequence: switch reads to the authority, stop dual maintenance, verify no consumers remain, then drop the copy. Avoid one deployment that changes schema, authority, and all readers simultaneously.

The durable principle is simple: normalize to make authority and write invariants unambiguous; denormalize only to create a named, maintained projection for a demonstrated access pattern. A good schema does not minimize joins or duplicate bytes. It minimizes the number of ways committed data can tell contradictory stories, and it gives operators a practical way to detect and repair every deliberate copy.