Data Modeling & Schema Design

Normalization Through BCNF

Normalization decomposes a relation so each fact is governed by the appropriate key, reducing update anomalies without losing information. The practical path from 1NF through 3NF and BCNF is driven by dependencies, not by counting tables.

First normal form gives each attribute one value from its domain.
Second normal form removes non-key facts dependent on only part of a composite key.
Third normal form removes transitive non-key dependencies while preserving a synthesis path.
BCNF requires every determinant of a nontrivial FD to be a superkey.
Normalization removes representation anomalies; it cannot decide whether a business rule or identity assumption is correct.
A sound decomposition must be lossless, and dependency preservation should be evaluated separately.

Stepwise commerce normalization

From a denormalized order document to focused relations
Stage	Shape	Problem removed
Unnormalized	`ORDER(order_id, customer…, items[{product…, qty, price}])`	Repeating group is not independently constrainable
1NF	`ORDER_LINE_FLAT(order_id, line_no, customer_id, customer_email, placed_at, product_id, sku, product_name, category_id, category_name, qty, unit_price)`	One line fact per tuple
2NF	`ORDERS` + `ORDER_ITEMS`; product facts still repeated on lines	Header attributes no longer repeat for every line
3NF	`CUSTOMERS`, `ORDERS`, `ORDER_ITEMS`, `PRODUCTS`, `CATEGORIES`	Customer, product, and category facts each have their own determinant
BCNF check	Every nontrivial determinant in each relation is a candidate key	No remaining FD has a non-key determinant

Normalized logical schema

CREATE TABLE customers (
  customer_id BIGINT PRIMARY KEY,
  email VARCHAR(320) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL
);

CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
  placed_at TIMESTAMP NOT NULL,
  status VARCHAR(30) NOT NULL
);

CREATE TABLE products (
  product_id BIGINT PRIMARY KEY,
  sku VARCHAR(80) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL
);

CREATE TABLE order_items (
  order_id BIGINT NOT NULL REFERENCES orders(order_id),
  line_no INTEGER NOT NULL,
  product_id BIGINT NOT NULL REFERENCES products(product_id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(19, 4) NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (order_id, line_no)
);

`unit_price` remains on the line because transaction price is a line fact, not the product's mutable current price.

Anomalies removed from the flat relation
Anomaly	Flat-table failure	Normalized result
Update	Changing a product name requires every historical line copy	Update one `products` tuple
Insert	A product cannot exist before its first order	Insert it independently into `products`
Delete	Deleting the last line for a product erases the catalog fact	Order history and catalog lifecycle are independent