10 Database Schema Design Patterns Every Engineer Should Know

1 User / Profile Separation

Beginner Authentication

The Problem

Storing all user data in one table couples authentication concerns (email, password hash, MFA) with profile concerns (bio, avatar, preferences). This bloats the users table and leaks auth fields into profile APIs.

The Solution

Split into users (auth) and profiles (public/personal data) with a 1:1 relationship.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  email_verified_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE profiles (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
  display_name VARCHAR(100),
  bio TEXT,
  avatar_url VARCHAR(500),
  timezone VARCHAR(50) DEFAULT 'UTC',
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_profiles_user_id ON profiles(user_id);

See the diff →

✅ When to use

Public profiles are queried often
Auth fields should not leak to profile APIs
Different caching strategies for auth vs profile

⚠️ Trade-offs

Extra JOIN on every profile fetch
Slightly more complex registration flow

2 Soft Delete

Beginner Data Safety

The Problem

Hard-deleting rows destroys data forever. Accidental deletes are irreversible, and foreign key cascades can wipe out related records silently.

The Solution

Add a deleted_at timestamp. Filter it out in application queries. Optionally add a composite unique index that ignores soft-deleted rows.

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(id),
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  body TEXT,
  published_at TIMESTAMPTZ,
  deleted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(slug, deleted_at)
);

CREATE INDEX idx_posts_deleted_at ON posts(deleted_at)
  WHERE deleted_at IS NULL;
CREATE INDEX idx_posts_user_id ON posts(user_id, deleted_at)
  WHERE deleted_at IS NULL;

See the diff →

✅ When to use

User-facing content that might be restored
Regulatory requirements for data retention
Any table with cascading relationships

⚠️ Trade-offs

Every query needs WHERE deleted_at IS NULL
Table bloats over time without periodic purge
Unique constraints need partial indexes

3 Audit Logging

Intermediate Compliance

The Problem

You need to know who changed what, when, and what the old value was. Built-in updated_at columns only tell you the last change, not the history.

The Solution

A generic audit_logs table that stores row-level changes. For stricter requirements, create table-specific audit tables.

CREATE TABLE audit_logs (
  id BIGSERIAL PRIMARY KEY,
  table_name VARCHAR(100) NOT NULL,
  record_id INT NOT NULL,
  action VARCHAR(10) NOT NULL CHECK (action IN ('INSERT','UPDATE','DELETE')),
  old_values JSONB,
  new_values JSONB,
  changed_by INT REFERENCES users(id),
  changed_at TIMESTAMPTZ DEFAULT NOW(),
  ip_address INET,
  user_agent TEXT
);

CREATE INDEX idx_audit_table_record ON audit_logs(table_name, record_id, changed_at DESC);
CREATE INDEX idx_audit_changed_by ON audit_logs(changed_by, changed_at DESC);

See the diff →

✅ When to use

Financial, medical, or legal data
Multi-user editing environments
Debugging production issues

⚠️ Trade-offs

Write amplification: every change becomes 2 writes
Audit table grows very fast
JSONB queries are slower than normalized columns

4 Multi-tenancy (Shared Database)

Intermediate SaaS

The Problem

Your SaaS serves multiple organizations. You need to isolate their data without running a separate database per customer (too expensive).

The Solution

Add a tenant_id column to every tenant-scoped table. Use row-level security (RLS) or application-level filtering. Add composite indexes on (tenant_id, ...).

CREATE TABLE tenants (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  subdomain VARCHAR(100) NOT NULL UNIQUE,
  plan VARCHAR(20) DEFAULT 'free',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE projects (
  id SERIAL PRIMARY KEY,
  tenant_id INT NOT NULL REFERENCES tenants(id),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(tenant_id, name)
);

CREATE INDEX idx_projects_tenant ON projects(tenant_id, created_at DESC);

See the diff →

✅ When to use

B2B SaaS with many small tenants
When per-DB cost is prohibitive
Shared resources for analytics/reporting

⚠️ Trade-offs

Every query must include tenant_id filter
Harder to scale a single tenant independently
Backup/restore per tenant is complex

5 Tagging (Many-to-Many)

Beginner Relationships

The Problem

Storing tags as a CSV string in a column breaks normalization, prevents indexing, and makes tag-based queries slow and error-prone.

The Solution

A classic many-to-many with a junction table: tags, posts, and post_tags.

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  slug VARCHAR(50) NOT NULL UNIQUE,
  color VARCHAR(7) DEFAULT '#6366f1'
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  body TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE post_tags (
  post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id INT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  tagged_at TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX idx_post_tags_tag ON post_tags(tag_id);

See the diff →

✅ When to use

Content categorization (posts, products, tickets)
When tags need metadata (color, description)
When you query by tag frequently

⚠️ Trade-offs

Extra JOIN for every tagged item query
Junction table can become large

6 Tree Structure (Closure Table)

Advanced Hierarchy

The Problem

Storing hierarchical data (org charts, comment threads, categories) with a simple parent_id makes recursive queries slow and complex in SQL.

The Solution

A closure table stores every ancestor-descendant relationship, making tree queries O(1) joins.

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE category_tree (
  ancestor_id INT NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
  descendant_id INT NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
  depth INT NOT NULL CHECK (depth >= 0),
  PRIMARY KEY (ancestor_id, descendant_id)
);

-- Example: Electronics (1) → Computers (2) → Laptops (3)
-- Inserts: (1,1,0), (1,2,1), (1,3,2), (2,2,0), (2,3,1), (3,3,0)

See the diff →

✅ When to use

Deep or frequently queried hierarchies
When you need fast subtree or ancestor queries
Org charts, nested comment threads

⚠️ Trade-offs

More writes on insert/move/delete
Storage grows as O(n²) in worst case
For simple 2-level trees, parent_id is enough

7 Polymorphic Associations

Intermediate Flexibility

The Problem

You want comments (or likes, attachments, notifications) to belong to multiple entity types (posts, products, videos) without a separate comments table per type.

The Solution

Store the target table name and ID as two columns. Use CHECK constraints to validate entity types. Add composite indexes for each target.

CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  entity_type VARCHAR(20) NOT NULL
    CHECK (entity_type IN ('post','product','video')),
  entity_id INT NOT NULL,
  user_id INT NOT NULL REFERENCES users(id),
  body TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_comments_post ON comments(entity_type, entity_id)
  WHERE entity_type = 'post';
CREATE INDEX idx_comments_product ON comments(entity_type, entity_id)
  WHERE entity_type = 'product';
CREATE INDEX idx_comments_video ON comments(entity_type, entity_id)
  WHERE entity_type = 'video';

See the diff →

✅ When to use

Comments, likes, or notifications on multiple types
When separate tables would be mostly identical
Rapid prototyping with uncertain entity types

⚠️ Trade-offs

No foreign key constraints on entity_id
Query plans can be harder to optimize
Harder to enforce referential integrity in app code

8 Voting / Rating

Beginner Engagement

The Problem

Storing only an average rating loses per-user data and makes it impossible to change a vote. Storing every vote in a wide table is inflexible.

The Solution

A votes table with value (+1/-1 or 1-5). Cache aggregates in the parent table for fast reads.

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  body TEXT,
  score INT DEFAULT 0,
  vote_count INT DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE votes (
  id SERIAL PRIMARY KEY,
  post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  value SMALLINT NOT NULL CHECK (value BETWEEN -1 AND 1),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(post_id, user_id)
);

CREATE INDEX idx_votes_post ON votes(post_id, value);
CREATE INDEX idx_votes_user ON votes(user_id, created_at DESC);

See the diff →

✅ When to use

Upvote/downvote systems
Star ratings (use value 1-5)
When vote history matters

⚠️ Trade-offs

Cached aggregates can drift — recalc periodically
High write volume on hot content

9 Shopping Cart

Intermediate E-commerce

The Problem

Storing cart items as JSON in a user column loses inventory validation, price history, and makes abandoned-cart recovery impossible.

The Solution

Separate carts and cart_items tables. Snapshot the product price at add-to-cart time to prevent price-change disputes.

CREATE TABLE carts (
  id SERIAL PRIMARY KEY,
  user_id INT UNIQUE REFERENCES users(id) ON DELETE CASCADE,
  session_id VARCHAR(255) UNIQUE,
  status VARCHAR(20) DEFAULT 'active'
    CHECK (status IN ('active','converted','abandoned')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  CONSTRAINT carts_user_or_session CHECK (
    (user_id IS NOT NULL) OR (session_id IS NOT NULL)
  )
);

CREATE TABLE cart_items (
  id SERIAL PRIMARY KEY,
  cart_id INT NOT NULL REFERENCES carts(id) ON DELETE CASCADE,
  product_id INT NOT NULL,
  quantity INT NOT NULL CHECK (quantity > 0),
  unit_price_cents INT NOT NULL,
  added_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(cart_id, product_id)
);

CREATE INDEX idx_cart_items_cart ON cart_items(cart_id);

See the diff →

✅ When to use

Any e-commerce or booking flow
When price snapshots matter
Guest checkout (session-based carts)

⚠️ Trade-offs

Requires cleanup job for abandoned carts
Inventory reservation logic adds complexity

10 Messaging (Conversation + Message)

Intermediate Social

The Problem

A simple messages table with sender_id and receiver_id breaks down for group chats, message status tracking, and participant metadata.

The Solution

conversations + conversation_participants + messages. Track read status per participant, not per message.

CREATE TABLE conversations (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  type VARCHAR(20) DEFAULT 'direct'
    CHECK (type IN ('direct','group')),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE conversation_participants (
  conversation_id INT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  joined_at TIMESTAMPTZ DEFAULT NOW(),
  last_read_message_id INT,
  PRIMARY KEY (conversation_id, user_id)
);

CREATE TABLE messages (
  id SERIAL PRIMARY KEY,
  conversation_id INT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  sender_id INT NOT NULL REFERENCES users(id),
  body TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_messages_conversation ON messages(conversation_id, created_at DESC);
CREATE INDEX idx_participants_user ON conversation_participants(user_id, joined_at DESC);

See the diff →

✅ When to use

Direct messages or group chats
When read receipts per user matter
When participants can join/leave

⚠️ Trade-offs

More complex than sender/receiver columns
Conversation list queries need careful indexing

10 Database Schema Design Patterns

Patterns

1 User / Profile Separation

The Problem

The Solution

✅ When to use

⚠️ Trade-offs

2 Soft Delete

The Problem

The Solution

✅ When to use

⚠️ Trade-offs

3 Audit Logging

The Problem

The Solution

✅ When to use

⚠️ Trade-offs

4 Multi-tenancy (Shared Database)

The Problem

The Solution

✅ When to use

⚠️ Trade-offs

5 Tagging (Many-to-Many)

The Problem

The Solution

✅ When to use

⚠️ Trade-offs

6 Tree Structure (Closure Table)

The Problem

The Solution

✅ When to use

⚠️ Trade-offs

7 Polymorphic Associations

The Problem

The Solution

✅ When to use

⚠️ Trade-offs

8 Voting / Rating

The Problem

The Solution

✅ When to use

⚠️ Trade-offs

9 Shopping Cart

The Problem

The Solution

✅ When to use

⚠️ Trade-offs

10 Messaging (Conversation + Message)

The Problem

The Solution

✅ When to use

⚠️ Trade-offs

Compare These Patterns in SchemaLens

Want More?