Database Patterns

Database schema design and best practices.

Naming Conventions

Tables

• Use plural, snake_case: users, user_profiles
• Junction tables: users_roles or user_role

Columns

• Use snake_case: first_name, created_at
• Primary keys: id
• Foreign keys: user_id, post_id

Indexes

• Use descriptive names: idx_users_email
• Unique indexes: uniq_users_email

Common Columns

Every table should have:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  -- ... other columns
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ID Strategies

UUID

id UUID PRIMARY KEY DEFAULT gen_random_uuid()

• Globally unique
• No sequential exposure
• Larger storage

Auto-increment

id SERIAL PRIMARY KEY

• Simple and efficient
• Exposes record count
• Only unique per table

Prefixed IDs

-- In application code
const userId = `usr_${uuidv4()}`;

• Self-documenting
• Easy to identify type
• Good for debugging

Relationships

One-to-Many

CREATE TABLE posts (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id),
  title TEXT NOT NULL
);

CREATE INDEX idx_posts_user_id ON posts(user_id);

Many-to-Many

CREATE TABLE users_roles (
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  PRIMARY KEY (user_id, role_id)
);

One-to-One

CREATE TABLE user_profiles (
  user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  bio TEXT,
  avatar_url TEXT
);

Soft Deletes

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

-- Query active users
SELECT * FROM users WHERE deleted_at IS NULL;

-- Soft delete
UPDATE users SET deleted_at = now() WHERE id = 'xxx';

Enum vs. Lookup Tables

Enum (for stable values)

CREATE TYPE user_status AS ENUM ('active', 'inactive', 'banned');

CREATE TABLE users (
  status user_status NOT NULL DEFAULT 'active'
);

Lookup Table (for changeable values)

CREATE TABLE statuses (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE users (
  status_id INT REFERENCES statuses(id)
);

Indexes

When to Index

• Foreign keys
• Columns used in WHERE clauses
• Columns used in ORDER BY
• Columns used in JOIN conditions

Index Types

-- B-tree (default, most common)
CREATE INDEX idx_users_email ON users(email);

-- Unique
CREATE UNIQUE INDEX uniq_users_email ON users(email);

-- Partial (filtered)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Composite
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at);

Migrations

File Naming

20240115_001_create_users.sql
20240115_002_add_user_status.sql

Structure

-- Up migration
CREATE TABLE users (
  id UUID PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- Down migration (in separate file or section)
DROP TABLE users;

Best Practices

• One change per migration
• Make migrations reversible
• Test migrations on copy of production data
• Backup before running migrations

JSON Columns

When to use:

• Flexible/dynamic attributes
• Rarely queried data
• Third-party data storage

CREATE TABLE users (
  id UUID PRIMARY KEY,
  preferences JSONB DEFAULT '{}'
);

-- Query JSON
SELECT * FROM users WHERE preferences->>'theme' = 'dark';

-- Index JSON
CREATE INDEX idx_users_theme ON users((preferences->>'theme'));

Performance Tips

1. Use appropriate types - Don't use TEXT for everything
2. Index strategically - Too many indexes slow writes
3. Normalize first - Denormalize for performance later
4. Use EXPLAIN ANALYZE - Understand query plans
5. Vacuum regularly - Keep statistics fresh