What normalization achieves
Normalization is the process of organizing relational data so each fact is stored in a location that reflects what the fact describes. It reduces unnecessary duplication and protects consistency when records are inserted, updated, or deleted. A design is not normalized merely because it has many tables; the important question is whether each column depends on the key, the whole key, and nothing but the key for the relevant normal form. Understanding the business facts and their dependencies is more useful than mechanically splitting every column.
A table that repeats a customer's email address on every order creates an update anomaly: changing the email requires updating many order rows, and missed rows leave inconsistent values. It also creates an insertion anomaly if a customer must be stored before placing an order, and a deletion anomaly if removing a customer's last order accidentally removes the only copy of their details. Storing customer data once and referencing it from orders avoids these problems.
Separate entities and repeating groups
First normal form encourages a row-and-column structure where each field holds one value from its domain rather than a list of independent values. Storing product identifiers as comma-separated text in an order column makes it difficult to validate a product, index a specific item, or join it reliably. A separate order_items table models each line as a row. This also supports item-specific attributes such as quantity and a price captured at checkout.
CREATE TABLE order_items (
order_id BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED NOT NULL,
quantity INT UNSIGNED NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders (order_id),
FOREIGN KEY (product_id) REFERENCES products (product_id)
) ENGINE = InnoDB;The composite key prevents the same product from appearing twice as separate rows for one order; some business models instead allow repeated lines and use a separate line identifier. The quantity and unit_price belong to the order-product relationship. A unit price at purchase is a historical transaction fact, not necessarily a redundant copy of the current catalog price.
Functional dependencies and normal forms
A functional dependency means one value determines another. If department_id determines a department name, storing that name repeatedly in every employee record makes the name dependent on a non-key attribute rather than directly on the employee key. A separate departments table keeps the department fact with its identifier. Second and third normal forms build on this reasoning by eliminating partial dependencies on part of a composite key and transitive dependencies through non-key attributes. These terms help diagnose design problems, but practical modeling begins with identifying entities, candidate keys, and dependencies.
Normalization does not mean that every derived value is forbidden. Sometimes the business event must preserve a snapshot, such as the shipping address used for a particular order. That value is historically about the transaction and may appropriately live on the order even when the customer later changes their current address. The model should distinguish current customer facts from immutable transaction facts rather than deduplicating solely by matching text.
Trade-offs and intentional denormalization
A normalized design often requires joins to assemble a read model. Joins are a normal part of relational databases; they are not automatically a performance problem. If measured workloads show a bottleneck, indexes, query shape, caching, or a maintained summary may help. Denormalization stores derived or repeated information to optimize a specific workload, but it introduces a consistency obligation: every write path must keep copies aligned, and failures or concurrent updates can produce drift.
Before denormalizing, identify the source of truth, define how the derived value is refreshed, and test recovery from partial updates. Avoid duplicating a field just because joins seem unfamiliar. Use query optimization and EXPLAIN to measure before changing the model.
A practical review method
For every table, state what one row represents and identify the key. Ask whether any attribute is repeated, whether updates require changing many copies, and whether deleting a row could remove an unrelated fact. For each many-to-many relationship, consider a junction table. Add primary keys, foreign keys, unique constraints, and nullability rules that express the domain. Test common insert, update, and delete workflows using representative examples.
Normalization is a tool for preserving meaning and integrity, not a goal of maximizing table count. Store each fact where it belongs, preserve historical snapshots when they represent real events, and denormalize only to meet a measured requirement with a clear consistency strategy. Continue with MySQL relationship patterns, keys and constraints, and joins.