Database Design Anti-Patterns: Mistakes That Will Haunt You Later

The Cost of Early Mistakes

Database design decisions made at the beginning of a project are among the hardest to reverse. Application code can be refactored and redeployed in minutes. Changing a database schema on a table with 50 million rows requires careful planning, migration scripts, downtime coordination, and rollback procedures.

Understanding what not to do is as valuable as knowing best practices. Here are the most destructive database design anti-patterns and how to avoid them.

Anti-Pattern 1: The Entity-Attribute-Value (EAV) Table

EAV is seductive when you need flexible schemas — just add rows instead of columns. In practice it creates unmaintainable, slow, and query-hostile data structures.

-- ANTI-PATTERN: EAV table
CREATE TABLE product_attributes (
  product_id  UUID,
  attr_name   VARCHAR(100),
  attr_value  TEXT  -- Everything is text, no type safety
);

-- Getting a product's price requires a pivot:
SELECT MAX(CASE WHEN attr_name = 'price' THEN attr_value::DECIMAL END) AS price,
       MAX(CASE WHEN attr_name = 'weight' THEN attr_value::DECIMAL END) AS weight
FROM product_attributes
WHERE product_id = $1;

-- BETTER: Use JSONB for truly dynamic attributes
CREATE TABLE products (
  id         UUID PRIMARY KEY,
  name       VARCHAR(255) NOT NULL,
  price      DECIMAL(10,2) NOT NULL,  -- Structured, typed, indexed
  attributes JSONB  -- Flexible but stored as structured JSON
);

-- Querying JSONB is clean and indexable
SELECT * FROM products
WHERE (attributes->>'weight')::DECIMAL < 1.5
  AND attributes ? 'color';  -- Has 'color' key

CREATE INDEX idx_products_attrs ON products USING GIN(attributes);

Use JSONB for genuinely flexible, user-defined attributes. Use regular columns for anything your application logic depends on.

Anti-Pattern 2: Storing Comma-Separated Values

Storing multiple values in a single column violates First Normal Form (1NF) and makes every query that touches those values painful.

-- ANTI-PATTERN: comma-separated list
CREATE TABLE articles (
  id   UUID PRIMARY KEY,
  tags TEXT  -- "postgresql,performance,indexing"
);

-- Querying requires LIKE or string functions — no index possible
SELECT * FROM articles WHERE tags LIKE '%postgresql%';

-- CORRECT: Junction table
CREATE TABLE article_tags (
  article_id UUID REFERENCES articles(id) ON DELETE CASCADE,
  tag        VARCHAR(100) NOT NULL,
  PRIMARY KEY (article_id, tag)
);
CREATE INDEX idx_article_tags_tag ON article_tags(tag);

-- Clean, indexed query
SELECT a.* FROM articles a
JOIN article_tags t ON t.article_id = a.id
WHERE t.tag = 'postgresql';

-- Or use PostgreSQL arrays with GIN index for acceptable performance
CREATE TABLE articles (
  id   UUID PRIMARY KEY,
  tags TEXT[]
);
CREATE INDEX idx_articles_tags ON articles USING GIN(tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];

Anti-Pattern 3: Using Natural Keys as Primary Keys

Natural keys (email addresses, phone numbers, product codes) seem convenient but they change, causing cascading updates across every foreign key that references them.

-- ANTI-PATTERN: Email as primary key
CREATE TABLE users (
  email VARCHAR(255) PRIMARY KEY,
  name  VARCHAR(100)
);
CREATE TABLE orders (
  user_email VARCHAR(255) REFERENCES users(email)  -- What if email changes?
);

-- User changes email → must update every orders row
UPDATE users SET email = 'new@example.com' WHERE email = 'old@example.com';
-- ON UPDATE CASCADE runs... across potentially millions of order rows

-- CORRECT: Surrogate key as primary, natural key as unique constraint
CREATE TABLE users (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email      VARCHAR(255) UNIQUE NOT NULL,  -- Still enforced unique
  name       VARCHAR(100)
);
CREATE TABLE orders (
  user_id UUID REFERENCES users(id)  -- Stable forever
);

Anti-Pattern 4: Missing or Wrong Indexes

Two equally bad mistakes: no indexes at all, and indexes on every column.

-- Missing: Queries on foreign keys without indexes
-- Every JOIN on orders.user_id scans the entire orders table
CREATE TABLE orders (user_id UUID);
-- FIX:
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Redundant: Single-column indexes when a composite exists
CREATE INDEX idx_a ON orders(user_id);
CREATE INDEX idx_b ON orders(user_id, created_at);  -- idx_a is now redundant

-- Wrong order in composite index
-- This index helps "WHERE status = ? AND created_at > ?" but NOT "WHERE created_at > ?"
CREATE INDEX idx_wrong ON orders(created_at, status);
-- This index helps both queries:
CREATE INDEX idx_correct ON orders(status, created_at);

Run EXPLAIN ANALYZE regularly on your most frequent queries and review pg_stat_user_indexes for indexes that are never used.

-- Find unused indexes (run after a week of production traffic)
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan < 10
ORDER BY idx_scan;

Anti-Pattern 5: God Table

A single enormous table that stores dozens of loosely related columns violates Single Responsibility and creates maintenance nightmares.

-- ANTI-PATTERN: God table
CREATE TABLE users (
  id, email, password_hash,
  -- Profile
  bio, avatar_url, website,
  -- Subscription
  tier, stripe_customer_id, trial_ends_at, subscription_status,
  -- Preferences
  notification_email, notification_push, theme, language,
  -- Stats (denormalized)
  total_projects, total_collaborators, last_active_at
  -- ... 40 more columns
);

-- CORRECT: Decompose by concern
CREATE TABLE users          (id, email, password_hash, created_at);
CREATE TABLE user_profiles  (user_id PK FK, bio, avatar_url, website);
CREATE TABLE subscriptions  (user_id PK FK, tier, stripe_customer_id, status);
CREATE TABLE user_settings  (user_id PK FK, theme, language, notifications JSONB);

Spot Anti-Patterns Early with Erdmates

The best time to catch these mistakes is before any data exists. Erdmates gives you a visual ERD editor where you can model your schema, share it with your team, and get feedback — making it easy to spot EAV misuse, missing junction tables, and god tables before they reach production.

Conclusion

Database design anti-patterns are easy to introduce and expensive to remove. Avoid EAV tables in favor of JSONB, use surrogate keys, normalize your data with junction tables, size your indexes carefully, and decompose god tables into focused entities. Investing in a clean schema design from the start saves enormous pain as your data grows.