Designing Database Schema
1. Normalizing Database Tables
| Form | Rule |
|---|
| 1NF | Atomic columns, no repeating groups |
| 2NF | 1NF + no partial key dep |
| 3NF | 2NF + no transitive dep on PK |
| BCNF | Stronger 3NF; every determinant is a key |
| Technique | Use |
|---|
| Redundant column | Avoid join for hot read |
| Aggregate column | Pre-computed totals |
| Materialized view | Refreshable derived data |
| Read model (CQRS) | Separate denormalized read store |
Warning: Denormalize only with measurement; pay maintenance cost.
3. Designing Primary Keys
| Type | Pros | Cons |
|---|
| Auto-increment BIGINT | Compact, fast index | Sequential leak; not distributed-friendly |
| UUID v4 | No coordination | Random; index fragmentation |
| UUID v7 / ULID | Time-sortable, distributed | 16 bytes |
| Composite | Natural keys | Awkward FK references |
4. Implementing Foreign Key Relationships
| Action | Behavior |
|---|
ON DELETE CASCADE | Delete children |
ON DELETE SET NULL | Null FK |
ON DELETE RESTRICT | Block delete |
ON UPDATE CASCADE | Propagate PK change (rare) |
5. Designing Indexes
| Index | When |
|---|
| B-tree | Equality, range, ORDER BY |
| Hash | Equality only (specific engines) |
| Composite | Multi-column WHERE (left-most rule) |
| Covering | Includes all SELECT columns |
| Partial | Subset of rows (WHERE clause in index) |
| GIN/GIST | Full-text, JSON, geo (PostgreSQL) |
6. Implementing Soft Deletes
Example: Soft-delete column
ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ NULL;
CREATE INDEX idx_orders_active ON orders(customer_id) WHERE deleted_at IS NULL;
7. Implementing Audit Columns
| Column | Type |
|---|
created_at | TIMESTAMPTZ DEFAULT now() |
created_by | UUID / VARCHAR |
updated_at | TIMESTAMPTZ; trigger or app |
updated_by | UUID / VARCHAR |
version | BIGINT for optimistic lock |
8. Designing Partitioning Strategies
| Strategy | Use Case |
|---|
| Range | Time-series (monthly partitions) |
| List | Tenant ID, region |
| Hash | Even distribution by key |
| Composite | Range + hash combinations |
9. Using Junction Tables
Example: M:N junction
CREATE TABLE user_roles (
user_id UUID NOT NULL REFERENCES users(id),
role_id UUID NOT NULL REFERENCES roles(id),
granted_at TIMESTAMPTZ DEFAULT now(),
PRIMARY KEY (user_id, role_id)
);
CREATE INDEX idx_user_roles_role ON user_roles(role_id);
10. Implementing Polymorphic Associations
| Pattern | Trade-off |
|---|
| Type column + ID | Simple; no FK enforcement |
| Multiple FK columns | FK enforced; sparse |
| Single-table inheritance | One wide table; sparse columns |
| Joined inheritance | Multiple tables + join |
11. Designing Column Types
| Data | Type |
|---|
| Money | NUMERIC(19,4) or BIGINT cents |
| Boolean | BOOLEAN (avoid CHAR(1)) |
| Timestamp | TIMESTAMPTZ (always with TZ) |
| Enum | VARCHAR + CHECK or DB ENUM |
| Text | TEXT (no length penalty in PG) |
| Binary | BYTEA / BLOB or external object store |
| JSON | JSONB (PostgreSQL) |
12. Implementing Database Constraints
| Constraint | Purpose |
|---|
| NOT NULL | Required field |
| UNIQUE | No duplicates |
| CHECK | Business invariant (amount >= 0) |
| FOREIGN KEY | Referential integrity |
| EXCLUDE | Range non-overlap (PG) |