Redundant Data and Update Anomalies
Definition
Data in a database is redundant if it can be deleted and then reconstructed from the data remaining in the database.
Why is redundancy problematic?
If data is held in more than one place, copies can disagree. When a value is updated in one location but not another, the database enters an inconsistent state.
In a database supporting a high rate of update transactions, high levels of data redundancy imply that correct transactions may have to acquire many locks to consistently update redundant copies. This reduces throughput.
When is redundancy useful?
Redundant data can also be useful. If updates are rare, having multiple copies can:
- Increase read bandwidth (more replicas to read from)
- Speed up lookup (a denormalised table avoids joins)
- Reduce data movement cost (a local copy avoids network round-trips)
What do we mean by ‘multiple copies’?
Two components contribute to query cost:
| Component | Description |
|---|---|
| Lookup cost | Finding the appropriate records via searching and key matching |
| Data movement cost | Sending the query and receiving the result over the network |
Schema examples
Consider these schemas and their performance characteristics:
Example 1: duplicate tables
| Schema | Structure |
|---|---|
| R0(K, V) | One table |
| R1(K, V) and R2(K, V) | Two identical tables |
Redundancy here might increase read throughput if the DBMS can parallelise reads across the two tables. However, every write must update both copies, doubling the write cost.
Example 2: split vs. combined
| Schema | Structure |
|---|---|
| A0(K, V1, V2) | Combined table |
| A1(K, V1) and A2(K, V2) | Split tables |
Combining reduces lookup costs (one table scan instead of two) but may lock more data per transaction. A query needing only V1 must still lock the entire row in A0, including V2.
The fundamental tension
More redundancy ---> Faster reads
More redundancy ---> Slower writesRedundancy speeds up reads but slows down writes. The right balance depends on the application’s read-to-write ratio.
Update anomalies
An update anomaly occurs when changing a single logical fact requires modifying multiple rows, and a partial update leaves the database inconsistent.
Example: a GP’s surgery address is stored in every patient record. If the surgery moves, every patient row must be updated. A partial update leaves some patients pointing to the old address.
| Anomaly type | Description |
|---|---|
| Update anomaly | Changing data in one place but not in others |
| Insertion anomaly | Cannot insert a fact without also inserting unrelated data |
| Deletion anomaly | Deleting a fact inadvertently removes other information |
Summary
- Redundant data can be reconstructed from remaining data.
- Redundancy causes disagreement between copies and update anomalies.
- Redundancy can also improve read performance when writes are rare.
- The fundamental trade-off: redundancy helps reads, hurts writes.