database-schema-designer

Database Schema Designer

Overview

This skill provides comprehensive guidance for designing robust, scalable database schemas for both SQL and NoSQL databases. Whether building from scratch or evolving existing schemas, this framework ensures data integrity, performance, and maintainability.

When to use this skill:

• Designing new database schemas
• Refactoring or migrating existing schemas
• Optimizing database performance
• Choosing between SQL and NoSQL approaches
• Creating database migrations
• Establishing indexing strategies
• Modeling complex relationships
• Planning data archival and partitioning

Database Design Philosophy

Core Principles

1. Model the Domain, Not the UI

• Schema reflects business entities and relationships
• Don't let UI requirements drive data structure
• Separate presentation concerns from data model

2. Optimize for Reads or Writes (Not Both)

• OLTP (transactional): Normalized, optimized for writes
• OLAP (analytical): Denormalized, optimized for reads
• Choose based on access patterns

3. Plan for Scale From Day One

• Indexing strategy
• Partitioning approach
• Caching layer
• Read replicas

4. Data Integrity Over Performance

• Use constraints, foreign keys, validation
• Performance issues can be optimized later
• Data corruption is costly to fix

---

SQL Database Design

Normalization

Database normalization reduces redundancy and ensures data integrity.

1st Normal Form (1NF)

Rule: Each column contains atomic (indivisible) values, no repeating groups.

-- ❌ Violates 1NF (multiple values in one column)
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  product_ids VARCHAR(255)  -- '101,102,103' (bad!)
);

-- ✅ Follows 1NF
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT
);

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

2nd Normal Form (2NF)

Rule: Must be in 1NF + all non-key columns depend on the entire primary key.