Database Design Guide
Normal Forms
| Form | Rule | Violation Example |
|---|---|---|
| 1NF | Atomic values, no repeating groups, unique rows | Storing "tag1,tag2" in one column |
| 2NF | 1NF + no partial dependency on composite key | order_items table storing product_name (depends only on product_id) |
| 3NF | 2NF + no transitive dependency (non-key → non-key) | Storing zip_code and city together (city depends on zip, not PK) |
| BCNF | 3NF + every determinant is a candidate key | Instructor → room (instructor is not a candidate key) |
| Denormalization | Intentional rule violation for read performance | Storing order_total instead of recalculating each time |
Relationship Types
| Relationship | Implementation | Example |
|---|---|---|
| One-to-One | FK in either table (or shared PK) | user → user_profile |
| One-to-Many | FK on the "many" side | user → orders (user_id FK in orders) |
| Many-to-Many | Junction table with two FKs | orders ↔ products via order_items |
| Self-Referential | FK references same table | employees.manager_id → employees.id |
| Polymorphic | Type column + FK to multiple tables | comments (commentable_type, commentable_id) |
Schema Design Patterns
-- E-commerce schema example (normalized)
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
sku VARCHAR(64) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
category_id INT REFERENCES categories(id)
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
status VARCHAR(20) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending','paid','shipped','completed','cancelled')),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
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 INT NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(10, 2) NOT NULL, -- snapshot price at purchase time
UNIQUE (order_id, product_id)
);
-- Soft delete pattern
ALTER TABLE products ADD COLUMN deleted_at TIMESTAMPTZ;
-- Filter: WHERE deleted_at IS NULL
Naming Conventions & Anti-Patterns
| Topic | Good Practice | Anti-Pattern |
|---|---|---|
| Table names | Plural, snake_case: order_items | tblOrderItem, OrderItem |
| Primary keys | id (surrogate) or natural key | OrderID (camelCase), oid (obscure) |
| Foreign keys | referenced_table_id: user_id | usr, fk1, parent |
| Booleans | is_active, has_verified_email | active (ambiguous), flag |
| Dates | created_at, updated_at (with TZ) | date1, ts (no timezone) |
| EAV pattern | Avoid for structured data | entity_attribute_value tables for everything |
| NULL usage | Use NULL for unknown; avoid for "false" | Empty string "" instead of NULL |