Introduction to Decomposition
Decomposition is the process of splitting one table into two or more smaller tables to eliminate redundancy and the anomalies that come with it, and it is the mechanism behind every step of normalization. A correct decomposition should be a lossless join, rebuilding exactly the original data with no lost or spurious rows, and should preserve dependencies.
Decomposition is the process of splitting one table into two or more smaller tables, done to eliminate redundancy and the anomalies that come with it. It's the practical mechanism behind every step of normalization, moving a table from an unnormalized form toward 1NF, 2NF, 3NF, and beyond is done specifically through decomposition.
The two properties a good decomposition must have
Splitting a table isn't automatically safe. A correct decomposition must satisfy two properties:
Lossless join. If you join the decomposed tables back together, you should get back exactly the original data, no rows lost, and critically, no extra spurious rows introduced that weren't in the original table. A lossy decomposition can actually create fake combinations of data that never existed in the source table, which is a serious integrity problem, not just an inconvenience.
Dependency preservation. Every functional dependency that held in the original table should still be enforceable using only the decomposed tables, without needing to join them back together first just to check a constraint. If a dependency can only be verified by joining tables, that's a sign the decomposition may have made the design harder to maintain correctly, even if it's technically lossless.
A good decomposition achieves both. In rare, more advanced cases (mostly relevant when reaching stricter normal forms like BCNF), a decomposition might be lossless but not fully dependency-preserving, a real tradeoff which is one reason 3NF is often considered "good enough" in practice, since it usually achieves both properties, while BCNF occasionally has to sacrifice dependency preservation for a stricter form of redundancy elimination.
A worked example
Take this table with a real redundancy problem:
| order_id | product_id | product_name | product_price |
|---|---|---|---|
| 1 | 55 | Keyboard | 49.99 |
| 2 | 55 | Keyboard | 49.99 |
| 3 | 61 | Mouse | 19.99 |
product_name and product_price depend only on product_id, not on order_id, a partial/transitive dependency issue depending on how the key is defined. Decompose it into two tables:
orders
| order_id | product_id |
|---|---|
| 1 | 55 |
| 2 | 55 |
| 3 | 61 |
products
| product_id | product_name | product_price |
|---|---|---|
| 55 | Keyboard | 49.99 |
| 61 | Mouse | 19.99 |
Checking the lossless join property: joining orders and products on product_id reconstructs exactly the original three rows, nothing more, nothing less. This works because product_id is a key in products (unique there), which is precisely the condition that guarantees a lossless join when decomposing this way.
Now consider a bad decomposition of the same original table: splitting it into {order_id, product_name} and {product_id, product_price} instead. Joining these two back together has no shared, meaningful key connecting them correctly, and could easily produce a cross-product of unrelated rows, product names and prices getting mismatched in ways that never existed in the original data. That's a lossy decomposition, and it's a genuinely serious integrity bug, not just an inefficiency.
Where this matters in real systems
Every time a schema designer splits a table to fix redundancy, whether following formal normalization steps or just applying good judgment, they need to verify the split is actually lossless, usually by making sure the common column used to reconnect the tables is a real key in at least one of the resulting tables. Getting this wrong doesn't just create ugly data, it can silently fabricate relationships between records that were never actually true in the original data.
What interviewers actually ask about decomposition
- "What does it mean for a decomposition to be lossless?" Joining the decomposed tables back together reconstructs exactly the original data, with no rows lost and no spurious extra rows created.
- "What does dependency preservation mean, and why does it matter?" Every functional dependency from the original table should be checkable using only the decomposed tables, without needing a join, which keeps constraints easy to enforce directly.
- "How do you know if a decomposition is lossless?" A common sufficient condition: the common column(s) shared between the two resulting tables must form a key (unique identifier) in at least one of them.
- "Can a decomposition be lossless but not dependency-preserving?" Yes, this can happen in some BCNF decompositions, a real tradeoff worth knowing exists, not just a hypothetical.
Key takeaways
- Decomposition splits a table into smaller tables to eliminate redundancy, and is the mechanism behind every step of normalization.
- A correct decomposition must be lossless (joining back reconstructs the original data exactly) and should preserve dependencies where possible.
- A lossless join is typically guaranteed when the shared column between decomposed tables is a real key in at least one of them.
- A lossy decomposition is a genuine data-integrity bug: it can fabricate combinations of data that never actually existed.
More in RDBMS Design
Browse all DBMS topics
Log in to save which topics you've read across every subject.
Frequently asked questions
What does it mean for a decomposition to be lossless?
Joining the decomposed tables back together reconstructs exactly the original data, with no rows lost and no spurious extra rows created.
What does dependency preservation mean, and why does it matter?
Every functional dependency from the original table should be checkable using only the decomposed tables, without needing a join, which keeps constraints easy to enforce directly.
How do you know if a decomposition is lossless?
A common sufficient condition: the common column(s) shared between the two resulting tables must form a key (unique identifier) in at least one of them.
Can a decomposition be lossless but not dependency-preserving?
Yes, this can happen in some BCNF decompositions, a real tradeoff worth knowing exists, not just a hypothetical.