Schema Design and Normalization for Backend Systems
A practical guide to keys, relationships, normal forms, and when to deliberately denormalize a production schema.
Schema Design and Normalization
Schema design is the highest-leverage decision you make in a backend system's data layer. A well-normalized schema prevents entire categories of bugs — but blind normalization also produces schemas that fall over under real query load. This guide covers keys, relationship modeling, the normal forms, and the engineering judgment required to know when to break the rules on purpose.
1. Primary Keys
A primary key uniquely identifies every row in a table. It is the anchor that foreign keys reference and the backbone of how the storage engine physically organizes data.
-- Auto-increment surrogate key (most common default choice)
CREATE TABLE customers (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- Natural key (rare — only when a column is truly immutable and unique)
CREATE TABLE countries (
iso_code CHAR(2) PRIMARY KEY, -- 'IN', 'US', 'DE'
name VARCHAR(100) NOT NULL
);
-- UUID primary key (distributed systems, no auto-increment coordination needed)
CREATE TABLE api_keys (
id BINARY(16) PRIMARY KEY, -- UUID stored as 16 bytes
tenant_id BIGINT UNSIGNED NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);Surrogate keys vs natural keys
| Aspect | Surrogate key (AUTO_INCREMENT / UUID) | Natural key (email, ISO code, SSN) |
|---|---|---|
| Stability | Never changes | Can change (email updates, renames) |
| Meaning | No business meaning — pure identity | Carries business meaning |
| Join performance | Small, fixed-width, fast to index | Often larger, sometimes variable-width |
| Coupling risk | None — safe to expose or not | Business rule changes can break the key |
| Distributed generation | AUTO_INCREMENT needs coordination; UUID doesn't | N/A |
| Recommendation | Default choice for almost everything | Only for genuinely immutable, unique, short values |
Never use something that can change as a primary key. Email addresses get updated, usernames get renamed, phone numbers get reassigned. Every one of these as a primary key means every foreign key referencing it must cascade-update, and every historical record referencing the old value becomes ambiguous. Use a surrogate key and put a UNIQUE constraint on the natural-feeling column instead.
AUTO_INCREMENT vs UUID trade-off
If you need UUID-style globally unique keys but still want good index locality, prefer UUIDv7 or ULID over random UUIDv4. Both are time-ordered, so new rows insert at the "end" of the clustered index (like AUTO_INCREMENT) instead of scattering randomly across B-tree pages — random UUIDv4 primary keys are a well-known cause of write amplification and page-split overhead on large InnoDB tables.
2. Foreign Keys and Referential Integrity
A foreign key enforces that a value in one table must exist in another — the mechanism that keeps relational data consistent.
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
customer_id BIGINT UNSIGNED NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers (id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);Referential actions
ON DELETE / ON UPDATE action | Behavior | Typical use case |
|---|---|---|
RESTRICT (default in most engines) | Blocks the delete/update if referencing rows exist | Prevent deleting a customer with active orders |
CASCADE | Propagates the delete/update to child rows | Deleting a parent order also deletes its order_items |
SET NULL | Sets the FK column to NULL (column must be nullable) | Deleting an employee sets orders.assigned_rep_id to NULL |
NO ACTION | Similar to RESTRICT, checked at end of statement/transaction (dialect-dependent) | Rare, mostly PostgreSQL deferred constraints |
-- Composite foreign key example: order_items belongs to orders
CREATE TABLE order_items (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
CONSTRAINT fk_items_order
FOREIGN KEY (order_id) REFERENCES orders (id)
ON DELETE CASCADE, -- deleting an order deletes its line items
CONSTRAINT fk_items_product
FOREIGN KEY (product_id) REFERENCES products (id)
ON DELETE RESTRICT -- cannot delete a product that's been ordered
);Production trade-off: Many high-scale systems (large sharded MySQL fleets, systems fronted by Vitess) deliberately skip foreign key constraints and enforce referential integrity in application code instead. The reasons: FK checks add write latency, they block certain online schema change tools, and they don't work across shards anyway. This is a legitimate choice at scale — but it means your application and its tests now own a responsibility the database used to guarantee. Don't drop FK constraints "for performance" on a small-to-medium system without this trade-off being a deliberate, documented decision.
3. Modeling Relationships
Relational schemas express three fundamental relationship shapes. Getting the shape right is the difference between a query that's a single join and one that requires application-side stitching.
1:1 — One-to-One
Used to split a table for security, size, or optionality reasons — not something you reach for by default.
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE
);
-- Split off rarely-accessed / sensitive / large columns
CREATE TABLE user_profiles (
user_id BIGINT UNSIGNED PRIMARY KEY,
bio TEXT,
avatar_url VARCHAR(500),
date_of_birth DATE,
CONSTRAINT fk_profile_user
FOREIGN KEY (user_id) REFERENCES users (id)
ON DELETE CASCADE
);A real 1:1 split is justified when: (a) one side has columns that are rarely read (avoid bloating the hot row), (b) one side is optional and would otherwise need many NULL columns, or (c) you want a separate access-control boundary (e.g., user_ssn in its own tightly-permissioned table). If neither applies, just use one table — an unnecessary 1:1 split adds a join to every query for no benefit.
1:N — One-to-Many
The most common relationship shape. The foreign key always lives on the "many" side.
CREATE TABLE customers (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
customer_id BIGINT UNSIGNED NOT NULL, -- FK lives here, the "many" side
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers (id)
);N:M — Many-to-Many
Requires a junction table (also called an associative or bridge table) because neither side can hold a single foreign key.
CREATE TABLE students (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
CREATE TABLE courses (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL
);
-- Junction table: models the relationship itself, and can carry
-- attributes that belong to the relationship, not either side
CREATE TABLE enrollments (
student_id BIGINT UNSIGNED NOT NULL,
course_id BIGINT UNSIGNED NOT NULL,
enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
grade CHAR(2),
PRIMARY KEY (student_id, course_id), -- composite PK, prevents duplicate enrollment
CONSTRAINT fk_enroll_student FOREIGN KEY (student_id) REFERENCES students (id) ON DELETE CASCADE,
CONSTRAINT fk_enroll_course FOREIGN KEY (course_id) REFERENCES courses (id) ON DELETE CASCADE
);A junction table isn't just plumbing — it's often where the interesting business data lives. enrolled_at, grade, order_items.quantity, role_assignments.granted_by — these attributes belong to the relationship, not to either side of it. This is a strong signal you're modeling an N:M relationship correctly.
4. Normal Forms
Normalization is a discipline for eliminating redundancy and the update anomalies redundancy causes. Each normal form builds on the previous one.
The un-normalized starting point
-- BAD: everything crammed into one table
CREATE TABLE orders_denormalized (
order_id INT,
customer_name VARCHAR(255),
customer_email VARCHAR(255),
product_names VARCHAR(1000), -- 'Widget, Gadget, Gizmo' — repeating group!
product_prices VARCHAR(500) -- '19.99, 45.00, 8.00' — repeating group!
);This single table has repeating groups packed into delimited strings — you cannot query, index, or aggregate individual products without string parsing.
1NF — First Normal Form
Rule: Every column holds a single atomic value; no repeating groups or arrays crammed into one column.
-- 1NF fix: one row per order-product pair, atomic columns
CREATE TABLE order_items_1nf (
order_id INT,
customer_name VARCHAR(255),
customer_email VARCHAR(255),
product_name VARCHAR(255),
product_price DECIMAL(10,2)
);Better — but customer_name/customer_email now repeat on every row for the same order, and order_id alone doesn't uniquely identify a row (the composite of order_id + product_name does).
2NF — Second Normal Form
Rule: Must be in 1NF, and every non-key column must depend on the whole composite primary key, not just part of it. (2NF only matters when you have a composite primary key.)
In order_items_1nf, if the key is (order_id, product_name), then customer_name and customer_email depend only on order_id (a partial dependency) — that's a 2NF violation.
-- 2NF fix: split off the customer/order-level data
CREATE TABLE orders_2nf (
order_id INT PRIMARY KEY,
customer_name VARCHAR(255),
customer_email VARCHAR(255)
);
CREATE TABLE order_items_2nf (
order_id INT,
product_name VARCHAR(255),
product_price DECIMAL(10,2),
PRIMARY KEY (order_id, product_name)
);3NF — Third Normal Form
Rule: Must be in 2NF, and no non-key column may depend on another non-key column (no transitive dependency).
In orders_2nf, customer_email depends on customer_name, not directly on order_id — a transitive dependency, and also a redundancy risk (the same customer's email is duplicated across every order).
-- 3NF fix: customers get their own table entirely
CREATE TABLE customers_3nf (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
customer_name VARCHAR(255),
customer_email VARCHAR(255) UNIQUE
);
CREATE TABLE orders_3nf (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers_3nf (customer_id)
);
CREATE TABLE products_3nf (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(255),
product_price DECIMAL(10,2)
);
CREATE TABLE order_items_3nf (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders_3nf (order_id),
FOREIGN KEY (product_id) REFERENCES products_3nf (product_id)
);This is the schema shape most production OLTP systems converge on: separate entity tables joined by foreign keys, each table owning exactly one concern.
BCNF — Boyce-Codd Normal Form
Rule: A stricter version of 3NF — every determinant (a column or set of columns that functionally determines another column) must be a candidate key. BCNF matters mainly when a table has multiple overlapping composite candidate keys, which is uncommon in typical CRUD schemas but shows up in scheduling/assignment domains.
-- Example where 3NF is satisfied but BCNF is not:
-- a student can have only one advisor per subject, and each advisor
-- teaches only one subject — advisor determines subject, but
-- advisor is not a candidate key of (student, subject)
CREATE TABLE advising_violates_bcnf (
student VARCHAR(100),
subject VARCHAR(100),
advisor VARCHAR(100),
PRIMARY KEY (student, subject)
-- advisor -> subject is a hidden functional dependency, not on a candidate key
);
-- BCNF fix: split so every determinant is a key
CREATE TABLE advisor_subject (
advisor VARCHAR(100) PRIMARY KEY,
subject VARCHAR(100) NOT NULL
);
CREATE TABLE student_advisor (
student VARCHAR(100),
advisor VARCHAR(100),
PRIMARY KEY (student, advisor),
FOREIGN KEY (advisor) REFERENCES advisor_subject (advisor)
);Normal forms summary
| Form | Requirement | Fixes |
|---|---|---|
| 1NF | Atomic column values, no repeating groups | Comma-separated lists, arrays-in-a-column |
| 2NF | 1NF + no partial dependency on composite key | Columns that depend on only part of a composite key |
| 3NF | 2NF + no transitive dependency between non-key columns | Redundant data derivable from another non-key column |
| BCNF | 3NF + every determinant is a candidate key | Rare overlapping-key anomalies |
In practice, most backend engineers design directly to 3NF and rarely need to reason explicitly about BCNF — if you're modeling one-entity-per-table with foreign keys and no column depends on a non-key column, you're almost always already there. Treat 3NF as the default target, not an advanced achievement.
5. When to Denormalize
Normalization optimizes for write correctness and storage efficiency. It does not optimize for read performance — a fully normalized schema on a high-read system can require joining six or seven tables for a single page render. Denormalization is a deliberate trade: you accept redundancy and the risk of inconsistency in exchange for fewer joins and faster reads.
Common denormalization patterns
-- Pattern 1: cached aggregate column, updated by the app or a trigger
ALTER TABLE orders ADD COLUMN item_count INT NOT NULL DEFAULT 0;
-- Maintained on every order_items insert/delete instead of
-- running SELECT COUNT(*) FROM order_items WHERE order_id = ? on every read
-- Pattern 2: duplicated read-heavy column to avoid a join
ALTER TABLE orders ADD COLUMN customer_email VARCHAR(255);
-- Order confirmation emails and dashboards read orders.customer_email
-- directly instead of joining to customers on every request
-- Pattern 3: precomputed summary/reporting table, refreshed on a schedule
CREATE TABLE daily_sales_summary (
sale_date DATE PRIMARY KEY,
total_orders INT NOT NULL,
total_revenue_cents BIGINT NOT NULL,
refreshed_at TIMESTAMP NOT NULL
);| Denormalization technique | Mechanism | Risk to manage |
|---|---|---|
| Cached counters/aggregates | App code or DB trigger keeps them in sync | Counter drift if an update path is missed |
| Duplicated columns | Copy a frequently-joined column onto the child table | Original updates must propagate (or accept staleness) |
| Materialized/summary tables | Precompute expensive aggregations on a schedule | Data is only as fresh as the last refresh |
| Read replicas + CQRS | Separate read model entirely, built from events | Eventual consistency between write and read models |
Denormalization should be measured, not assumed. Don't denormalize because "joins are slow" in the abstract — profile the actual query with EXPLAIN, confirm the join is the bottleneck, and confirm a normalized index-backed join genuinely can't hit your latency target first. Premature denormalization creates permanent synchronization bugs for a performance problem that a composite index would have solved.
A common middle ground: keep the normalized schema as the source of truth and denormalize only in a separate read path — a cache (Redis), a search index (Elasticsearch), or a reporting replica. This way correctness lives in one place, and the denormalized copy is explicitly disposable and rebuildable.
6. Composite Keys and Junction Table Design
-- Composite primary key prevents duplicate relationships at the DB level
CREATE TABLE role_assignments (
user_id BIGINT UNSIGNED NOT NULL,
role_id BIGINT UNSIGNED NOT NULL,
granted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
granted_by BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (user_id, role_id),
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE
);A composite primary key (user_id, role_id) does more than identify rows — it is itself a uniqueness constraint that makes "assign the same role to the same user twice" a database-level impossibility rather than something application code has to remember to check. Prefer letting the schema enforce invariants like this over relying on application logic alone.
Key takeaways
- Use surrogate keys (
AUTO_INCREMENTor time-ordered UUID/ULID) by default; reserve natural keys for genuinely immutable, unique values. - Random UUIDv4 primary keys cause B-tree page fragmentation on large InnoDB tables — prefer UUIDv7/ULID if you need global uniqueness.
- Foreign keys enforce referential integrity for free, but large sharded systems often trade them away deliberately — know which world you're in.
- 1:1 splits should be justified (rarely-read columns, optionality, or access control), not the default — an unjustified split just adds a join.
- Junction tables for N:M relationships are often where the most interesting relationship-specific business data lives.
- Design to 3NF by default. It's the shape that avoids update anomalies and matches how most ORMs and query planners expect data to look.
- Denormalize deliberately and only after profiling — never as a reflexive response to "joins feel slow."
- Composite primary keys double as uniqueness constraints — let the schema enforce invariants instead of relying on application-level checks.
Interview Questions
- What is the difference between a surrogate key and a natural key? When would you choose each?
- Why can random UUIDv4 primary keys hurt write performance on large tables, and how does UUIDv7 address it?
- Explain
ON DELETE CASCADEvsON DELETE RESTRICTvsON DELETE SET NULLwith examples. - Why might a large-scale system deliberately avoid foreign key constraints? What replaces that integrity guarantee?
- How do you model a many-to-many relationship in a relational schema? What does the junction table's primary key typically look like?
- Walk through normalizing a table from an unnormalized repeating-group structure to 3NF.
- What's the difference between a 2NF violation and a 3NF violation?
- When is BCNF stricter than 3NF? Give an example where a table satisfies 3NF but not BCNF.
- What is a transitive dependency, and why does 3NF eliminate it?
- Give three concrete examples of denormalization and the risk each one introduces.
- How would you keep a denormalized
item_countcolumn consistent with the underlyingorder_itemstable? - When would you justify splitting a table into a 1:1 relationship instead of keeping one table?
- What's a composite primary key, and how does it double as a business-rule constraint?