03-database-design

Inkdown

Start writing

Shared from "Study" on Inkdown

03 - Database Design & Normalization

The Goal of Database Design

Good database design ensures:

Data Integrity: No duplication, no inconsistencies
Efficiency: Fast queries, minimal storage
Scalability: Can grow without redesign
Maintainability: Easy to understand and modify

The Design Process

Plain text

1. Requirements Gathering
   ↓
2. Conceptual Design (ER Diagrams)
   ↓
3. Logical Design (Tables, Columns, Relations)
   ↓
4. Normalization (Apply rules to remove redundancy)
   ↓
5. Physical Design (Indexes, Partitioning)
   ↓
6. Implementation

Entity-Relationship (ER) Diagrams

Plain text

┌─────────────────────────────────────────────────────────────────┐
│                     E-COMMERCE DATABASE                          │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│   ┌──────────┐         ┌──────────┐         ┌──────────┐      │
│   │  USER    │         │  ORDER   │         │ PRODUCT  │      │
│   ├──────────┤         ├──────────┤         ├──────────┤      │
│   │ PK id    │◄────┐   │ PK id    │   ┌────►│ PK id    │      │
│   │    name  │     │   │ FK user_id│   │     │    name  │      │
│   │    email │     └───│    total  │   │     │    price │      │
│   │    phone │         │    status │   │     │    stock │      │
│   └──────────┘         └────┬─────┘   │     └──────────┘      │
│        │                    │         │           ▲            │
│        │ 1:M                │ M:N     │ M:1       │            │
│        ▼                    ▼         │           │            │
│   ┌──────────┐         ┌──────────┐   │     ┌──────────┐      │
│   │  ADDRESS │         │ORDER_ITEM│   │     │ CATEGORY │      │
│   ├──────────┤         ├──────────┤   │     ├──────────┤      │
│   │ PK id    │         │ PK id    │   │     │ PK id    │      │
│   │ FK user_id│         │ FK order_id│   │     │    name  │      │
│   │    street│         │ FK prod_id│───┘     └──────────┘      │
│   │    city  │         │    qty   │                          │
│   └──────────┘         │    price │                          │
│                        └──────────┘                          │
│                                                                  │
│  Legend: PK = Primary Key, FK = Foreign Key                    │
│          1:M = One-to-Many, M:N = Many-to-Many                 │
└─────────────────────────────────────────────────────────────────┘

Sql

-- Example: User and UserProfile
-- Users: id, email, name
-- UserProfiles: user_id (PK & FK), bio, avatar_url

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY,  -- Also the FK
    bio TEXT,
    avatar_url VARCHAR(500),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- When to use: Rarely needed. Consider if:
-- - Optional data (not all users have profiles)
-- - Security (separate sensitive data)
-- - Performance (rarely accessed data in separate table)

Sql

-- Example: User has many Orders
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Foreign key always goes on the "many" side

Sql

-- Example: Students and Courses
-- A student takes many courses
-- A course has many students

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200)
);

-- Junction/Link/Associate Table
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id),
    course_id INTEGER REFERENCES courses(id),
    enrolled_at TIMESTAMP DEFAULT NOW(),
    grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id)  -- Composite PK
);

-- Query: Get all courses for a student
SELECT c.* 
FROM courses c
JOIN enrollments e ON c.id = e.course_id
WHERE e.student_id = 1;

Plain text

┌─────────────────────────────────────┐
│           5NF (Perfect)             │
├─────────────────────────────────────┤
│   4NF (No multi-valued dependencies)│
├─────────────────────────────────────┤
│   3NF (No transitive dependencies)  │ ◄── Target for most apps
├─────────────────────────────────────┤
│   2NF (No partial dependencies)     │
├─────────────────────────────────────┤
│   1NF (Atomic values, no repeating) │
├─────────────────────────────────────┤
│         Unnormalized (Mess)         │
└─────────────────────────────────────┘

Plain text

orders table:
┌────┬───────────┬────────────────────────────────────────────┬──────────┐
│ id │ customer  │ items                                      │ total    │
├────┼───────────┼────────────────────────────────────────────┼──────────┤
│ 1  │ John, NYC │ Laptop: $1000 x 1, Mouse: $25 x 2         │ $1050    │
│ 2  │ Jane, LA  │ Keyboard: $75 x 1, Monitor: $300 x 1      │ $375     │
└────┴───────────┴────────────────────────────────────────────┴──────────┘

Problems:
- Items stored as text blob (can't query individual items)
- Multiple values in customer column
- Can't calculate "total spent on laptops"
- Can't find all NYC customers easily

Plain text

After 1NF:

orders:
┌────┬──────────┬───────┬──────────┐
│ id │ cust_name│ city  │ total    │
├────┼──────────┼───────┼──────────┤
│ 1  │ John     │ NYC   │ $1050    │
│ 2  │ Jane     │ LA    │ $375     │
└────┴──────────┴───────┴──────────┘

order_items:
┌─────────┬───────────┬───────┬─────┬─────────┐
│ order_id│ item_name │ price │ qty │ line_total│
├─────────┼───────────┼───────┼─────┼─────────┤
│ 1       │ Laptop    │ 1000  │ 1   │ 1000    │
│ 1       │ Mouse     │ 25    │ 2   │ 50      │
│ 2       │ Keyboard  │ 75    │ 1   │ 75      │
│ 2       │ Monitor   │ 300   │ 1   │ 300     │
└─────────┴───────────┴───────┴─────┴─────────┘

Now we can:
- Query "how many laptops sold" easily
- Sum line_total to verify order total
- Separate customer info if needed

Plain text

Before 2NF (with composite key):
enrollments:
┌────────────┬──────────┬──────────────┬─────────────┬──────────────┐
│ student_id │ course_id│ student_name │ course_name │ enrolled_date│
├────────────┼──────────┼──────────────┼─────────────┼──────────────┤
│ 1          │ 101      │ John         │ Math 101    │ 2024-01-15   │
│ 1          │ 102      │ John         │ Physics     │ 2024-01-16   │
│ 2          │ 101      │ Jane         │ Math 101    │ 2024-01-15   │
└────────────┴──────────┴──────────────┴─────────────┴──────────────┘
PK: (student_id, course_id)

Problems:
- student_name depends only on student_id (part of key)
- course_name depends only on course_id (part of key)
- If John changes name, update multiple rows
- Redundancy: "Math 101" stored for every enrollment

After 2NF:

students:
┌────┬──────────┐
│ id │ name     │
├────┼──────────┤
│ 1  │ John     │
│ 2  │ Jane     │
└────┴──────────┘

courses:
┌────┬───────────┐
│ id │ name      │
├────┼───────────┤
│ 101│ Math 101  │
│ 102│ Physics   │
└────┴───────────┘

enrollments:
┌────────────┬──────────┬──────────────┐
│ student_id │ course_id│ enrolled_date│
├────────────┼──────────┼──────────────┤
│ 1          │ 101      │ 2024-01-15   │
│ 1          │ 102      │ 2024-01-16   │
│ 2          │ 101      │ 2024-01-15   │
└────────────┴──────────┴──────────────┘

Plain text

Before 3NF:
orders:
┌────┬──────────┬───────────┬─────────────┬────────────┐
│ id │ cust_id  │ cust_name │ cust_city   │ total      │
├────┼──────────┼───────────┼─────────────┼────────────┤
│ 1  │ 100      │ John      │ NYC         │ 1000       │
│ 2  │ 100      │ John      │ NYC         │ 500        │
│ 3  │ 101      │ Jane      │ LA          │ 750        │
└────┴──────────┴───────────┴─────────────┴────────────┘

Problems:
- cust_name depends on cust_id, not directly on order id (transitive)
- cust_city depends on cust_id (transitive)
- John's info repeated for every order
- If John moves, need to update all his orders

After 3NF:

orders:
┌────┬──────────┬────────┐
│ id │ cust_id  │ total  │
├────┼──────────┼────────┤
│ 1  │ 100      │ 1000   │
│ 2  │ 100      │ 500    │
│ 3  │ 101      │ 750    │
└────┴──────────┴────────┘

customers:
┌────┬───────────┬───────────┐
│ id │ name      │ city      │
├────┼───────────┼───────────┤
│ 100│ John      │ NYC       │
│ 101│ Jane      │ LA        │
└────┴───────────┴───────────┘

Now:
- Update customer info in one place
- No redundancy
- Can add customer fields without touching orders

Plain text

Before 4NF:
employee_skills_languages:
┌─────────────┬───────────┬───────────┐
│ employee_id │ skill     │ language  │
├─────────────┼───────────┼───────────┤
│ 1           │ Python    │ English   │
│ 1           │ Python    │ Spanish   │
│ 1           │ Java      │ English   │
│ 1           │ Java      │ Spanish   │
└─────────────┴───────────┴───────────┘

Problem: Skills and languages are independent multi-valued attributes
Need 4 rows to express: Employee 1 knows {Python, Java} and {English, Spanish}

After 4NF:
employee_skills:
┌─────────────┬───────────┐
│ employee_id │ skill     │
├─────────────┼───────────┤
│ 1           │ Python    │
│ 1           │ Java      │
└─────────────┴───────────┘

employee_languages:
┌─────────────┬───────────┐
│ employee_id │ language  │
├─────────────┼───────────┤
│ 1           │ English   │
│ 1           │ Spanish   │
└─────────────┴───────────┘

Sql

-- 1. Counter Cache
-- Instead of counting every time
SELECT COUNT(*) FROM orders WHERE user_id = 1;

-- Add counter to users table
ALTER TABLE users ADD COLUMN orders_count INTEGER DEFAULT 0;

-- Update via trigger or application
UPDATE users SET orders_count = orders_count + 1 WHERE id = 1;

-- 2. Storing Aggregates
CREATE TABLE daily_stats (
    date DATE PRIMARY KEY,
    total_orders INTEGER,
    total_revenue DECIMAL(12,2),
    unique_customers INTEGER
);

-- 3. Materialized Path (for trees)
-- Instead of recursive queries
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    path LTREE  -- PostgreSQL ltree extension: '1.5.12.3'
);

-- Query all subcategories of 'Electronics' easily
SELECT * FROM categories WHERE path <@ '1.5';

-- 4. JSON for flexible attributes
-- When attributes vary by product type
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    base_price DECIMAL(10,2),
    attributes JSONB  -- { "color": "red", "size": "XL", "material": "cotton" }
);

Sql

-- Instead of DELETE, mark as deleted
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;

-- Query only active
SELECT * FROM users WHERE deleted_at IS NULL;

-- Can restore if needed
UPDATE users SET deleted_at = NULL WHERE id = 1;

Sql

-- Track who changed what and when
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW(),
    created_by INTEGER,
    updated_at TIMESTAMP DEFAULT NOW(),
    updated_by INTEGER
);

-- Or separate audit table
CREATE TABLE users_audit (
    id INTEGER,
    name VARCHAR(100),
    operation VARCHAR(10),  -- INSERT, UPDATE, DELETE
    changed_at TIMESTAMP,
    changed_by INTEGER
);

Sql

-- Same database, different schemas
CREATE SCHEMA tenant_abc;
CREATE SCHEMA tenant_xyz;

-- Tables: tenant_abc.users, tenant_xyz.users
-- Set search_path per connection
SET search_path = tenant_abc;

Sql

CREATE TABLE users (
    tenant_id INTEGER NOT NULL,
    id SERIAL,
    name VARCHAR(100),
    PRIMARY KEY (tenant_id, id)
);

-- Every query must include tenant_id
SELECT * FROM users WHERE tenant_id = 123 AND id = 1;

-- Use Row Level Security
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.tenant_id')::INTEGER);

Sql

-- Store in UTC, display in local time
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    start_time TIMESTAMPTZ,  -- Stored as UTC
    timezone VARCHAR(50)     -- For reference
);

-- Convert on retrieval
SELECT 
    name,
    start_time AT TIME ZONE 'America/New_York' as local_start
FROM events;

Sql

-- Store large content separately or use TEXT
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    summary VARCHAR(500),    -- For list views
    content TEXT,            -- Full content
    content_storage VARCHAR(20) DEFAULT 'inline',
    -- For really large content, store in S3 and keep reference
    s3_key VARCHAR(200)
);

03-database-design

03 - Database Design & Normalization

The Goal of Database Design

The Design Process

Entity-Relationship (ER) Diagrams

03-database-design

03 - Database Design & Normalization

The Goal of Database Design

The Design Process

Entity-Relationship (ER) Diagrams

Relationships Explained

1. One-to-One (1:1)

2. One-to-Many (1:M)

3. Many-to-Many (M:N)

Normalization

Normal Forms Hierarchy

0NF - Unnormalized (The Mess)

1NF - First Normal Form

2NF - Second Normal Form

3NF - Third Normal Form

Higher Normal Forms (Briefly)

Denormalization: When to Break the Rules

When to Denormalize:

Common Denormalization Patterns:

Design Patterns

1. Soft Deletes

2. Audit Logging

3. Multi-tenancy Patterns

4. Handling Time Zones

5. Handling Large Text

Design Checklist

Next: Indexing Deep Dive