Data redundancy and inconsistency

Databases · Redundancy to inconsistency

Sam moves house. Who updates what?

Step through the same change twice: first on a flat table, then on linked tables.

Before you step: Sam Patel's address is written on three orders. Sam moves. What would have to go wrong for the database to hold two different addresses for Sam?

What you need to know

• Data redundancy means the same data is stored in more than one place in a database.
• Data inconsistency means copies of the same data don't match.
• Have a goYour friend says: "My phone number is on three school forms. Two say 07700 900123 and one says 07700 900321." Redundancy, inconsistency, or both?

  Both.

  The number is stored in more than one place (redundancy) and the copies don't match (inconsistency).

• Redundancy is the repeating; inconsistency is the copies disagreeing. They are not the same thing.
• Redundancy is what makes inconsistency possible: with only one copy, there is nothing to disagree with.
• Have a goMaya is sure that if a database has redundancy, it must be inconsistent. What could be true of a redundant database that proves her wrong?

  All the copies match, so it is redundant but still consistent.

  Redundancy only makes inconsistency possible; it takes copies that disagree for the data to be inconsistent.

• It goes wrong when an update changes some copies but misses others.
• Redundancy also wastes storage space, and makes updates slower and more error-prone.
• The fix is to split the data into linked tables, so each fact is stored once.
• A primary key identifies each record in a table.
• A foreign key is a field that holds another table's primary key, and that is how the tables are linked.
• Have a goAn Orders table stores "CustomerID 7" on each order. That ID is the primary key of a record in the Customers table. In Orders, is it a primary key or a foreign key?

  A foreign key.

  It sits in Orders but holds the primary key of a record in another table, which is exactly what a foreign key is.

• Now a change is made in one place, so the risk of inconsistency is reduced.

The big picture

Storing the same data in more than one place is called data redundancy. If an update changes some copies but misses others, the copies stop matching, which is data inconsistency. Redundancy also wastes storage space and makes updates slower and more error-prone. Splitting the data into linked tables, joined by a primary key and a foreign key, stores each fact once so a change is made in one place.

⚠ Watch out

Treating redundancy and inconsistency as two names for the same problem. Redundancy is the repeated data. Inconsistency is what you get when the copies stop matching. A redundant database can still be consistent, until a change misses a copy.

Memory hook

Redundancy is repeating. Inconsistency is repeating that fell out of step. The cure: say it once and link it by key.

Check yourself

Cover the page and finish these in your own words: "Redundancy means ___. Inconsistency means ___. Redundancy makes inconsistency possible because ___." Then say what a foreign key stores.