Good database design prevents countless headaches. Here are the fundamentals that matter.
Normalization Basics
No repeating groups:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
-- Bad
CREATE TABLE orders (
id INT,
items TEXT -- "item1,item2,item3"
);
-- Good
CREATE TABLE orders (id INT PRIMARY KEY);
CREATE TABLE order_items (
order_id INT REFERENCES orders(id),
item_id INT,
quantity INT
);
|
No partial dependencies:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
-- Bad: product_name depends only on product_id
CREATE TABLE order_items (
order_id INT,
product_id INT,
product_name TEXT, -- Partial dependency
quantity INT
);
-- Good
CREATE TABLE products (
id INT PRIMARY KEY,
name TEXT
);
CREATE TABLE order_items (
order_id INT,
product_id INT REFERENCES products(id),
quantity INT
);
|
No transitive dependencies:
1
2
3
4
5
6
7
8
9
10
11
12
|
-- Bad: city depends on zip_code, not directly on user
CREATE TABLE users (
id INT,
zip_code TEXT,
city TEXT -- Transitive dependency
);
-- Good
CREATE TABLE zip_codes (
code TEXT PRIMARY KEY,
city TEXT
);
|
When to Denormalize
Normalization isn’t always best:
- Read-heavy workloads: Duplicate for faster reads
- Reporting: Pre-aggregate for dashboards
- Caching: Store computed values
1
2
3
4
5
6
7
|
-- Denormalized for performance
CREATE TABLE order_summary (
order_id INT PRIMARY KEY,
total_items INT,
total_amount DECIMAL,
customer_name TEXT -- Denormalized
);
|
Indexing Strategy
1
2
3
4
5
6
7
8
9
10
11
12
|
-- Primary key (automatic index)
CREATE TABLE users (
id SERIAL PRIMARY KEY
);
-- Foreign key (often needs index)
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Query patterns
CREATE INDEX idx_users_email ON users(email); -- Lookups
CREATE INDEX idx_orders_created ON orders(created_at DESC); -- Sorting
CREATE INDEX idx_products_category_price ON products(category, price); -- Compound
|
Don’t over-index: Each index slows writes.
Data Types Matter
1
2
3
4
5
6
7
8
|
-- Use appropriate types
CREATE TABLE events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
created_at TIMESTAMPTZ DEFAULT NOW(),
amount DECIMAL(10,2), -- Not FLOAT for money
status TEXT CHECK (status IN ('pending', 'complete')),
metadata JSONB
);
|
Constraints
1
2
3
4
5
6
7
|
CREATE TABLE products (
id SERIAL PRIMARY KEY,
sku TEXT UNIQUE NOT NULL,
price DECIMAL(10,2) CHECK (price > 0),
category_id INT REFERENCES categories(id),
created_at TIMESTAMPTZ DEFAULT NOW()
);
|
Constraints enforce data integrity at the database level.
Handling Relationships
One-to-Many
1
2
3
4
5
|
-- User has many orders
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id)
);
|
Many-to-Many
1
2
3
4
5
6
|
-- Products in multiple categories
CREATE TABLE product_categories (
product_id INT REFERENCES products(id),
category_id INT REFERENCES categories(id),
PRIMARY KEY (product_id, category_id)
);
|
One-to-One
1
2
3
4
5
6
|
-- User profile (separate for performance)
CREATE TABLE user_profiles (
user_id INT PRIMARY KEY REFERENCES users(id),
bio TEXT,
avatar_url TEXT
);
|
Schema Evolution
Plan for changes:
1
2
3
4
5
6
|
-- Add columns with defaults
ALTER TABLE users ADD COLUMN verified BOOLEAN DEFAULT FALSE;
-- Use migrations
-- migrations/001_create_users.sql
-- migrations/002_add_verified_column.sql
|
Quick Tips
- Always use primary keys
- Name things consistently (snake_case or camelCase, not both)
- Use foreign keys for integrity
- Don’t store calculated values (usually)
- Plan for soft deletes if needed (
deleted_at column)
Good schema design is foundational. Invest time upfront to avoid pain later.
Comments