Write dependencies before splitting tables
Imagine one order table holding order ID, product ID, customer ID, current address, current product price and quantity. Repeating an address across many rows makes a customer update redundant and can leave contradictory current addresses. Normalization is not arbitrary fragmentation; it reasons from functional dependencies about which attributes determine others and where updates duplicate information.
Assume order determines customer, customer determines current address, product determines current price, and order/product together determine quantity. If one order may contain several distinct lines for the same product, that pair is not necessarily a line identifier and another attribute is needed. A few observed rows cannot prove a business dependency for every valid future row. State dependencies as domain contracts, then use sample data to illustrate their consequences.
Separate current facts from historical agreement
Product determining price applies here to one current price. A historical order may need its agreed unit price preserved despite later catalog changes. That fact can belong to an order line rather than be determined by product alone. Before transferring a normalization diagram into a product, define what time and business event each attribute describes.
Current contact address and historical shipping address likewise need not be the same fact. Looking up today's customer address may not reconstruct an old delivery destination. The SQL example decomposes a small current-fact relation, not a complete order-history schema. Reducing apparent duplication must not collapse genuinely different temporal facts. Dependency analysis begins after attribute meanings are fixed, not before. Otherwise formally tidy tables can encode the wrong application information.
Derive candidate keys with closure
Order and product together determine the customer, address, price and quantity under the stated dependencies. The pair is therefore a superkey. Removing order loses which order is represented; removing product loses the specific line product and quantity. The pair is a candidate key in this model. Observed uniqueness in a tiny sample is weaker than determination under all valid instances.
Compute closure by repeatedly applying dependencies to the attributes already available. Check that all attributes are reached and that no component can be removed while preserving that property. Do not infer a key merely from a column name. New dependencies can change candidate keys, so preserve assumptions and intermediate closure reasoning. A PRIMARY KEY constraint enforces a chosen rule; it does not automatically discover the business semantics that justify choosing it.
Check reconstruction in both directions
Split customers/addresses, orders/customers, products/prices and order/product quantities, then join on the shared identities. The SQL example compares original and reconstructed rows using EXCEPT in both directions and asserts exactly three reconstructed rows. One direction alone could miss extra spurious combinations that still include every original row.
Lossless decomposition is a property under the dependencies, not merely equality for one sample. Connect the example to the condition that common attributes determine an appropriate component. CREATE TABLE AS in the experiment does not automatically carry every constraint needed for future updates. A live schema must separately establish keys, foreign keys and relevant business checks. Sample reconstruction, a general losslessness argument and enforcing future validity are three different parts of the design.
BEGIN;
CREATE TEMP TABLE original(order_id text,product_id text,customer_id text,address text,price int,qty int,PRIMARY KEY(order_id,product_id));
INSERT INTO original VALUES('O1','P1','C1','North',10,2),('O1','P2','C1','North',20,1),('O2','P1','C1','North',10,1);
CREATE TEMP TABLE customers AS SELECT DISTINCT customer_id,address FROM original;
CREATE TEMP TABLE orders AS SELECT DISTINCT order_id,customer_id FROM original;
CREATE TEMP TABLE products AS SELECT DISTINCT product_id,price FROM original;
CREATE TEMP TABLE lines AS SELECT order_id,product_id,qty FROM original;
CREATE TEMP TABLE restored AS SELECT order_id,product_id,customer_id,address,price,qty FROM lines JOIN orders USING(order_id) JOIN customers USING(customer_id) JOIN products USING(product_id);
DO $$BEGIN
IF EXISTS((SELECT * FROM original EXCEPT SELECT * FROM restored) UNION ALL (SELECT * FROM restored EXCEPT SELECT * FROM original)) THEN RAISE EXCEPTION 'lossy reconstruction'; END IF;
IF (SELECT count(*) FROM restored)<>3 THEN RAISE EXCEPTION 'wrong row count';END IF;
END$$;
SELECT count(*) AS restored_rows FROM restored;
CREATE TEMP TABLE ac(a text,c text,PRIMARY KEY(a,c));
CREATE TEMP TABLE cb(c text PRIMARY KEY,b text);
INSERT INTO ac VALUES('a1','c1'),('a1','c2');
INSERT INTO cb VALUES('c1','b1'),('c2','b1');
SELECT a,b,count(DISTINCT c) AS c_values FROM ac JOIN cb USING(c) GROUP BY a,b HAVING count(DISTINCT c)>1;
ROLLBACK;
Read 3NF and BCNF as different conditions
BCNF requires the determinant of every nontrivial functional dependency to be a superkey. Third normal form also permits a non-superkey determinant when the dependent attribute is prime, meaning it belongs to some candidate key. The informal slogan about nonkeys depending on nonkeys does not fully express that distinction.
For R(A,B,C) with AB determining C and C determining B, candidate keys are AB and AC. Every attribute is prime. C alone does not determine A, so C-to-B violates BCNF, while prime B allows the dependency under3NF. Do not define prime attributes only from the one key chosen as PRIMARY KEY. Candidate-key analysis is what supplies the relevant classification, and it must remain consistent with the assumed dependency set.
| Attributes | Closure | Candidate key? |
|---|---|---|
| AB | ABC | Yes |
| AC | ABC | Yes |
| C | BC | No: A is missing |
Separate losslessness from dependency preservation
Decomposing the example into CB and AC is lossless under the original dependencies because shared C determines CB. Yet checking each table's local keys does not necessarily enforce AB-to-C. Insert (a1,c1) and (a1,c2) into AC and map both c values to b1 in CB. Local keys hold, but their join gives two C values for one A/B pair.
Thus lossless reconstruction and dependency preservation are separate properties. A BCNF split does not make every original business constraint locally enforceable. A design may favor a dependency-preserving3NF structure or explicitly maintain cross-table conditions after a stronger decomposition. Normal-form labels are not simply quality rankings. Explain which redundancy is removed and where the corresponding integrity checks will live.
Combine update meaning with read cost
Decomposition can centralize an address update while adding joins to reads. Evaluate query mix, update frequency and historical requirements. Neither one extra join proves a slowdown nor normalization guarantees faster overall service. Verify equivalent business answers before comparing plans and indexes for the changed schema.
A read projection or cache may intentionally duplicate values, but then define the source of truth, propagation, allowed staleness and rebuilding procedure. Intentional derived redundancy differs from accidental contradictory updates. When connecting to CQRS, keep canonical functional dependencies separate from projection lag. Understanding normalization supports a deliberate duplication choice; it does not demand either maximal fragmentation or collapsing every table back into one to pursue performance.
Separate decomposition from update constraints
A primary key on every decomposed table does not necessarily enforce every original rule. Rejecting an order line whose order does not exist also needs the appropriate reference to the order relation. Conversely, a customer may legitimately exist before placing any order. Independent insertion can be part of the intended meaning, even when that customer contributes no row to an order join. A reconstruction query alone does not decide which entities may exist independently.
SQL null handling needs its own attention. Classical dependency arguments about relation values are not automatically a complete account of unknown values in SQL comparisons. Check required nullability and uniqueness for key columns. Do not assume that adding a UNIQUE constraint makes every engine treat null-containing rows identically.
The CREATE TABLE AS example makes a small reconstruction inspectable; it is not finished operational DDL. For each added constraint, prepare an accepted row and a rejected row under the selected engine's semantics. This connects the mathematical decomposition to the update interface without claiming that splitting tables alone preserves every business rule. Retain both the theoretical assumptions and the concrete constraint behavior.
Exercise: answer both decomposition properties
For R(A,B,C) with AB-to-C and C-to-B, identify keys AB and AC. The relation is3NF but not BCNF because C is not a superkey while dependent B is prime. Splitting into CB and AC can lose local enforcement of AB-to-C. Explain each conclusion from closure and prime-attribute conditions rather than memorized labels.
Now reinterpret current product price as the agreed historical order-line price. Revisit product-to-price before applying the same split. Different historical prices may be valid facts rather than an anomaly under the old current-price contract. State time meaning, keys, dependencies, losslessness and dependency preservation before choosing tables. Those five pieces make the design explainable beyond the small sample rows.