🏗️ Advanced Table Design — Schema Architecture & Normalization

Learn professional-grade table design patterns for scalable databases.

🎯 Database Normalization

Normalization reduces data redundancy and improves data integrity through structured design.

❌ Bad Design (Denormalized)

# All data in one table = redundancy & update anomalies
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_name TEXT,
    customer_email TEXT,
    customer_phone TEXT,
    product_name TEXT,
    category TEXT,
    price REAL,
    quantity INTEGER
)

# Problem: If customer moves, must update multiple rows!
# Problem: If product price changes, must update multiple rows!

✅ Good Design (Normalized)

# Separate tables with relationships
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    phone TEXT
)

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    price REAL NOT NULL
)

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
)

# Update customer info once = fixes all orders!
# Update product price once = affects all orders!

🔗 Foreign Keys & Relationships

Foreign keys maintain referential integrity between tables.

import sqlite3

conn = sqlite3.connect("app.db")
cursor = conn.cursor()

# Enable foreign key support (must be done each session!)
cursor.execute("PRAGMA foreign_keys = ON")

# Create parent table
cursor.execute("""
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
    )
""")

# Create child table with foreign key
cursor.execute("""
    CREATE TABLE posts (
        id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        user_id INTEGER NOT NULL,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )
""")

conn.commit()

Key options:

• `ON DELETE CASCADE` — Delete posts when user is deleted

• `ON DELETE SET NULL` — Set user_id to NULL when user deleted

• `ON UPDATE CASCADE` — Update post when user ID changes

🎨 Advanced Column Definitions

# Generated columns (computed values)
cursor.execute("""
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        first_name TEXT NOT NULL,
        last_name TEXT NOT NULL,
        full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
    )
""")

# JSON columns (SQLite 3.38+)
cursor.execute("""
    CREATE TABLE products (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        metadata TEXT CHECK(json_valid(metadata))
    )
""")

# Date/time columns with defaults
cursor.execute("""
    CREATE TABLE events (
        id INTEGER PRIMARY KEY,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT DEFAULT CURRENT_TIMESTAMP
    )
""")

📊 Advanced Constraints

# CHECK constraints for business logic
cursor.execute("""
    CREATE TABLE employees (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        salary REAL CHECK(salary > 0),
        end_date TEXT CHECK(end_date > start_date),
        start_date TEXT
    )
""")

# Composite UNIQUE constraints
cursor.execute("""
    CREATE TABLE course_enrollments (
        id INTEGER PRIMARY KEY,
        student_id INTEGER NOT NULL,
        course_id INTEGER NOT NULL,
        semester TEXT NOT NULL,
        UNIQUE(student_id, course_id, semester)
    )
""")

# Multiple CHECK constraints
cursor.execute("""
    CREATE TABLE products (
        id INTEGER PRIMARY KEY,
        price REAL CHECK(price > 0),
        discount REAL CHECK(discount >= 0 AND discount <= 100),
        final_price REAL CHECK(final_price = price * (1 - discount/100))
    )
""")

🔄 Schema Evolution & Migrations

# Add column with default (backward compatible)
cursor.execute("""
    ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'
""")

# Add column with NOT NULL (requires default for existing rows)
cursor.execute("""
    ALTER TABLE users ADD COLUMN created_at TEXT DEFAULT CURRENT_TIMESTAMP
""")

# Drop column (requires table rebuild in SQLite)
cursor.execute("PRAGMA foreign_keys = OFF")
cursor.execute("BEGIN")
cursor.execute("""
    CREATE TABLE users_new AS
    SELECT id, name, email FROM users
""")
cursor.execute("DROP TABLE users")
cursor.execute("ALTER TABLE users_new RENAME TO users")
cursor.execute("COMMIT")
cursor.execute("PRAGMA foreign_keys = ON")

⚙️ Performance Optimization in Schema

# Add indexes on foreign keys (automatic on PRIMARY KEY)
cursor.execute("""
    CREATE TABLE orders (
        id INTEGER PRIMARY KEY,
        user_id INTEGER,
        product_id INTEGER,
        FOREIGN KEY (user_id) REFERENCES users(id),
        FOREIGN KEY (product_id) REFERENCES products(id)
    )
""")

# Add separate indexes for join optimization
cursor.execute("CREATE INDEX idx_orders_user ON orders(user_id)")
cursor.execute("CREATE INDEX idx_orders_product ON orders(product_id)")

# Covering indexes (include all needed columns)
cursor.execute("""
    CREATE INDEX idx_users_email_active
    ON users(email) WHERE status = 'active'
""")

# Partial indexes (only index relevant rows)
cursor.execute("""
    CREATE INDEX idx_deleted_users
    ON users(id) WHERE is_deleted = 1
""")

🔐 Data Integrity Patterns

# Prevent duplicate entries with UNIQUE on multiple columns
cursor.execute("""
    CREATE TABLE user_settings (
        id INTEGER PRIMARY KEY,
        user_id INTEGER NOT NULL,
        setting_name TEXT NOT NULL,
        setting_value TEXT,
        UNIQUE(user_id, setting_name)
    )
""")

# Self-referencing foreign key (hierarchies)
cursor.execute("""
    CREATE TABLE employees (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        manager_id INTEGER,
        FOREIGN KEY (manager_id) REFERENCES employees(id)
    )
""")

# Soft deletes (mark as deleted, don't remove)
cursor.execute("""
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        is_deleted INTEGER DEFAULT 0,
        deleted_at TEXT
    )
""")

📋 Schema Introspection

# Get all tables
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = cursor.fetchall()

# Get table structure
cursor.execute("PRAGMA table_info(users)")
columns = cursor.fetchall()
for col in columns:
    print(f"{col[1]} ({col[2]})")  # name, type

# Get indexes on a table
cursor.execute("PRAGMA index_list(users)")
indexes = cursor.fetchall()

# Get foreign keys
cursor.execute("PRAGMA foreign_key_list(orders)")
foreign_keys = cursor.fetchall()

🔑 Key Takeaways

• ✅ Normalize schemas to reduce redundancy

• ✅ Use foreign keys with CASCADE for data consistency

• ✅ Create indexes on foreign keys and frequently filtered columns

• ✅ Use CHECK constraints for business logic

• ✅ UNIQUE constraints on natural keys

• ✅ Partial/covering indexes for query optimization

• ✅ Enable `PRAGMA foreign_keys = ON` for integrity

• ✅ Plan schema evolution with ALTER TABLE

Ready to practice? Challenges | Quiz