Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
The definitive guide to database schema design. Master 1NF, 2NF, 3NF, BCNF normalization, surrogate vs natural keys, UUIDv7, and denormalization.
Quick answer
Database normalization is the formal mathematical process of organizing relational tables to eliminate data redundancy and prevent insertion, update, and deletion anomalies. Most modern production applications normalize schemas to Third Normal Form (3NF) or Boyce-Codd Normal Form (BCNF), applying selective denormalization only when verified read performance bottlenecks require it.
What are the core requirements of 1NF, 2NF, and 3NF?
1NF requires atomic column values and no repeating groups. 2NF requires 1NF and that every non-key column depends on the WHOLE primary key (no partial dependencies on composite keys). 3NF requires 2NF and that no non-key column depends on another non-key column (no transitive dependencies).
Why is UUIDv7 superior to UUIDv4 for database primary keys?
UUIDv4 is purely random, causing massive B-Tree index fragmentation, page splits, and cache evictions on inserts. UUIDv7 embeds a Unix millisecond timestamp prefix, making generated UUIDs naturally sequential (time-ordered), preserving B-Tree append locality while retaining global uniqueness.
When should you denormalize a normalized database?
Denormalize only when verified query metrics show that joining multiple tables causes unacceptable latency in read-heavy workloads (e.g. caching total counts or denormalizing customer names into reporting tables). Always maintain single-source-of-truth invariants.
What is the Entity-Attribute-Value (EAV) anti-pattern?
EAV stores dynamic attributes across generic `(entity_id, attribute_name, attribute_value)` rows. It destroys relational integrity, prevents foreign keys, makes queries require dozens of self-joins, and ruins indexing. Use native JSONB columns instead.
Designing a scalable relational database schema requires balancing mathematical normalization rigor with pragmatic hardware performance. Getting schema foundations right up front avoids painful zero-downtime migrations later.
Key takeaways
Target Third Normal Form (3NF) as the baseline for online transaction processing (OLTP) schemas.
Eliminate transitive dependencies: every attribute must depend on the key, the whole key, and nothing but the key.
Adopt time-ordered sequential UUIDv7 or BIGINT IDENTITY rather than random UUIDv4 for primary keys.
Avoid the EAV anti-pattern by utilizing modern JSONB columns with GIN indexes for dynamic fields.
The progression of Normal Forms explained
First Normal Form (1NF): Atomicity
Each column must store a single, indivisible scalar value. Comma-separated strings (e.g. `tags: 'sql,postgres,db'`) violate 1NF and must be modeled as a child table.
Second Normal Form (2NF): No Partial Dependencies
In tables with composite primary keys (e.g., `order_id, product_id`), every non-key column must depend on both keys. If `product_name` depends only on `product_id`, it belongs in a dedicated products table.
Third Normal Form (3NF): No Transitive Dependencies
Non-key columns cannot depend on other non-key columns. If a table stores `customer_id` and `customer_city`, the city depends on the customer, not the order ID. Customer information must reside in the customer entity.
Denormalized table transformed into 3NF schema
-- BEFORE: Denormalized table riddled with update anomalies-- If customer moves or product price changes, historical rows become inconsistentCREATE TABLE raw_orders_unnormalized ( order_id BIGINT, customer_name VARCHAR(100), customer_email VARCHAR(255), product_name VARCHAR(100), product_price DECIMAL(10, 2), quantity INT);-- AFTER: Clean 3NF relational designCREATE TABLE core.customers ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, full_name VARCHAR(100) NOT NULL);CREATE TABLE catalog.products ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, sku VARCHAR(64) NOT NULL UNIQUE, unit_price DECIMAL(10, 2) NOT NULL);CREATE TABLE core.orders ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id BIGINT NOT NULL REFERENCES core.customers (id), order_date TIMESTAMPTZ NOT NULL DEFAULT NOW());CREATE TABLE core.order_items ( order_id BIGINT NOT NULL REFERENCES core.orders (id), product_id BIGINT NOT NULL REFERENCES catalog.products (id), quantity INT NOT NULL CHECK (quantity > 0), unit_price DECIMAL(10, 2) NOT NULL, -- Preserves price at time of purchase PRIMARY KEY (order_id, product_id));
Primary key design: BIGINT vs UUIDv4 vs UUIDv7
Choosing primary key formats impacts B-Tree index performance:
BIGINT Identity: 8 bytes, compact, sequentially ordered, best write performance, but exposes record counts to external users.
UUIDv4: 16 bytes, unguessable, distributed generation, but causes severe B-Tree leaf-node fragmentation and random disk I/O.