Begin with dependencies, not table count
Normalization uses semantic dependencies to reduce redundant representation and update anomalies. Its objective is not mechanically maximizing the number of tables. A relation is in BCNF when every nontrivial functional dependency X → Y has a superkey as its determinant X. The starting point is therefore which attributes determine which others. SQL tables also bring nulls, duplicate rows and implementation-specific constraints into the picture. Keep the relational reasoning separate from the details needed to implement it correctly. A normal-form label is useful when its assumptions and the dependencies being checked are made explicit.
Decompose the employee example
In Employee(employee_id, department_id, department_name), suppose employee_id determines the whole row while department_id determines department_name. Department ID is not a superkey because it does not identify one employee. That dependency violates BCNF. Decomposing into Employee(employee_id, department_id) and Department(department_id, department_name) stores each department name in one place. Joining on department_id can reconstruct the intended information under the relevant dependency and integrity assumptions. The implementation still needs appropriate keys and references so that employee rows cannot silently point to missing departments or unexpected duplicate department definitions.
Check losslessness and preserved constraints
A lossless decomposition permits the original relation to be reconstructed correctly by joining the decomposed relations. Dependency preservation asks a different question: can the original dependencies be enforced using constraints on the smaller relations without having to join them? A BCNF decomposition can be lossless yet fail to preserve every dependency in that convenient form. Third-normal-form designs offer a different trade-off involving dependency preservation. Identify which rule would require a cross-table check. Correct reconstruction and convenient constraint enforcement are separate properties, and both affect the practical cost of keeping data valid.
Denormalization needs an update protocol
Duplicating a value for read performance or aggregation does not remove the underlying consistency responsibility. A denormalized design needs an authoritative copy, an update protocol and a decision about whether temporary disagreement is acceptable. Caches and summary tables also need a reconstruction strategy. Avoid treating normalized designs as inherently slow or denormalized designs as inherently fast. Evaluate actual read and update workloads and include the work needed to maintain correctness. A complete explanation covers constraints, reconstruction and query costs together, rather than optimizing one read while leaving the duplicated data’s update behaviour unspecified.