tomevault-io Database Design Skill
This skill provides comprehensive guidance for designing efficient, scalable, and maintainable database schemas and queries.
Capabilities
- Design normalized database schemas
- Write optimized SQL queries
- Create database indexes
- Design database migrations
- Implement relationships (one-to-many, many-to-many)
- Apply database design patterns
- Optimize query performance
When to Use
Use this skill when:
- Designing new database schemas
- Optimizing slow queries
- Creating database migrations
- Modeling complex relationships
- Troubleshooting performance issues
Schema Design Principles
Normalization
First Normal Form (1NF)
- Eliminate repeating groups
- Each column contains atomic values
- Each row is unique
-- ❌ Bad - not atomic, repeating
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
phones VARCHAR(500) -- '555-1234, 555-5678, 555-9012'
);
-- ✅ Good - atomic, normalized
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE user_phones (
id INT PRIMARY KEY,
user_id INT REFERENCES users(id),
phone VARCHAR(20)
);
Second Normal Form (2NF)
- Meet 1NF requirements
- Remove partial dependencies
-- ❌ Bad - order_date depends only on order_id, not product_id
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
order_date DATE, -- Partial dependency
PRIMARY KEY (order_id, product_id)
);
-- ✅ Good - separate tables
CREATE TABLE orders (
id INT PRIMARY KEY,
order_date DATE
);
CREATE TABLE order_items (
order_id INT REFERENCES orders(id),
product_id INT REFERENCES products(id),
quantity INT,
PRIMARY KEY (order_id, product_id)
);
Third Normal Form (3NF)
- Meet 2NF requirements
- Remove transitive dependencies
-- ❌ Bad - city depends on zip_code, not directly on user_id
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
zip_code VARCHAR(10),
city VARCHAR(100) -- Transitive dependency
);
-- ✅ Good - separate zip code data
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
zip_code VARCHAR(10) REFERENCES zip_codes(code)
);
CREATE TABLE zip_codes (
code VARCHAR(10) PRIMARY KEY,
city VARCHAR(100),
state VARCHAR(2)
);
Common Relationships
One-to-Many
-- User has many orders
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100) UNIQUE NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(10, 2),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
Many-to-Many
-- Students and courses (many-to-many)
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE courses (
id INT PRIMARY KEY,
name VARCHAR(100),
credits INT
);
-- Junction table
CREATE TABLE student_courses (
student_id INT,
course_id INT,
enrollment_date DATE DEFAULT CURRENT_DATE,
grade VARCHAR(2),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);
CREATE INDEX idx_student_courses_student ON student_courses(student_id);
CREATE INDEX idx_student_courses_course ON student_courses(course_id);
Self-Referencing
-- Employee hierarchy (self-referencing)
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES employees(id)
);
CREATE INDEX idx_employees_manager ON employees(manager_id);
Indexes
When to Add Indexes
✅ Add indexes for:
- Primary keys (automatic)
- Foreign keys
- Columns used in WHERE clauses frequently
- Columns used in JOIN conditions
- Columns used in ORDER BY
- Columns used for uniqueness
❌ Avoid indexes on:
- Small tables (< 1000 rows)
- Columns with low cardinality (few unique values)
- Columns that change frequently
- Wide columns (large text, blobs)
Index Types
-- Single column index
CREATE INDEX idx_users_email ON users(email);
-- Composite index (order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date DESC);
-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);
-- Partial index (PostgreSQL)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
-- Full-text index (PostgreSQL)
CREATE INDEX idx_posts_content ON posts USING GIN(to_tsvector('english', content));
-- Covering index (includes extra columns)
CREATE INDEX idx_orders_user_covering
ON orders(user_id)
INCLUDE (order_date, total_amount);
Query Optimization
Use EXPLAIN to Analyze Queries
-- Analyze query execution plan
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC
LIMIT 10;
Avoid N+1 Queries
-- ❌ Bad - N+1 query problem
-- First query: get all users
SELECT * FROM users;
-- Then for each user (N queries):
SELECT * FROM orders WHERE user_id = ?;
-- ✅ Good - single query with JOIN
SELECT
u.id,
u.name,
o.id as order_id,
o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Or use subquery/CTE
WITH user_orders AS (
SELECT
user_id,
COUNT(*) as order_count,
SUM(total_amount) as total_spent
FROM orders
GROUP BY user_id
)
SELECT
u.*,
COALESCE(uo.order_count, 0) as order_count,
COALESCE(uo.total_spent, 0) as total_spent
FROM users u
LEFT JOIN user_orders uo ON u.id = uo.user_id;
Use Appropriate JOINs
-- INNER JOIN - only matching rows
SELECT u.name, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- LEFT JOIN - all left table rows + matches
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- RIGHT JOIN - all right table rows + matches
SELECT u.name, o.total_amount
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;
-- FULL OUTER JOIN - all rows from both tables
SELECT u.name, o.total_amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
Optimize WHERE Clauses
-- ❌ Bad - function on indexed column prevents index usage
SELECT * FROM users WHERE UPPER(email) = 'JOHN@EXAMPLE.COM';
-- ✅ Good - use index directly
SELECT * FROM users WHERE email = 'john@example.com';
-- ❌ Bad - implicit type conversion
SELECT * FROM orders WHERE user_id = '123'; -- user_id is INT
-- ✅ Good - correct type
SELECT * FROM orders WHERE user_id = 123;
-- ✅ Good - use IN for multiple values
SELECT * FROM users WHERE id IN (1, 2, 3, 4, 5);
-- ✅ Good - use BETWEEN for ranges
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
Database Migrations
Migration Best Practices
-- Migration: 001_create_users_table.sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
-- Migration: 002_add_user_profile.sql
CREATE TABLE user_profiles (
user_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
bio TEXT,
avatar_url VARCHAR(500),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- Migration: 003_add_user_status.sql
-- Add column with default value to avoid null issues
ALTER TABLE users
ADD COLUMN status VARCHAR(20) DEFAULT 'active' NOT NULL;
-- Add index for new column
CREATE INDEX idx_users_status ON users(status);
-- Migration: 004_modify_user_email.sql
-- Increase email column size
ALTER TABLE users
ALTER COLUMN email TYPE VARCHAR(255);
-- Migration: 005_rename_column.sql
ALTER TABLE users
RENAME COLUMN username TO user_name;
-- Rollback: drop_users_table.sql
DROP TABLE IF EXISTS user_profiles;
DROP TABLE IF EXISTS users;
Performance Patterns
Pagination
-- ✅ Good - offset pagination (simple but slow for large offsets)
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 40; -- Page 3
-- ✅ Better - cursor-based pagination (faster)
SELECT * FROM users
WHERE created_at < '2024-01-15 10:00:00'
ORDER BY created_at DESC
LIMIT 20;
-- ✅ Best - keyset pagination (most efficient)
SELECT * FROM users
WHERE (created_at, id) < ('2024-01-15 10:00:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Soft Deletes
-- Add deleted_at column
ALTER TABLE users
ADD COLUMN deleted_at TIMESTAMP NULL;
CREATE INDEX idx_users_deleted_at ON users(deleted_at);
-- "Delete" user (soft delete)
UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = 123;
-- Query only active users
SELECT * FROM users
WHERE deleted_at IS NULL;
-- Create view for active users
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;
Auditing
-- Audit log table
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name VARCHAR(50) NOT NULL,
record_id INT NOT NULL,
action VARCHAR(20) NOT NULL, -- INSERT, UPDATE, DELETE
old_data JSONB,
new_data JSONB,
user_id INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_audit_table_record ON audit_log(table_name, record_id);
CREATE INDEX idx_audit_created_at ON audit_log(created_at);
-- Trigger function (PostgreSQL)
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO audit_log (table_name, record_id, action, old_data)
VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', row_to_json(OLD));
RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', row_to_json(OLD), row_to_json(NEW));
RETURN NEW;
ELSIF TG_OP = 'INSERT' THEN
INSERT INTO audit_log (table_name, record_id, action, new_data)
VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', row_to_json(NEW));
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
-- Apply trigger to table
CREATE TRIGGER users_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();
Data Types
Choose Appropriate Types
-- ✅ Good type choices
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL, -- Variable length string
sku VARCHAR(50) UNIQUE NOT NULL, -- Fixed pattern string
price DECIMAL(10, 2) NOT NULL, -- Exact decimal for money
quantity INT NOT NULL DEFAULT 0, -- Whole numbers
weight DECIMAL(8, 2), -- Decimal for measurements
is_active BOOLEAN DEFAULT true, -- Boolean flag
description TEXT, -- Long text
metadata JSONB, -- Structured data (PostgreSQL)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- ❌ Avoid - inappropriate types
CREATE TABLE bad_products (
id INT, -- Should be SERIAL
price FLOAT, -- Imprecise for money
quantity VARCHAR(10), -- Should be INT
is_active VARCHAR(5), -- Should be BOOLEAN
tags VARCHAR(1000) -- Should be JSONB array or separate table
);
Best Practices
- Normalize data to 3NF, denormalize only when needed
- Use appropriate data types for each column
- Add indexes on foreign keys and frequently queried columns
- Use transactions for related operations
- Implement constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY)
- Version migrations and make them reversible
- Monitor query performance with EXPLAIN
- Use connection pooling for better resource management
- Implement soft deletes for important data
- Add audit logging for compliance and debugging
Converted and distributed by TomeVault — claim your Tome and manage your conversions.