For a schema you are designing, how do you decide whether to decompose all the way to Boyce-Codd Normal Form or stop at Third Normal Form?
answer
- choice only exists with overlapping candidate keys
- 3NF cost = duplicated fact; BCNF cost = unenforceable rule
- if decomposition preserves dependencies, just take BCNF
- challenge the requirement before engineering around it
- document the tolerated dependency plus a detection query
basics
~20 sDecide per table. Go to BCNF when the redundant fact changes often enough that multi-row updates risk inconsistency. Stay at 3NF when reaching BCNF would lose a dependency you must enforce and no key on a single table could replace it. Most tables never present the choice.
solid answer
~50 sThe choice only arises in the narrow band where a table is 3NF but not BCNF — a non-superkey determinant pointing at a prime attribute, which needs overlapping candidate keys. Almost every table with a surrogate key skips the question entirely. When it does arise, I weigh two costs. **Staying at 3NF** keeps a fact duplicated across rows; the risk is proportional to how often that fact changes and how many write paths touch it. **Going to BCNF** may push a dependency across two tables, where the engine can no longer enforce it with a key, leaving triggers, reconciliation, or nothing. So: if the duplicated value is effectively static reference data and the lost dependency is a hard invariant, stop at 3NF. If the value churns and the lost dependency is soft or can be re-expressed as a unique index, decompose. And first ask whether the awkward dependency is a real business rule — often it is not, and the conflict disappears.
go deeper
Know that BCNF is stricter and that designers sometimes stop at 3NF; do not attempt a policy.
Name the two costs — duplicated facts versus a dependency no key can enforce — and pick one for a concrete example.
Drive from the workload: update frequency of the duplicated value, number of write paths, and whether the lost dependency can be re-expressed as a unique index.
Own the governance angle — where invariants live, how tolerated redundancy is documented and monitored, and why this decision is distinct from performance denormalisation.
## The decision is narrower than it sounds BCNF and 3NF only differ for tables with overlapping composite candidate keys, where a non-superkey determinant determines a column that belongs to some candidate key. A table with one surrogate primary key and properly modelled entities cannot be in that band. So the honest first move is to notice how rarely the question is live: in a typical transactional schema it might apply to one or two junction tables that have absorbed an attribute of a parent entity. That framing matters in an interview because the wrong answer is a blanket policy — "always BCNF" or "3NF is enough in practice" — applied to tables where the two forms are identical anyway. ## What you are actually trading **Cost of staying at 3NF: duplicated facts.** The determined value is stored once per row of the containing table. Three questions size the risk. How often does that value change? How many independent code paths can write it — services, batch jobs, admin tooling, migrations? What is the blast radius of an inconsistency — a cosmetic mismatch, or a billing figure? A course-to-teacher mapping that changes once a term across one write path is low risk. A price or an entitlement duplicated across a million rows and written by four services is not. **Cost of going to BCNF: an unenforceable dependency.** If the decomposition is dependency-preserving, there is no trade at all — take BCNF and move on. The real decision only bites when a dependency ends up spanning both tables. Then the engine can no longer reject a bad write with a key, and you are choosing between a trigger (enforced but easy to get wrong under concurrent writes, where two transactions each see a legal snapshot and jointly break the rule), a periodic reconciliation job (detects, does not prevent), or application checks (bypassed by every write path that forgets them). ## The questions I ask in order 1. **Is the awkward dependency a real requirement?** Rules like "a student may not take two courses from the same teacher" are frequently scheduling conventions that nobody will defend. Retiring one requirement is cheaper than any enforcement mechanism. Ask the domain expert before you ask the algorithm. 2. **Is the model wrong rather than the normal form?** A 3NF-not-BCNF table is usually a junction table holding an attribute that belongs to one of its parents. Moving the attribute home is the same split arrived at by modelling judgement, and it usually reads better to the next engineer than any dependency argument. 3. **Can the lost dependency be re-expressed as a unique constraint somewhere?** Sometimes carrying one deliberately redundant column plus a unique index restores engine-level enforcement at a fraction of the original redundancy. That is a legitimate outcome: BCNF for the bulk of the data, one controlled copy to keep the invariant checkable. 4. **What is the read and write profile?** A table read constantly and updated monthly tolerates redundancy well. A hot table with concurrent writers to the duplicated column will eventually diverge, whatever the review process says. 5. **Who enforces it if the database will not?** If the answer is "the service layer", assume the invariant will be violated at some point by a backfill and plan a reconciliation query and an alert, or choose differently. ## Where I land by default Normalise to BCNF where it is free — which is most of the time, because most decompositions preserve dependencies. Stop at 3NF when the alternative is an invariant no constraint can hold and the duplicated value is stable reference data. Never leave a 3NF-not-BCNF table undocumented: record the dependency the schema tolerates, the reason, and the query that detects a violation, so the next reviewer inherits a decision rather than an accident. Finally, keep this separate from denormalising for performance. Stopping at 3NF is *declining to remove* a redundancy that theory says exists; deliberately duplicating a column to avoid a join is *introducing* one for read speed. They can look similar in the finished DDL, but the review question, the enforcement plan, and the exit criteria are different, and conflating them is how a schema loses track of which redundancies are intentional.
- How would you monitor a table you consciously left at 3NF instead of BCNF?Write the query that finds rows contradicting the tolerated dependency — group by the determinant and count distinct values of what it determines, alerting on any count above one. Schedule it and record it next to the schema, so the accepted risk is observable rather than assumed away.
- Does reaching BCNF ever hurt read performance?It adds a join, which in an OLTP schema against an indexed key is usually negligible. If profiling shows the join genuinely dominates a hot path, that is a separate, explicit denormalisation decision with its own synchronisation plan — not a reason to describe a normalisation shortfall as a performance design.
saying these in an interview costs you the question
- Stating a universal policy (always BCNF, or 3NF is fine) without noting the two forms coincide for most tables.
- Justifying stopping at 3NF for query performance; that is a denormalisation argument, not a normalisation one.
- Assuming reaching BCNF always costs a dependency, when most decompositions preserve all of them.
- Choosing BCNF and delegating the lost invariant to application code with no reconciliation or alerting.
- Leaving the tolerated dependency undocumented, so the next reviewer reads it as an oversight.