database-design

1# Database Design
2 
3## Schema Design Principles
4 
5### Normalization Guidelines
6```sql
7-- 1NF: Atomic values, no repeating groups
8-- 2NF: No partial dependencies on composite keys
9-- 3NF: No transitive dependencies
10 
11-- Users table (normalized)
12CREATE TABLE users (
13  id SERIAL PRIMARY KEY,
14  email VARCHAR(255) UNIQUE NOT NULL,
15  created_at TIMESTAMPTZ DEFAULT NOW()
16);
17 
18-- Addresses table (separate entity)
19CREATE TABLE addresses (
20  id SERIAL PRIMARY KEY,
21  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
22  street VARCHAR(255),
23  city VARCHAR(100),
24  country VARCHAR(100),
25  is_primary BOOLEAN DEFAULT false
26);
27```
28 
29### Denormalization for Performance
30```sql
31-- When read performance matters more than write consistency
32CREATE TABLE order_summaries (
33  id SERIAL PRIMARY KEY,
34  order_id INTEGER REFERENCES orders(id),
35  customer_name VARCHAR(255),  -- Denormalized from customers
36  total_amount DECIMAL(10,2),
37  item_count INTEGER,
38  last_updated TIMESTAMPTZ DEFAULT NOW()
39);
40```
41 
42## Index Design
43 
44### Common Index Patterns
45```sql
46-- B-tree (default) for equality and range queries
47CREATE INDEX idx_users_email ON users(email);
48 
49-- Composite index (order matters!)
50CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
51 
52-- Partial index for specific conditions
53CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;
54 
55-- GIN index for array/JSONB columns
56CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
57 
58-- Covering index (includes additional columns)
59…