Database Schema Designer
Design production-ready database schemas with best practices built-in.
Quick Start
Just describe your data model:
design a schema for an e-commerce platform with users, products, orders
You'll get a complete SQL schema like:
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
total DECIMAL(10,2) NOT NULL,
INDEX idx_orders_user (user_id)
);
What to include in your request:
- Entities (users, products, orders)
- Key relationships (users have orders, orders have items)
- Scale hints (high-traffic, millions of records)
- Database preference (SQL/NoSQL) - defaults to SQL if not specified
Triggers
Key Terms
Quick Reference
Process Overview
Your Data Requirements
|
v
+-----------------------------------------------------+
| Phase 1: ANALYSIS |
| * Identify entities and relationships |
| * Determine access patterns (read vs write heavy) |
| * Choose SQL or NoSQL based on requirements |
+-----------------------------------------------------+
|
v
+-----------------------------------------------------+
| Phase 2: DESIGN |
| * Normalize to 3NF (SQL) or embed/reference (NoSQL) |
| * Define primary keys and foreign keys |
| * Choose appropriate data types |
| * Add constraints (UNIQUE, CHECK, NOT NULL) |
+-----------------------------------------------------+
|
v
+-----------------------------------------------------+
| Phase 3: OPTIMIZE |
| * Plan indexing strategy |
| * Consider denormalization for read-heavy queries |
| * Add timestamps (created_at, updated_at) |
+-----------------------------------------------------+
|
v
+-----------------------------------------------------+
| Phase 4: MIGRATE |
| * Generate migration scripts (up + down) |
| * Ensure backward compatibility |
| * Plan zero-downtime deployment |
+-----------------------------------------------------+
|
v
Production-Ready Schema
Commands
Workflow: Start with design schema → iterate with normalize → optimize with add indexes → evolve with migration
Core Principles
Anti-Patterns
Verification Checklist
After designing a schema:
Normal Forms
1st Normal Form (1NF)
-- BAD: Multiple values in column
CREATE TABLE orders (
id INT PRIMARY KEY,
product_ids VARCHAR(255) -- '101,102,103'
);
-- GOOD: Separate table for items
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT
);
CREATE TABLE order_items (
id INT PRIMARY KEY,
order_id INT REFERENCES orders(id),
product_id INT
);
2nd Normal Form (2NF)
-- BAD: customer_name depends only on customer_id
CREATE TABLE order_items (
order_id INT,
product_id INT,
customer_name VARCHAR(100), -- Partial dependency!
PRIMARY KEY (order_id, product_id)
);
-- GOOD: Customer data in separate table
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100)
);
3rd Normal Form (3NF)
-- BAD: country depends on postal_code
CREATE TABLE customers (
id INT PRIMARY KEY,
postal_code VARCHAR(10),
country VARCHAR(50) -- Transitive dependency!
);
-- GOOD: Separate postal_codes table
CREATE TABLE postal_codes (
code VARCHAR(10) PRIMARY KEY,
country VARCHAR(50)
);
When to Denormalize
-- Denormalized for performance
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
total_amount DECIMAL(10,2), -- Calculated
item_count INT -- Calculated
);
String Types
-- Good sizing
email VARCHAR(255)
phone VARCHAR(20)
country_code CHAR(2)
Numeric Types
-- ALWAYS use DECIMAL for money
price DECIMAL(10, 2) -- $99,999,999.99
-- NEVER use FLOAT for money
price FLOAT -- Rounding errors!
Date/Time Types
DATE -- 2025-10-31
TIME -- 14:30:00
DATETIME -- 2025-10-31 14:30:00
TIMESTAMP -- Auto timezone conversion
-- Always store in UTC
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Boolean
-- PostgreSQL
is_active BOOLEAN DEFAULT TRUE
-- MySQL
is_active TINYINT(1) DEFAULT 1
When to Create Indexes
-- Foreign key index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- Query pattern index
CREATE INDEX idx_orders_status_date ON orders(status, created_at);
Index Types
Composite Index Order
CREATE INDEX idx_customer_status ON orders(customer_id, status);
-- Uses index (customer_id first)
SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';
-- Does NOT use index (status alone)
SELECT * FROM orders WHERE status = 'pending';
Rule: Most selective column first, or column most queried alone.
Index Pitfalls
Primary Keys
-- Auto-increment (simple)
id INT AUTO_INCREMENT PRIMARY KEY
-- UUID (distributed systems)
id CHAR(36) PRIMARY KEY DEFAULT (UUID())
-- Composite (junction tables)
PRIMARY KEY (student_id, course_id)
Foreign Keys
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE -- Delete children with parent
ON DELETE RESTRICT -- Prevent deletion if referenced
ON DELETE SET NULL -- Set to NULL when parent deleted
ON UPDATE CASCADE -- Update children when parent changes
Other Constraints
-- Unique
email VARCHAR(255) UNIQUE NOT NULL
-- Composite unique
UNIQUE (student_id, course_id)
-- Check
price DECIMAL(10,2) CHECK (price >= 0)
discount INT CHECK (discount BETWEEN 0 AND 100)
-- Not null
name VARCHAR(100) NOT NULL
One-to-Many
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customers(id)
);
CREATE TABLE order_items (
id INT PRIMARY KEY,
order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id INT NOT NULL,
quantity INT NOT NULL
);
Many-to-Many
-- Junction table
CREATE TABLE enrollments (
student_id INT REFERENCES students(id) ON DELETE CASCADE,
course_id INT REFERENCES courses(id) ON DELETE CASCADE,
enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (student_id, course_id)
);
Self-Referencing
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
manager_id INT REFERENCES employees(id)
);
Polymorphic
-- Approach 1: Separate FKs (stronger integrity)
CREATE TABLE comments (
id INT PRIMARY KEY,
content TEXT NOT NULL,
post_id INT REFERENCES posts(id),
photo_id INT REFERENCES photos(id),
CHECK (
(post_id IS NOT NULL AND photo_id IS NULL) OR
(post_id IS NULL AND photo_id IS NOT NULL)
)
);
-- Approach 2: Type + ID (flexible, weaker integrity)
CREATE TABLE comments (
id INT PRIMARY KEY,
content TEXT NOT NULL,
commentable_type VARCHAR(50) NOT NULL,
commentable_id INT NOT NULL
);
Embedding vs Referencing
Embedded Document
{
"_id": "order_123",
"customer": {
"id": "cust_456",
"name": "Jane Smith",
"email": "[email protected]"
},
"items": [
{ "product_id": "prod_789", "quantity": 2, "price": 29.99 }
],
"total": 109.97
}
Referenced Document
{
"_id": "order_123",
"customer_id": "cust_456",
"item_ids": ["item_1", "item_2"],
"total": 109.97
}
MongoDB Indexes
// Single field
db.users.createIndex({ email: 1 }, { unique: true });
// Composite
db.orders.createIndex({ customer_id: 1, created_at: -1 });
// Text search
db.articles.createIndex({ title: "text", content: "text" });
// Geospatial
db.stores.createIndex({ location: "2dsphere" });
Migration Best Practices
Adding a Column (Zero-Downtime)
-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Step 2: Deploy code that writes to new column
-- Step 3: Backfill existing rows
UPDATE users SET phone = '' WHERE phone IS NULL;
-- Step 4: Make required (if needed)
ALTER TABLE users MODIFY phone VARCHAR(20) NOT NULL;
Renaming a Column (Zero-Downtime)
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
-- Step 2: Copy data
UPDATE users SET email_address = email;
-- Step 3: Deploy code reading from new column
-- Step 4: Deploy code writing to new column
-- Step 5: Drop old column
ALTER TABLE users DROP COLUMN email;
Migration Template
-- Migration: YYYYMMDDHHMMSS_description.sql
-- UP
BEGIN;
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
CREATE INDEX idx_users_phone ON users(phone);
COMMIT;
-- DOWN
BEGIN;
DROP INDEX idx_users_phone ON users;
ALTER TABLE users DROP COLUMN phone;
COMMIT;
Query Analysis
EXPLAIN SELECT * FROM orders
WHERE customer_id = 123 AND status = 'pending';
N+1 Query Problem
# BAD: N+1 queries
orders = db.query("SELECT * FROM orders")
for order in orders:
customer = db.query(f"SELECT * FROM customers WHERE id = {order.customer_id}")
# GOOD: Single JOIN
results = db.query("""
SELECT orders.*, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id
""")
Optimization Techniques
Extension Points
- Database-Specific Patterns: Add MySQL vs PostgreSQL vs SQLite variations
- Advanced Patterns: Time-series, event sourcing, CQRS, multi-tenancy
- ORM Integration: TypeORM, Prisma, SQLAlchemy patterns
- Monitoring: Query performance tracking, slow query alerts
