One Big Table and Update Anomalies
The naive approach
Consider a directed E/R model with two entity sets — Movies and People — connected by a Directed relationship. A naive implementation puts everything into a single table:
DirectedComplete(movie_id, title, year, person_id, name, birthYear)This seems convenient: one query, one table, all the data. But it introduces severe problems.
Data redundancy
Each row in DirectedComplete stores a (movie, director) pair. If a director has directed five films, their name and birth year appear in five separate rows. This duplication is the root cause of the three classical update anomalies.
Insertion anomaly
An insertion anomaly occurs when we cannot insert a legitimate piece of data without also providing data that we do not yet have — or that is entirely unrelated.
| Scenario | Problem |
|---|---|
| Insert a new person who has not yet directed any film | Cannot insert: movie_id and title would be NULL, but they are part of the key. Even if we allow NULL, what value should they take? |
| Insert a new film that has no directors yet | Cannot insert: person_id, name, and birthYear would be NULL, but what director do we reference? |
The schema forces us to fabricate nonexistent data or leave it incomplete — both unacceptable.
Deletion anomaly
A deletion anomaly occurs when deleting one fact inadvertently destroys another, independent fact.
Suppose we delete the last film directed by a particular person from the table. We lose not only the fact that the person directed that film, but also the person’s name and birth year — information that we might still need. The person still exists in the real world, but they vanish from our database.
Update anomaly
An update anomaly occurs when a single conceptual change requires multiple physical updates — and failing to apply them all leaves the database in an inconsistent state.
If a director’s name is misspelled in the database, we must correct it in every row where they appear. A transaction that updates only some of those rows (perhaps because of a bug, a partial failure, or concurrent access) creates inconsistent data: the same person appears with two different names in different rows.
This is not merely a theoretical concern. In a production system, a transaction that must scan and lock many rows to make a conceptually simple update degrades throughput and increases the likelihood of deadlocks.
The “one fact, one place” principle
All three anomalies stem from the same root cause: a single fact is stored in multiple places. The fundamental rule of good relational design is:
Each fact should be stored in exactly one place in the database.
When facts are duplicated, any update to that fact must be propagated to all copies. The database system cannot do this automatically — it is the schema designer’s responsibility to avoid duplication in the first place.
Performance implications
Beyond correctness, data redundancy has direct performance consequences:
| Issue | Effect |
|---|---|
| Write amplification | Updating one director’s name requires touching many rows |
| Lock contention | A transaction updating many rows holds locks longer, blocking other transactions |
| Storage waste | Redundant copies consume disk space and buffer-pool memory |
| Cache inefficiency | Duplicated data displaces useful data from the buffer pool |
In a database supporting many concurrent read and write operations, the combination of lock contention and write amplification from redundant schemas can be catastrophic for throughput.
The remedy
The solution is to decompose the single big table into smaller tables — one per entity set and one per relationship. This is the subject of the next note.
Summary
- A single-table implementation of an E/R model introduces data redundancy.
- Redundancy causes insertion, deletion, and update anomalies.
- The principle “one fact, one place” guides good schema design.
- Redundancy also harms write throughput and increases lock contention.
- The answer is to break tables down.