Breaking Tables Down to Reduce Redundancy
The decomposition strategy
To eliminate redundancy, we decompose DirectedComplete into three tables: one for each entity set and one for the relationship.
Movies(movie_id, title, year)
People(person_id, name, birthYear)
Directed(movie_id, person_id)Entity tables
Each E/R entity becomes a relational table. The entity’s attributes become the table’s columns. The entity’s key becomes the table’s primary key.
| Entity | Table |
|---|---|
| Movies | Movies(movie_id, title, year) |
| People | People(person_id, name, birthYear) |
Each row in Movies stores exactly one movie. Each row in People stores exactly one person. No duplication.
Relationship tables
Each E/R relationship also becomes a relational table. For the Directed relationship (a many-to-many relationship between Movies and People), we create:
Directed(movie_id, person_id)The key of Directed is the combination (movie_id, person_id). This is called an “all-key” table because every column is part of the primary key. This pattern is standard for many-to-many relationships: the relationship table has no attributes of its own beyond the foreign keys to the participating entities.
A concrete example
Suppose the database contains the following:
Movies:
| movie_id | title | year |
|---|---|---|
| m01 | The Matrix | 1999 |
| m02 | Speed Racer | 2008 |
| m03 | Cloud Atlas | 2012 |
People:
| person_id | name | birthYear |
|---|---|---|
| p01 | Lana Wachowski | 1965 |
| p02 | Lilly Wachowski | 1967 |
Directed:
| movie_id | person_id |
|---|---|
| m01 | p01 |
| m01 | p02 |
| m02 | p01 |
| m02 | p02 |
| m03 | p01 |
| m03 | p02 |
| m03 | NULL |
Each fact is stored in exactly one place. Lana Wachowski’s name appears only once, in the People table. If we need to correct it, we update one row.
Reconstructing the original view
The original DirectedComplete table can be reconstructed using a SQL JOIN:
SELECT movie_id, title, year, person_id, name, birthYear
FROM movies
JOIN directed ON directed.movie_id = movies.movie_id
JOIN people ON people.person_id = directed.person_id;The join combines the three tables on their common key columns, producing exactly the same columns we had in DirectedComplete — but without the redundancy.
The fundamental trade-off
| Single-table design | Decomposed design | |
|---|---|---|
| Redundancy | High | None (or minimal) |
| Insertion anomalies | Present | Absent |
| Deletion anomalies | Present | Absent |
| Update anomalies | Present | Absent |
| Query simplicity | Simple (one table) | Requires JOINs |
| Write performance | Poor (write amplification) | Good |
The decomposed design is the “good” design: we accept the cost of writing JOIN queries in exchange for eliminating redundancy and its associated anomalies. JOINs are the price we pay for a well-normalised schema.
Both entities and relationships are tables
A key insight: in the relational model, both E/R entities and E/R relationships are implemented as tables. There is no separate construct for relationships. A relationship table is distinguished only by its content — it contains foreign keys referencing entity tables.
Summary
- Decompose a big table into one table per entity set and one per relationship.
- Entity tables store attributes; relationship tables store foreign keys.
- Many-to-many relationships produce all-key tables.
- JOINs reconstruct the combined data when needed.
- The cost of JOINs is the price of eliminating redundancy and anomalies.