Anomalies and Denormalisation

The three anomalies

Normalisation is not an aesthetic exercise. Each normal form exists because an unnormalised table permits a specific failure.

CREATE TABLE course_flat (
    student_id   INT,
    student_name VARCHAR(60),
    course       VARCHAR(50),
    instructor   VARCHAR(60),
    instructor_phone VARCHAR(20),
    PRIMARY KEY (student_id, course)
);

-- Normalised: each fact has exactly one home
CREATE TABLE instructors (
    id    INT PRIMARY KEY,
    name  VARCHAR(60) NOT NULL,
    phone VARCHAR(20)
);

CREATE TABLE courses (
    id            INT PRIMARY KEY,
    title         VARCHAR(50) NOT NULL,
    instructor_id INT,
    CONSTRAINT fk_courses_instructor FOREIGN KEY (instructor_id) REFERENCES instructors(id)
);

CREATE TABLE enrolments (
    student_id INT,
    course_id  INT,
    PRIMARY KEY (student_id, course_id)
);

All three anomalies disappear at once. A course with no students is a row in courses. A phone number change is one UPDATE. Unenrolling the last student removes a row from enrolments and nothing else.

Denormalisation

Denormalisation is deliberately reintroducing redundancy to make reads cheaper. It is a performance decision, taken after measurement, that trades write complexity for read speed.

When it is justified

• The read is frequent, the join is expensive, and you have measured both.
• The duplicated value changes rarely.
• You have a mechanism to keep the copy correct, and a query that proves it still is.

Common techniques

-- 1. Cached aggregate
ALTER TABLE customers ADD COLUMN order_count INT NOT NULL DEFAULT 0;

-- 2. Duplicated lookup value, to avoid a join on a hot path
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(80) NULL;

-- 3. Pre computed reporting table, rebuilt on a schedule
CREATE TABLE daily_revenue (
    day      DATE PRIMARY KEY,
    orders   INT           NOT NULL,
    revenue  DECIMAL(12,2) NOT NULL
);

The third is usually the best of the three: the summary is clearly derived, clearly stale by a known amount, and rebuilding it cannot corrupt the source data.

The cost, stated honestly

-- Every denormalised column needs a query like this, scheduled
SELECT c.id, c.order_count AS cached, COUNT(o.id) AS actual
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.order_count
HAVING c.order_count <> COUNT(o.id);

Important rules

• Normalise first. Denormalise only in response to a measurement, never in anticipation.
• Every denormalised value needs an owner: the code, trigger or job responsible for keeping it correct.
• Every denormalised value needs a reconciliation query, written at the same time.
• A point in time snapshot - the price at the moment of sale - is not denormalisation. It is a different fact.
• Reporting and analytics systems are routinely denormalised on purpose; that is what star schemas are.

Common mistakes

• Denormalising because a query "feels slow" without reading its execution plan. Most slow joins are missing indexes, not too many tables.
• Adding a cached counter with no job to verify it.
• Copying a value that changes often, guaranteeing constant drift.
• Denormalising the transactional schema when a separate reporting table would have served better.

Best practices

• Try an index, a covering index, or a rewritten query before touching the schema.
• Prefer a separate summary table over a duplicated column in a live entity table.
• Document each denormalisation: what it duplicates, who maintains it, how it is verified.
• Schedule the reconciliation query and alert on any mismatch.

Practice

1. Give a concrete example of each anomaly in a table storing employee, department, department_head.
2. Propose a denormalisation that would speed up a "top customers" dashboard, and write its reconciliation query.
3. Why is a nightly daily_revenue table usually safer than a live customers.lifetime_value column?