Database Normalization

Normalization is not a command that automatically produces a perfect schema. It is a reasoning process based on keys and functional dependencies. Practical schemas balance that reasoning with query patterns, database features, audit requirements, and measured performance.

Begin With Facts And Keys

Suppose a spreadsheet stores orders like this:

order_id | order_date | customer_id | customer_email | products
1001     | 2026-06-10 | 42          | ada@example.com | P10:Notebook:2:499,P20:Pen:3:129

The products field contains several repeating values. Product names and prices are copied into a comma-separated encoding. SQL cannot address one line reliably without parsing application-specific text.

Before decomposing the data, list the facts:

• an order has an ID, date, and customer;
• a customer has an ID and email;
• an order contains one or more lines;
• each line identifies a product and quantity;
• a product has an ID and current catalogue name;
• an order line may need the price agreed at purchase time.

A key uniquely identifies a row. A functional dependency describes determination: customer_id -> customer_email means one customer ID determines one customer email in this model.

Understand Anomalies

A poorly structured table creates three classic problems.

An update anomaly occurs when the same fact appears in many rows. If a customer email is copied into every order, changing it requires multiple updates. Missing one creates contradictory values.

An insertion anomaly occurs when one fact cannot be stored without an unrelated fact. If product details exist only inside order rows, the catalogue cannot record a product before its first sale.

A deletion anomaly occurs when deleting one fact accidentally removes another. Deleting the only order containing product P20 would erase the only stored description of that product.

Normalization moves independently meaningful facts into tables whose keys determine those facts.

First Normal Form: One Value Per Attribute

First Normal Form, or 1NF, requires a relational shape without repeating groups in one row. Each row-column intersection should contain one value from its domain, and rows should be distinguishable by a key.

Split the product list into order-line rows:

CREATE TABLE order_lines_1nf (
    order_id BIGINT NOT NULL,
    product_id VARCHAR(40) NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price_pence INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

This assumes a product appears at most once per order. If the business allows multiple lines for the same product, use a separate line_number or line ID in the key.

1NF does not mean every text value must be split into its smallest imaginable pieces. An email address is one value for ordinary identity and delivery operations even though it contains local and domain parts. Atomicity here is relative to how the application uses the value.

Functional Dependencies Drive Later Forms

Consider one combined table:

(order_id, product_id) -> quantity, unit_price_pence
order_id -> order_date, customer_id
customer_id -> customer_email
product_id -> product_name

The composite key is (order_id, product_id). Quantity and agreed unit price depend on the whole key. Order date and customer ID depend only on order_id. Product name depends only on product_id. Customer email depends on customer_id, which is not the table key.

Those dependencies reveal where facts belong.

Second Normal Form: Remove Partial Dependencies

Second Normal Form, or 2NF, requires 1NF and removes non-key attributes that depend on only part of a composite candidate key.

In the combined order-line table, order_date depends only on order_id, and product_name depends only on product_id. Move them:

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id BIGINT NOT NULL,
    customer_email VARCHAR(255) NOT NULL
);

CREATE TABLE products (
    id VARCHAR(40) PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE order_lines (
    order_id BIGINT NOT NULL,
    product_id VARCHAR(40) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_pence INTEGER NOT NULL CHECK (unit_price_pence >= 0),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Tables with a single-column candidate key cannot have a dependency on only part of that key, so the specific 2NF problem arises most visibly with composite keys.

Third Normal Form: Remove Transitive Dependencies

Third Normal Form, or 3NF, requires 2NF and removes non-key facts that depend on another non-key fact rather than directly on a key.

In orders, the dependency is:

order_id -> customer_id -> customer_email

The email belongs to the customer, not the order. Create a customer table:

CREATE TABLE customers (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id BIGINT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Now changing the customer's current email requires one row update. Orders reference the customer by stable identity.

There is an important historical-data question: should an old order display the customer's current email or the email used at checkout? If the checkout email is part of the historical order record, storing it on the order under a name such as billing_email_at_purchase is not accidental duplication. It is a different fact with different time semantics.

Boyce-Codd Normal Form

Boyce-Codd Normal Form, or BCNF, strengthens the dependency rule: for every non-trivial functional dependency X -> Y, X should be a superkey.

Most straightforward 3NF business tables also satisfy BCNF. Differences appear when a table has overlapping candidate keys and dependencies whose determinant is not a superkey.

Suppose tutors teach subjects, each subject has one assigned tutor, and students can study several subjects:

(student_id, subject) -> tutor
 tutor -> subject

If each tutor teaches only one subject, tutor -> subject. Yet tutor alone does not identify a student's enrollment row. That dependency can create redundancy. Decompose into tutor assignments and student-tutor enrollment according to the exact business keys.

Do not apply BCNF mechanically without validating the real rules. If tutors can teach multiple subjects, the dependency is false and the proposed decomposition is wrong.

Candidate Keys Matter

A primary key is the candidate key selected as the main row identifier. A table can have other candidate keys.

For users, both an internal numeric ID and a normalized email might be unique. The schema may choose the ID as primary and enforce email with UNIQUE. Normalization reasoning must consider all candidate keys, not only the primary key.

Surrogate IDs do not erase dependencies. Adding id BIGINT to a table full of repeated customer and product facts does not normalize it. The natural dependencies remain and should be represented through constraints and table boundaries.

Preserve Historical Facts Deliberately

A product's current catalogue price and an order line's agreed price are different facts:

products.current_price_pence
order_lines.unit_price_pence_at_purchase

Copying the current price into the order line at checkout is correct historical modeling. Looking up the current product price when rendering an old invoice would rewrite history.

Likewise, shipping addresses, tax rates, product descriptions, and legal business names may need snapshots. Name snapshot columns clearly so maintainers do not mistake them for normalization errors.

Many-To-Many Relationships Need Junction Tables

Products can belong to many categories, and categories can contain many products. Do not store category IDs as comma-separated text.

CREATE TABLE categories (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE product_categories (
    product_id BIGINT NOT NULL REFERENCES products(id),
    category_id BIGINT NOT NULL REFERENCES categories(id),
    PRIMARY KEY (product_id, category_id)
);

The composite primary key prevents duplicate membership. The junction table can also contain facts about the relationship, such as display order or assignment date.

Repeating Columns Are Another Warning

Columns such as phone_1, phone_2, and phone_3 impose an arbitrary limit and make searching awkward. Use a child table:

CREATE TABLE customer_phone_numbers (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    phone_number VARCHAR(40) NOT NULL,
    label VARCHAR(30) NOT NULL,
    UNIQUE (customer_id, phone_number)
);

The child rows express a one-to-many relationship. Constraints can prevent duplicates and validate allowed labels.

JSON Columns Need A Deliberate Boundary

JSON columns are useful for data whose shape is variable, document-like, provider-specific, or rarely queried relationally. They are not a default escape from schema design.

Keep a value relational when it needs joins, uniqueness, foreign keys, range filters, frequent updates, or independent indexing. A list of order lines belongs in relational rows. A preserved webhook payload or optional provider metadata may fit JSON.

A JSON document can still require versioning and validation. Know which fields are authoritative, how migrations occur, and whether the database can index required paths. Avoid storing important IDs only inside opaque JSON when relationships matter.

Normalization Does Not Eliminate Joins

Normalized schemas require joins to reconstruct views. That is expected. Relational databases are designed to join indexed keys.

SELECT
    o.id,
    c.email,
    p.name,
    ol.quantity,
    ol.unit_price_pence
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN order_lines AS ol ON ol.order_id = o.id
JOIN products AS p ON p.id = ol.product_id
WHERE o.id = :order_id;

Do not denormalize solely because a query contains several joins. Measure the plan, indexes, row counts, and latency first. Poor indexes or an N+1 application loop may be the actual problem.

Normalization And Domain Boundaries

A normalized database model is not automatically the best object model or API response. PHP can load rows into value objects, aggregates, read models, or DTOs shaped for the use case.

Do not expose table structure directly as a public API contract. Database normalization optimizes storage integrity; application models organize behavior; API representations serve clients. These concerns influence each other but do not need identical shapes.

A Practical Normalization Process

For a proposed table:

1. Identify what one row represents.
2. List candidate keys.
3. List functional dependencies supported by real business rules.
4. Remove repeating groups to reach 1NF.
5. Remove dependencies on part of a composite key for 2NF.
6. Remove transitive dependencies for 3NF.
7. Review every determinant for BCNF.
8. Add primary, unique, foreign-key, and check constraints.
9. Identify intentional historical snapshots.
10. Test important queries and only then consider measured denormalization.

This process is more reliable than memorizing slogans such as "never duplicate data." Some duplication represents a different historical fact or an intentional performance model.

Review Checklist

When reviewing a schema, ask:

• What fact does one row represent?
• Which columns form each candidate key?
• Which attributes depend on which keys?
• Are lists hidden in text, JSON, or numbered columns?
• Does one fact appear in many rows and require synchronized updates?
• Can a fact be inserted without an unrelated record?
• Can deleting one record erase the only copy of another fact?
• Are snapshot values clearly distinguished from current reference data?
• Do constraints express the intended relationships?
• Is proposed denormalization based on measured need?

What You Should Be Able To Do

After this lesson, you should be able to identify update, insertion, and deletion anomalies; describe functional dependencies; and normalize a worked dataset through 1NF, 2NF, and 3NF. You should understand the stronger BCNF rule and why candidate keys matter.

You should also be able to recognize legitimate historical snapshots, model many-to-many and one-to-many relationships, choose a deliberate JSON boundary, and explain why adding a surrogate ID or avoiding joins does not by itself produce a sound relational design.