database-schema-designer

Normal Forms

1st Normal Form (1NF)

-- BAD: Multiple values in columnCREATE TABLE orders (  id INT PRIMARY KEY,  product_ids VARCHAR(255)  -- '101,102,103');-- GOOD: Separate table for itemsCREATE 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_idCREATE 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 tableCREATE TABLE customers (  id INT PRIMARY KEY,  name VARCHAR(100));

3rd Normal Form (3NF)

-- BAD: country depends on postal_codeCREATE TABLE customers (  id INT PRIMARY KEY,  postal_code VARCHAR(10),  country VARCHAR(50)  -- Transitive dependency!);-- GOOD: Separate postal_codes tableCREATE TABLE postal_codes (  code VARCHAR(10) PRIMARY KEY,  country VARCHAR(50));

When to Denormalize

-- Denormalized for performanceCREATE TABLE orders (  id INT PRIMARY KEY,  customer_id INT,  total_amount DECIMAL(10,2),  -- Calculated  item_count INT               -- Calculated);

String Types

-- Good sizingemail VARCHAR(255)phone VARCHAR(20)country_code CHAR(2)

Numeric Types

-- ALWAYS use DECIMAL for moneyprice DECIMAL(10, 2)  -- $99,999,999.99-- NEVER use FLOAT for moneyprice FLOAT  -- Rounding errors!

Date/Time Types

DATE        -- 2025-10-31TIME        -- 14:30:00DATETIME    -- 2025-10-31 14:30:00TIMESTAMP   -- Auto timezone conversion-- Always store in UTCcreated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMPupdated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Boolean

-- PostgreSQLis_active BOOLEAN DEFAULT TRUE-- MySQLis_active TINYINT(1) DEFAULT 1

When to Create Indexes

-- Foreign key indexCREATE INDEX idx_orders_customer ON orders(customer_id);-- Query pattern indexCREATE 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

-- Uniqueemail VARCHAR(255) UNIQUE NOT NULL-- Composite uniqueUNIQUE (student_id, course_id)-- Checkprice DECIMAL(10,2) CHECK (price >= 0)discount INT CHECK (discount BETWEEN 0 AND 100)-- Not nullname 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 tableCREATE 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 fielddb.users.createIndex({ email: 1 }, { unique: true });// Compositedb.orders.createIndex({ customer_id: 1, created_at: -1 });// Text searchdb.articles.createIndex({ title: "text", content: "text" });// Geospatialdb.stores.createIndex({ location: "2dsphere" });

Migration Best Practices

Adding a Column (Zero-Downtime)

-- Step 1: Add nullable columnALTER TABLE users ADD COLUMN phone VARCHAR(20);-- Step 2: Deploy code that writes to new column-- Step 3: Backfill existing rowsUPDATE 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 columnALTER TABLE users ADD COLUMN email_address VARCHAR(255);-- Step 2: Copy dataUPDATE 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 columnALTER TABLE users DROP COLUMN email;

Migration Template

-- Migration: YYYYMMDDHHMMSS_description.sql-- UPBEGIN;ALTER TABLE users ADD COLUMN phone VARCHAR(20);CREATE INDEX idx_users_phone ON users(phone);COMMIT;-- DOWNBEGIN;DROP INDEX idx_users_phone ON users;ALTER TABLE users DROP COLUMN phone;COMMIT;

Query Analysis

EXPLAIN SELECT * FROM ordersWHERE customer_id = 123 AND status = 'pending';

N+1 Query Problem

# BAD: N+1 queriesorders = db.query("SELECT * FROM orders")for order in orders:    customer = db.query(f"SELECT * FROM customers WHERE id = {order.customer_id}")# GOOD: Single JOINresults = db.query("""    SELECT orders.*, customers.name    FROM orders    JOIN customers ON orders.customer_id = customers.id""")

Optimization Techniques