Schema Design Best Practices - Building Robust Databases
Introduction
You've learned CREATE TABLE, constraints, foreign keys, indexes, and normalization. Now let's put it all together! This lesson covers practical schema design principles for building production-ready databases.
Naming Conventions
Table Names
- ✅ Use plural nouns
CREATE TABLE customers (...); -- Good
CREATE TABLE customer (...); -- Avoid
- ✅ Use snake_case
CREATE TABLE order_items (...); -- Good
CREATE TABLE OrderItems (...); -- Avoid
Column Names
- ✅ Descriptive and clear
created_at TIMESTAMPTZ -- Good
dt TIMESTAMPTZ -- Avoid
- ✅ Avoid reserved words
user_order INTEGER -- Good
order INTEGER -- Bad (reserved word!)
Constraint Names
- ✅ Use prefixes:
pk_,fk_,uq_,chk_,idx_
CONSTRAINT pk_customers PRIMARY KEY (id)
CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
CONSTRAINT uq_users_email UNIQUE (email)
CONSTRAINT chk_products_price CHECK (price >= 0)
CREATE INDEX idx_orders_customer ON orders(customer_id)
Essential Columns
Every Table Should Have
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY, -- 1. Primary key
name VARCHAR(200) NOT NULL, -- 2. Business data
created_at TIMESTAMPTZ DEFAULT NOW(), -- 3. Created timestamp
updated_at TIMESTAMPTZ DEFAULT NOW() -- 4. Updated timestamp
);
Why:
- id: Unique identifier
- created_at: Track when record was added
- updated_at: Track last modification
Optional Audit Columns
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total DECIMAL(10,2) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
created_by INTEGER REFERENCES users(id), -- Who created?
updated_by INTEGER REFERENCES users(id), -- Who last updated?
deleted_at TIMESTAMPTZ -- Soft delete
);
Soft Deletes Pattern
Instead of DELETE, mark as deleted:
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
deleted_at TIMESTAMPTZ
);
-- "Delete" a user
UPDATE users SET deleted_at = NOW() WHERE id = 1;
-- Query active users
SELECT * FROM users WHERE deleted_at IS NULL;
-- Query deleted users
SELECT * FROM users WHERE deleted_at IS NOT NULL;
Benefits:
- Recover accidentally deleted data
- Maintain audit trail
- Preserve foreign key relationships
E-Commerce Schema Example
Complete schema with best practices:
-- Users
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
last_login_at TIMESTAMPTZ,
deleted_at TIMESTAMPTZ,
CONSTRAINT chk_users_email CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$')
);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(active) WHERE deleted_at IS NULL;
-- Addresses
CREATE TABLE addresses (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
address_line1 VARCHAR(255) NOT NULL,
address_line2 VARCHAR(255),
city VARCHAR(100) NOT NULL,
state VARCHAR(50),
postal_code VARCHAR(20) NOT NULL,
country CHAR(2) NOT NULL DEFAULT 'US',
is_default BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_addresses_user ON addresses(user_id);
-- Categories
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) UNIQUE NOT NULL,
slug VARCHAR(100) UNIQUE NOT NULL,
description TEXT,
parent_id INTEGER REFERENCES categories(id),
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_categories_slug ON categories(slug);
CREATE INDEX idx_categories_parent ON categories(parent_id);
-- Products
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
slug VARCHAR(200) UNIQUE NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
cost DECIMAL(10,2) CHECK (cost >= 0),
category_id INTEGER REFERENCES categories(id),
stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
deleted_at TIMESTAMPTZ
);
CREATE INDEX idx_products_slug ON products(slug);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_active ON products(active) WHERE deleted_at IS NULL;
-- Orders
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
subtotal DECIMAL(10,2) NOT NULL CHECK (subtotal >= 0),
tax DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (tax >= 0),
shipping DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (shipping >= 0),
total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
shipping_address_id BIGINT REFERENCES addresses(id),
billing_address_id BIGINT REFERENCES addresses(id),
notes TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
shipped_at TIMESTAMPTZ,
delivered_at TIMESTAMPTZ
);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at);
-- Order Items
CREATE TABLE order_items (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id BIGINT NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
created_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE (order_id, product_id)
);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);
Schema Versioning
Track schema changes over time:
CREATE TABLE schema_migrations (
version VARCHAR(20) PRIMARY KEY,
description TEXT,
applied_at TIMESTAMPTZ DEFAULT NOW()
);
INSERT INTO schema_migrations (version, description) VALUES
('001', 'Initial schema'),
('002', 'Add soft delete to users'),
('003', 'Add indexes on foreign keys');
Data Type Choices
IDs
SERIAL -- Up to 2 billion rows
BIGSERIAL -- Billions+ rows (use this by default!)
UUID -- Globally unique, distributed systems
Money
DECIMAL(10,2) -- Exact precision (always use for money!)
NUMERIC(10,2) -- Same as DECIMAL
Timestamps
TIMESTAMPTZ -- With timezone (always use this!)
TIMESTAMP -- Without timezone (avoid)
Strings
VARCHAR(255) -- Email, names
TEXT -- Descriptions, content
CHAR(2) -- Fixed codes (US, CA)
Booleans
BOOLEAN -- TRUE/FALSE/NULL
Performance Optimization Patterns
Partial Indexes
-- Index only active records
CREATE INDEX idx_products_active ON products(name)
WHERE active = TRUE AND deleted_at IS NULL;
Composite Indexes
-- Optimize common query
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
Include Columns (Covering Indexes)
CREATE INDEX idx_orders_user_total ON orders(user_id)
INCLUDE (total, created_at);
Common Anti-Patterns to Avoid
❌ Storing Arrays/JSON Instead of Proper Tables
-- ❌ Bad
CREATE TABLE users (
id SERIAL PRIMARY KEY,
tags TEXT[] -- Hard to query, no referential integrity
);
-- ✅ Good
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name VARCHAR(50) UNIQUE
);
CREATE TABLE user_tags (
user_id INTEGER REFERENCES users(id),
tag_id INTEGER REFERENCES tags(id),
PRIMARY KEY (user_id, tag_id)
);
❌ EAV (Entity-Attribute-Value) Pattern
-- ❌ Bad: Generic "attributes" table
CREATE TABLE entity_attributes (
entity_id INTEGER,
attribute_name VARCHAR(50),
attribute_value TEXT
);
-- ✅ Good: Proper columns
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(200),
color VARCHAR(50),
size VARCHAR(20)
);
❌ No Foreign Keys
-- ❌ Bad: No referential integrity
CREATE TABLE orders (
customer_id INTEGER -- No constraint!
);
-- ✅ Good: Foreign key enforced
CREATE TABLE orders (
customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE
);
Checklist for Production-Ready Schema
- ✅ All tables have primary keys
- ✅ Foreign keys defined with appropriate ON DELETE actions
- ✅ Indexes on all foreign keys
- ✅ Indexes on frequently queried columns
- ✅ Appropriate data types (DECIMAL for money, TIMESTAMPTZ for time)
- ✅ NOT NULL on required fields
- ✅ Unique constraints on business keys (email, username, etc.)
- ✅ CHECK constraints for validation
- ✅ Default values where appropriate
- ✅ created_at and updated_at timestamps
- ✅ Named constraints for clarity
- ✅ Consistent naming conventions
- ✅ Schema versioning/migration system
Key Takeaways
- ✅ Consistent naming: snake_case, plural table names
- ✅ Essential columns: id, created_at, updated_at
- ✅ Soft deletes preserve data
- ✅ Index foreign keys always
- ✅ Use BIGSERIAL for future-proofing
- ✅ TIMESTAMPTZ for timestamps
- ✅ DECIMAL for money
- ✅ Normalize but denormalize when performance demands
- ✅ Version your schema with migrations
Next Steps
Congratulations! You've completed Module 7 and mastered database design fundamentals. In Module 8, we'll apply everything you've learned in a Capstone Project—building a complete database for a real-world application!
Ready to build something amazing!
اختبار قصير
التالي · الدرس 38 من 44
Project Overview - Building a Business Database
قراءة 4 دقيقة
أنهيت 37 من 44 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

