Data Integrity, Redundancy and Consistency

Concept

Data integrity means the data in the database is correct and internally consistent. Relational databases enforce it at three levels, and each has its own tools.

Redundancy and its anomalies

Redundancy is storing the same fact in more than one place. It is not merely wasteful - it makes contradiction possible, which is far worse.

-- A redundant design: department name and location repeated on every employee
CREATE TABLE employee_flat (
    id          INT PRIMARY KEY,
    name        VARCHAR(60),
    dept_name   VARCHAR(60),     -- repeated for every employee in the department
    dept_city   VARCHAR(60)      -- repeated too
);

INSERT INTO employee_flat VALUES
(1, 'Asha',  'Engineering', 'Bengaluru'),
(2, 'Ravi',  'Engineering', 'Bengaluru'),
(3, 'Meera', 'Engineering', 'Bangalore');   -- nothing stopped this

Three rows claim to describe the same department and two spellings of the city now exist. No constraint was violated; the design simply permitted the contradiction.

-- The fix: one fact, one place
CREATE TABLE departments (
    id       INT PRIMARY KEY,
    name     VARCHAR(60) NOT NULL,
    location VARCHAR(60) NOT NULL,
    CONSTRAINT uq_departments_name UNIQUE (name)
);

CREATE TABLE employees (
    id      INT PRIMARY KEY,
    name    VARCHAR(60) NOT NULL,
    dept_id INT NULL,
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(id)
);

Now the department location exists once. Correcting it is one UPDATE of one row, and it is impossible for two employees to disagree about where their department is.

When redundancy is deliberate

Not all duplication is a bug. Two cases are legitimate, and both need a written justification:

-- 1. Point in time snapshot: the price AT THE TIME OF SALE
CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    quantity   INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,   -- copied deliberately from products.price
    PRIMARY KEY (order_id, product_id)
);

This is not redundancy at all, properly understood. products.price is the current price; order_items.unit_price is the price this customer paid. They are different facts that happen to be equal on the day of the sale. Joining to products for historical invoices would silently rewrite history every time a price changed.

-- 2. Cached aggregate, maintained on purpose
ALTER TABLE orders ADD COLUMN item_count INT NOT NULL DEFAULT 0;

A genuine denormalisation. It is correct only while something keeps it in step - a trigger, an application transaction, or a scheduled rebuild - and it needs a reconciliation query that can prove it is still right:

-- Does the cached count still match reality?
SELECT o.id, o.item_count, COUNT(oi.product_id) AS actual
FROM   orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.item_count
HAVING o.item_count <> COUNT(oi.product_id);

Important rules

• Every table needs a primary key, or entity integrity does not exist for it.
• Every foreign key should be declared, not merely implied by a naming convention.
• Constraints are the only rules that cannot be bypassed. Application checks can.
• A stored derived value is correct only until someone writes to the source without going through your code.
• Consistency across tables is a transaction concern - see the transactions notes.

Common mistakes

• Repeating a descriptive attribute on the child table "to avoid a join".
• Storing a total that no process recomputes, and discovering months later it drifted.
• Trusting application validation alone, then importing a CSV directly.
• Joining live product prices into historical invoices.
• Allowing free text where a lookup table or CHECK belongs, producing five spellings of one status.

Best practices

• Store each fact exactly once; join to read it.
• Copy a value only when it is genuinely a different fact - a point in time snapshot - and say so in a comment.
• For every cached aggregate, write the reconciliation query at the same time and schedule it.
• Push validation into the schema wherever the rule is absolute.
• Prefer lookup tables to free text for anything with a fixed set of values.

Practice

1. Identify the insert, update and delete anomaly in a table storing student, course, instructor, instructor_phone.
2. Explain why order_items.unit_price is not a normalisation error.
3. Write the reconciliation query for a cached customers.order_count column.