database-design

Database Design Skill

Naming Conventions

Element	Convention	Example
Tables	snake_case, plural	users, order_items
Columns	snake_case	created_at, user_id
Primary Key	id	UUID or BIGINT
Foreign Key	{table_singular}_id	user_id
Indexes	idx_{table}_{columns}	idx_users_email

Required Columns

Every table MUST have:

CREATE TABLE example (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Data Types (PostgreSQL)

Use Case	Type
Primary Key	UUID or BIGINT
Text	TEXT or VARCHAR(n)
Integer	INTEGER or BIGINT
Money	NUMERIC(12,2)
Boolean	BOOLEAN
Timestamp	TIMESTAMPTZ
JSON	JSONB

Indexing Strategy

Always index:

• Foreign keys
• Columns in WHERE clauses
• Columns in ORDER BY

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Partial index
CREATE INDEX idx_users_active ON users(email) WHERE status = 'active';

Relationships

-- One-to-Many
CREATE TABLE books (
  id UUID PRIMARY KEY,
  author_id UUID NOT NULL REFERENCES authors(id) ON DELETE CASCADE
);
CREATE INDEX idx_books_author ON books(author_id);

-- Many-to-Many
CREATE TABLE categories_products (
  category_id UUID REFERENCES categories(id) ON DELETE CASCADE,
  product_id UUID REFERENCES products(id) ON DELETE CASCADE,
  PRIMARY KEY (category_id, product_id)
);

Soft Deletes

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
CREATE INDEX idx_users_active ON users(id) WHERE deleted_at IS NULL;

Migration Rules

1. Never modify existing migrations
2. Always include down() for rollbacks
3. Test both directions
4. Use CONCURRENTLY for large table indexes

Checklist

• All tables have id, created_at, updated_at
• Foreign keys are indexed
• Naming conventions consistent
• Cascade behavior explicit
• Migrations reversible