Oreoluwa
Database Design Patterns and Best Practices
February 8, 2024
14 min read

Database Design Patterns and Best Practices

Database
SQL
Architecture
Performance

Effective database design is the foundation of any scalable application. Poor database design can lead to performance issues, data inconsistencies, and maintenance nightmares. Let's explore proven patterns and best practices for designing robust databases.

Database Normalization

Understanding normalization forms helps create efficient, maintainable database structures:

First Normal Form (1NF)

Eliminate repeating groups and ensure atomic values:

-- BAD: Violates 1NF (repeating groups)
CREATE TABLE employees_bad (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    phone1 VARCHAR(15),
    phone2 VARCHAR(15),
    phone3 VARCHAR(15)
);

-- GOOD: Follows 1NF
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employee_phones (
    id INT PRIMARY KEY,
    employee_id INT,
    phone_number VARCHAR(15),
    phone_type ENUM('work', 'home', 'mobile'),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

Second Normal Form (2NF)

Eliminate partial dependencies on composite primary keys:

-- BAD: Violates 2NF (partial dependency)
CREATE TABLE order_items_bad (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- Depends only on product_id
    product_price DECIMAL(10,2), -- Depends only on product_id
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- GOOD: Follows 2NF
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Third Normal Form (3NF)

Eliminate transitive dependencies:

-- BAD: Violates 3NF (transitive dependency)
CREATE TABLE employees_bad (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    department_name VARCHAR(100), -- Depends on department_id, not employee id
    department_budget DECIMAL(12,2) -- Depends on department_id, not employee id
);

-- GOOD: Follows 3NF
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    budget DECIMAL(12,2)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

Essential Design Patterns

Surrogate Keys Pattern

Use artificial primary keys for better performance and flexibility:

-- Instead of using natural keys
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY, -- Surrogate key
    email VARCHAR(255) UNIQUE NOT NULL,         -- Natural key as unique constraint
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
);

Audit Trail Pattern

Track changes to critical data:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE user_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    operation ENUM('INSERT', 'UPDATE', 'DELETE'),
    old_values JSON,
    new_values JSON,
    changed_by INT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_operation (user_id, operation),
    INDEX idx_changed_at (changed_at)
);

-- Trigger to automatically populate audit table
DELIMITER //
CREATE TRIGGER user_audit_trigger
    AFTER UPDATE ON users
    FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, operation, old_values, new_values, changed_by)
    VALUES (
        NEW.id,
        'UPDATE',
        JSON_OBJECT('username', OLD.username, 'email', OLD.email),
        JSON_OBJECT('username', NEW.username, 'email', NEW.email),
        @current_user_id
    );
END//
DELIMITER ;

Soft Delete Pattern

Mark records as deleted instead of physically removing them:

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    author_id INT,
    deleted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_deleted_at (deleted_at),
    INDEX idx_author_active (author_id, deleted_at)
);

-- View for active posts only
CREATE VIEW active_posts AS
SELECT * FROM posts WHERE deleted_at IS NULL;

-- Soft delete procedure
DELIMITER //
CREATE PROCEDURE soft_delete_post(IN post_id INT)
BEGIN
    UPDATE posts 
    SET deleted_at = CURRENT_TIMESTAMP 
    WHERE id = post_id AND deleted_at IS NULL;
END//
DELIMITER ;

Polymorphic Association Pattern

Handle relationships to multiple table types:

-- Comments can belong to posts, photos, or videos
CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT,
    commentable_type VARCHAR(50),  -- 'post', 'photo', 'video'
    commentable_id INT,            -- ID of the related record
    user_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_commentable (commentable_type, commentable_id),
    INDEX idx_user (user_id)
);

-- Better approach: Use junction tables for type safety
CREATE TABLE post_comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT,
    comment_id INT,
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (comment_id) REFERENCES comments(id)
);

CREATE TABLE photo_comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    photo_id INT,
    comment_id INT,
    FOREIGN KEY (photo_id) REFERENCES photos(id),
    FOREIGN KEY (comment_id) REFERENCES comments(id)
);

Advanced Indexing Strategies

Composite Indexes

Design indexes for specific query patterns:

-- For queries filtering by status and created_at
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

-- For queries ordering by created_at within a status
CREATE INDEX idx_orders_status_created_desc ON orders (status, created_at DESC);

-- Covering index for specific queries
CREATE INDEX idx_orders_covering ON orders (customer_id, status, total_amount, created_at);

Partial Indexes

Index only relevant subset of data:

-- Index only active users
CREATE INDEX idx_active_users ON users (email) WHERE deleted_at IS NULL;

-- Index only recent orders
CREATE INDEX idx_recent_orders ON orders (customer_id, created_at) 
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

Function-based Indexes

Index computed values:

-- Index for case-insensitive searches
CREATE INDEX idx_users_email_lower ON users ((LOWER(email)));

-- Index for full-text search
ALTER TABLE posts ADD FULLTEXT(title, content);

-- Index for JSON data
CREATE INDEX idx_metadata_tags ON products ((JSON_EXTRACT(metadata, '$.tags')));

Performance Optimization Patterns

Denormalization for Read Performance

Strategic denormalization for query optimization:

-- Denormalized table for reporting
CREATE TABLE order_summary (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNIQUE,
    customer_name VARCHAR(100),
    customer_email VARCHAR(255),
    order_total DECIMAL(10,2),
    item_count INT,
    order_date DATE,
    status VARCHAR(50),
    
    INDEX idx_customer_email (customer_email),
    INDEX idx_order_date_status (order_date, status),
    INDEX idx_order_total (order_total)
);

-- Update summary when orders change
DELIMITER //
CREATE TRIGGER update_order_summary
    AFTER UPDATE ON orders
    FOR EACH ROW
BEGIN
    IF NEW.status != OLD.status OR NEW.total_amount != OLD.total_amount THEN
        UPDATE order_summary 
        SET 
            order_total = NEW.total_amount,
            status = NEW.status
        WHERE order_id = NEW.id;
    END IF;
END//
DELIMITER ;

Partitioning Pattern

Distribute large tables across multiple storage units:

-- Partition by date range
CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATE,
    amount DECIMAL(10,2),
    customer_id INT,
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Partition by hash for even distribution
CREATE TABLE user_sessions (
    id INT AUTO_INCREMENT,
    user_id INT,
    session_data TEXT,
    created_at TIMESTAMP,
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH(user_id) PARTITIONS 8;

Materialized Views Pattern

Pre-compute expensive aggregations:

-- Create materialized view table
CREATE TABLE monthly_sales_summary (
    year_month VARCHAR(7),
    total_sales DECIMAL(15,2),
    order_count INT,
    avg_order_value DECIMAL(10,2),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (year_month)
);

-- Procedure to refresh materialized view
DELIMITER //
CREATE PROCEDURE refresh_monthly_sales_summary()
BEGIN
    DELETE FROM monthly_sales_summary;
    
    INSERT INTO monthly_sales_summary (year_month, total_sales, order_count, avg_order_value)
    SELECT 
        DATE_FORMAT(created_at, '%Y-%m') as year_month,
        SUM(total_amount) as total_sales,
        COUNT(*) as order_count,
        AVG(total_amount) as avg_order_value
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_FORMAT(created_at, '%Y-%m');
END//
DELIMITER ;

-- Schedule to run daily
CREATE EVENT refresh_sales_summary
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 01:00:00'
DO CALL refresh_monthly_sales_summary();

Data Integrity and Constraints

Check Constraints

Enforce business rules at the database level:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    discount_percentage DECIMAL(5,2) DEFAULT 0,
    stock_quantity INT NOT NULL DEFAULT 0,
    
    -- Business rule constraints
    CONSTRAINT chk_price_positive CHECK (price > 0),
    CONSTRAINT chk_discount_range CHECK (discount_percentage >= 0 AND discount_percentage <= 100),
    CONSTRAINT chk_stock_non_negative CHECK (stock_quantity >= 0)
);

Complex Foreign Key Relationships

Handle hierarchical and self-referential data:

-- Self-referential for organizational hierarchy
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_department_id INT,
    manager_id INT,
    
    FOREIGN KEY (parent_department_id) REFERENCES departments(id),
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- Many-to-many with additional attributes
CREATE TABLE project_assignments (
    project_id INT,
    employee_id INT,
    role VARCHAR(50),
    start_date DATE,
    end_date DATE,
    billable_rate DECIMAL(8,2),
    
    PRIMARY KEY (project_id, employee_id),
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    
    CHECK (end_date IS NULL OR end_date >= start_date)
);

Schema Migration Best Practices

Version Control for Database Changes

-- Migration tracking table
CREATE TABLE schema_migrations (
    version VARCHAR(50) PRIMARY KEY,
    description TEXT,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Example migration: Add email verification
-- Migration: 20240208_001_add_email_verification.sql
ALTER TABLE users 
ADD COLUMN email_verified BOOLEAN DEFAULT FALSE,
ADD COLUMN email_verification_token VARCHAR(255),
ADD COLUMN email_verification_sent_at TIMESTAMP NULL;

CREATE INDEX idx_users_verification_token ON users (email_verification_token);

INSERT INTO schema_migrations (version, description) 
VALUES ('20240208_001', 'Add email verification fields to users table');

Zero-downtime Schema Changes

-- Step 1: Add new column with default value
ALTER TABLE users ADD COLUMN new_email VARCHAR(255);

-- Step 2: Backfill data (in batches)
UPDATE users SET new_email = email WHERE new_email IS NULL LIMIT 1000;

-- Step 3: Add constraints after data is populated
ALTER TABLE users MODIFY COLUMN new_email VARCHAR(255) NOT NULL;
ALTER TABLE users ADD UNIQUE KEY unique_new_email (new_email);

-- Step 4: Update application to use new column

-- Step 5: Drop old column (after application is fully migrated)
ALTER TABLE users DROP COLUMN email;
ALTER TABLE users CHANGE COLUMN new_email email VARCHAR(255) NOT NULL;

NoSQL Design Patterns

Document Store Best Practices (MongoDB example)

// User profile with embedded documents
{
  _id: ObjectId("..."),
  username: "john_doe",
  email: "john@example.com",
  profile: {
    firstName: "John",
    lastName: "Doe",
    bio: "Software developer passionate about databases",
    avatar: "https://example.com/avatars/john.jpg"
  },
  preferences: {
    theme: "dark",
    notifications: {
      email: true,
      push: false
    }
  },
  addresses: [
    {
      type: "home",
      street: "123 Main St",
      city: "Anytown",
      zipCode: "12345",
      country: "USA",
      primary: true
    }
  ],
  createdAt: ISODate("2024-01-01T00:00:00Z"),
  updatedAt: ISODate("2024-02-08T00:00:00Z")
}

// Separate collection for frequently updated data
{
  _id: ObjectId("..."),
  userId: ObjectId("..."),
  loginCount: 150,
  lastLoginAt: ISODate("2024-02-08T10:30:00Z"),
  sessionCount: 3,
  totalTimeSpent: 7200000 // milliseconds
}

Performance Monitoring and Analysis

Query Analysis Techniques

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- Analyze query execution
EXPLAIN FORMAT=JSON
SELECT c.name, COUNT(o.id) as order_count, SUM(o.total_amount) as total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.created_at >= '2024-01-01'
GROUP BY c.id, c.name
HAVING total_spent > 1000
ORDER BY total_spent DESC;

-- Index usage analysis
SHOW INDEX FROM orders;
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    NULLABLE
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = 'your_database';

Best Practices Summary

  1. Normalize appropriately - Don't over-normalize, but ensure data integrity
  2. Use appropriate data types - Choose the smallest data type that fits your needs
  3. Index strategically - Index for your query patterns, not just every column
  4. Implement proper constraints - Use foreign keys, check constraints, and unique constraints
  5. Plan for growth - Consider partitioning and sharding strategies early
  6. Monitor performance - Regular analysis of slow queries and index usage
  7. Version control schema changes - Treat database changes like code changes
  8. Test migrations - Always test schema changes in staging environments
  9. Document your design - Maintain documentation of business rules and relationships
  10. Regular maintenance - Schedule maintenance tasks like index rebuilding and statistics updates

Conclusion

Effective database design requires balancing normalization with performance, understanding your query patterns, and planning for future growth. The patterns and practices outlined here provide a solid foundation for building scalable, maintainable database systems.

Remember that database design is often about trade-offs. The key is understanding the implications of your choices and making informed decisions based on your specific requirements and constraints.