Database Design
Overview
Provides comprehensive guidance for designing robust, scalable, and maintainable database schemas for both relational (SQL) and NoSQL databases, from conceptual modeling to physical implementation.
Design Workflow
- Requirements Analysis - Gather data requirements and usage patterns
- Conceptual Modeling - Create entity-relationship diagrams (ERD)
- Logical Modeling - Define normalized schema with relationships
- Physical Modeling - Select data types, indexes, and constraints
- Validation - Review design against requirements and best practices
Quick Start
Basic E-commerce Schema:
-- Users table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Products table
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock_quantity INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_name (name)
);
-- Orders table
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
total_amount DECIMAL(10,2) NOT NULL,
status VARCHAR(50) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_user_created (user_id, created_at)
);Core Principles
- Start normalized, denormalize only when proven necessary
- Index strategically based on actual query patterns
- Use constraints to enforce data integrity at database level
- Choose appropriate data types to optimize storage and performance
- Plan for growth with partitioning and sharding strategies
- Document design decisions and their rationale
- Test with realistic data volumes
When to Load References
- Design Workflow: See database-design-workflow.md for step-by-step design process including requirements analysis, conceptual/logical/physical modeling, normalization steps, and relationship patterns
- Advanced Patterns: See advanced-design-patterns.md for many-to-many relationships, inheritance/polymorphism, temporal data, soft deletes, audit trails, and hierarchical data
- NoSQL Design: See nosql-database-design.md when designing MongoDB documents, Cassandra column families, or Redis data structures
- Anti-Patterns: See common-anti-patterns-to-avoid.md to identify EAV pattern issues, generic tables, redundant data, multi-value columns, and other problematic designs
- Performance: See performance-optimization.md for query optimization, partitioning strategies, caching patterns, and index tuning
- Migration: See schema-migration-best-practices.md for zero-downtime migrations, backward compatibility, and rollback strategies
- Checklist: See database-design-checklist.md for comprehensive validation before implementation