relational-database-design

Relational Database Design Skill

Step 1 — Gather Requirements

Ask (or infer from context) before designing anything:

• What are the core domain entities and their relationships?
• What are the primary access patterns (queries the application must answer)?
• What are the expected data volumes (rows per table, growth rate)?
• What are the SLA and consistency requirements (read latency, write throughput, ACID)?
• Are there regulatory or retention requirements (GDPR deletion, audit trails)?
• Which database engine will be used (PostgreSQL, MySQL, SQLite, …)?

Step 2 — Draft Entity List and Relationships

Enumerate entities and classify relationships:

• One-to-many: a parent row owns many child rows (orders → line_items).
• Many-to-many: resolve via a join table (users ↔ roles via user_roles).
• One-to-one: split tables only when the subset is queried independently or has different access control requirements.

Sketch an ERD in text or ASCII before writing DDL:

users (id, email, created_at)
  └── orders (id, user_id FK, status, placed_at)
        └── line_items (id, order_id FK, product_id FK, quantity, unit_price)
products (id, sku, name, price)

Step 3 — Apply Normalization

Normalize to 3NF as the default:

1. 1NF: atomic values per column, no repeating groups.
2. 2NF: every non-key column depends on the entire primary key (relevant for composite PKs).
3. 3NF: no transitive dependencies — non-key columns depend only on the primary key.

Document any intentional denormalization with a comment explaining the trade-off:

-- Denormalized: product_name copied at order time so historical orders
-- remain readable even if the product is later renamed or deleted.
line_items.product_name TEXT NOT NULL

Step 4 — Define Indexes

Map each identified access pattern to an index strategy:

Access pattern	Index type	Example
Equality lookup by FK	B-tree	CREATE INDEX ON orders(user_id)
Range query on timestamp	B-tree	CREATE INDEX ON orders(placed_at)
Partial: active records only	Partial B-tree	CREATE INDEX ON orders(user_id) WHERE status = 'active'
Case-insensitive lookup	Expression	CREATE INDEX ON users(lower(email))
Avoid heap fetch on hot query	Covering	CREATE INDEX ON orders(user_id) INCLUDE (status, placed_at)