Database Schema Design

# Database Schema Design

Design a comprehensive, normalized database schema based on the provided requirements, including tables, relationships, indexes, constraints, and migration strategies.

## Design Process

### 1. Requirements Analysis
- Identify all entities and their attributes
- Determine relationships between entities
- Identify business rules and constraints
- Consider query patterns and access patterns
- Plan for scalability and performance

### 2. Normalization
- Apply appropriate normal forms (typically 3NF)
- Eliminate data redundancy
- Ensure data integrity
- Balance normalization with performance needs
- Document denormalization decisions

### 3. Table Design

For each table, define:
- **Table Name**: Clear, descriptive, plural noun
- **Primary Key**: Usually auto-incrementing ID or UUID
- **Columns**: Name, data type, constraints
- **Indexes**: Performance optimization
- **Relationships**: Foreign keys and join tables

### 4. Data Types

Choose appropriate data types:
- Use VARCHAR with appropriate length limits
- Use TEXT for long content
- Use INTEGER/BIGINT for numeric IDs
- Use DECIMAL for monetary values
- Use TIMESTAMP/DATETIME for dates
- Use BOOLEAN for flags
- Use ENUM for fixed sets of values
- Use JSON for flexible structured data

### 5. Constraints

Implement data integrity:
- PRIMARY KEY constraints
- FOREIGN KEY constraints with CASCADE rules
- UNIQUE constraints for natural keys
- NOT NULL for required fields
- CHECK constraints for business rules
- DEFAULT values where appropriate

### 6. Indexes

Create indexes for:
- Primary keys (automatic)
- Foreign keys for join performance
- Columns used in WHERE clauses
- Columns used in ORDER BY
- Composite indexes for multi-column queries
- Unique indexes for natural keys

### 7. Relationships

Define relationship types:
- **One-to-One**: Rare, consider merging tables
- **One-to-Many**: Most common, use foreign key
- **Many-to-Many**: Use junction/join table

### 8. Advanced Features

Consider implementing:
- Soft deletes (deleted_at timestamp)
- Audit trails (created_at, updated_at, created_by, updated_by)
- Versioning for historical data
- Partitioning for large tables
- Materialized views for complex queries
- Full-text search indexes

## Output Format

Provide the schema in SQL DDL format with:

1. **CREATE TABLE statements** for each table
2. **Comments** explaining design decisions
3. **Indexes** for performance
4. **Foreign key constraints** with appropriate CASCADE rules
5. **Migration strategy** (up and down migrations)

## Example Output Structure

```sql
-- Users table: Core authentication and profile data
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
);

-- Create indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_active ON users(is_active) WHERE is_active = true;

Additional Considerations

• Security: Hash passwords, encrypt sensitive data
• Performance: Query patterns, index strategy
• Scalability: Sharding strategy, read replicas
• Backup: Point-in-time recovery strategy
• Documentation: ER diagrams, data dictionary
• Testing: Sample data and test scenarios

Best Practices

• Use singular or plural consistently (prefer plural)
• Use snake_case for SQL identifiers
• Add created_at and updated_at to all tables
• Use soft deletes instead of hard deletes
• Plan for data growth and archival
• Document complex business rules
• Version control all schema changes
• Test migrations in both directions