Database Design Patterns for Modern Applications

Database design is the foundation of every successful application. Poor database design leads to performance issues, data inconsistencies, and maintenance nightmares. Let's explore proven patterns and best practices for modern database architecture.

Why Database Design Matters

The impact of good database design:

- Performance - Optimized queries and efficient data access

- Scalability - Handle millions of records without degradation

- Data Integrity - Prevent inconsistencies and anomalies

- Maintainability - Easy to evolve with business requirements

- Cost Efficiency - Reduce storage and compute costs

Relational vs Document Databases

Choose the right database for your use case.

PostgreSQL (Relational)

Best for:

- Complex relationships and joins

- ACID transactions

- Data integrity constraints

- Structured data with fixed schema

- Financial applications

query.sql


-- Strong relationships and constraints
class="keyword">CREATE class="keyword">TABLE users(
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE class="keyword">NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

class="keyword">CREATE class="keyword">TABLE orders(
  id SERIAL PRIMARY KEY,
  user_id INTEGER class="keyword">NOT NULL REFERENCES users(id) class="keyword">ON class="keyword">DELETE CASCADE,
  total DECIMAL(10,2) class="keyword">NOT NULL,
  status VARCHAR(50) class="keyword">NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

class="keyword">CREATE INDEX idx_orders_user_id class="keyword">ON orders(user_id);
class="keyword">CREATE INDEX idx_orders_status class="keyword">ON orders(status);

MongoDB (Document)

Best for:

- Flexible, evolving schemas

- Nested/hierarchical data

- High write throughput

- Rapid prototyping

- Content management systems

script.js


class="keyword">class="comment">// Flexible document structure
{
  _id: ObjectId(class="keyword">class="string">"..."),
  email: class="keyword">class="string">"user@example.com",
  profile: {
    name: class="keyword">class="string">"John Doe",
    bio: class="keyword">class="string">"Full-stack developer",
    avatar: class="keyword">class="string">"https:class="keyword">class="comment">//..."
  },
  orders: [
    {
      orderId: class="keyword">class="string">"ORD-001",
      total: 99.99,
      items: [
        { productId: class="keyword">class="string">"P123", quantity: 2, price: 49.99 }
      ],
      status: class="keyword">class="string">"completed",
      createdAt: ISODate(class="keyword">class="string">"2025-12-01T10:00:00Z")
    }
  ],
  createdAt: ISODate(class="keyword">class="string">"2025-01-01T00:00:00Z")
}

Normalization Strategies

First Normal Form (1NF)

Eliminate repeating groups.

query.sql


-- ❌ Bad: Repeating columns
class="keyword">CREATE class="keyword">TABLE users(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  phone1 VARCHAR(20),
  phone2 VARCHAR(20),
  phone3 VARCHAR(20)
);

-- ✅ Good: Separate table
class="keyword">CREATE class="keyword">TABLE users(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255)
);

class="keyword">CREATE class="keyword">TABLE phone_numbers(
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  phone VARCHAR(20),
  class="keyword">type VARCHAR(20) -- class="keyword">class="string">'mobile', class="keyword">class="string">'work', class="keyword">class="string">'home'
);

Second Normal Form (2NF)

Remove partial dependencies.

query.sql


-- ❌ Bad: Course name depends only on course_id
class="keyword">CREATE class="keyword">TABLE enrollments(
  student_id INTEGER,
  course_id INTEGER,
  course_name VARCHAR(255),
  enrollment_date DATE,
  PRIMARY KEY(student_id, course_id)
);

-- ✅ Good: Separate course table
class="keyword">CREATE class="keyword">TABLE courses(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) class="keyword">NOT NULL
);

class="keyword">CREATE class="keyword">TABLE enrollments(
  student_id INTEGER,
  course_id INTEGER REFERENCES courses(id),
  enrollment_date DATE,
  PRIMARY KEY(student_id, course_id)
);

Third Normal Form (3NF)

Eliminate transitive dependencies.

query.sql


-- ❌ Bad: City and country depend on postal_code
class="keyword">CREATE class="keyword">TABLE addresses(
  id SERIAL PRIMARY KEY,
  street VARCHAR(255),
  postal_code VARCHAR(10),
  city VARCHAR(100),
  country VARCHAR(100)
);

-- ✅ Good: Separate location reference
class="keyword">CREATE class="keyword">TABLE postal_codes(
  code VARCHAR(10) PRIMARY KEY,
  city VARCHAR(100),
  country VARCHAR(100)
);

class="keyword">CREATE class="keyword">TABLE addresses(
  id SERIAL PRIMARY KEY,
  street VARCHAR(255),
  postal_code VARCHAR(10) REFERENCES postal_codes(code)
);

Common Design Patterns

One-to-Many Relationship

query.sql


-- Blog posts with comments
class="keyword">CREATE class="keyword">TABLE posts(
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) class="keyword">NOT NULL,
  content TEXT,
  author_id INTEGER class="keyword">NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

class="keyword">CREATE class="keyword">TABLE comments(
  id SERIAL PRIMARY KEY,
  post_id INTEGER class="keyword">NOT NULL REFERENCES posts(id) class="keyword">ON class="keyword">DELETE CASCADE,
  content TEXT class="keyword">NOT NULL,
  author_id INTEGER class="keyword">NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

class="keyword">CREATE INDEX idx_comments_post_id class="keyword">ON comments(post_id);

Many-to-Many Relationship

query.sql


-- Students and courses
class="keyword">CREATE class="keyword">TABLE students(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) class="keyword">NOT NULL,
  email VARCHAR(255) UNIQUE class="keyword">NOT NULL
);

class="keyword">CREATE class="keyword">TABLE courses(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) class="keyword">NOT NULL,
  code VARCHAR(20) UNIQUE class="keyword">NOT NULL
);

-- Junction table
class="keyword">CREATE class="keyword">TABLE student_courses(
  student_id INTEGER REFERENCES students(id) class="keyword">ON class="keyword">DELETE CASCADE,
  course_id INTEGER REFERENCES courses(id) class="keyword">ON class="keyword">DELETE CASCADE,
  enrolled_at TIMESTAMP DEFAULT NOW(),
  grade VARCHAR(2),
  PRIMARY KEY(student_id, course_id)
);

class="keyword">CREATE INDEX idx_student_courses_student class="keyword">ON student_courses(student_id);
class="keyword">CREATE INDEX idx_student_courses_course class="keyword">ON student_courses(course_id);

Polymorphic Associations

query.sql


-- Comments can belong to posts, photos, or videos
class="keyword">CREATE class="keyword">TABLE posts(
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  content TEXT
);

class="keyword">CREATE class="keyword">TABLE photos(
  id SERIAL PRIMARY KEY,
  url VARCHAR(500),
  caption TEXT
);

class="keyword">CREATE class="keyword">TABLE comments(
  id SERIAL PRIMARY KEY,
  content TEXT class="keyword">NOT NULL,
  commentable_type VARCHAR(50) class="keyword">NOT NULL, -- class="keyword">class="string">'post', class="keyword">class="string">'photo', class="keyword">class="string">'video'
  commentable_id INTEGER class="keyword">NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

class="keyword">CREATE INDEX idx_comments_polymorphic class="keyword">ON comments(commentable_type, commentable_id);

Self-Referencing (Hierarchical Data)

query.sql


-- Organizational hierarchy
class="keyword">CREATE class="keyword">TABLE employees(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) class="keyword">NOT NULL,
  manager_id INTEGER REFERENCES employees(id),
  department VARCHAR(100)
);

class="keyword">CREATE INDEX idx_employees_manager class="keyword">ON employees(manager_id);

-- Query hierarchy
WITH RECURSIVE employee_hierarchy class="keyword">AS (
  -- Anchor: Top-level managers
  class="keyword">SELECT id, name, manager_id, 0 class="keyword">AS level
  class="keyword">FROM employees
  class="keyword">WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: Direct reports
  class="keyword">SELECT e.id, e.name, e.manager_id, eh.level + 1
  class="keyword">FROM employees e
  class="keyword">JOIN employee_hierarchy eh class="keyword">ON e.manager_id = eh.id
)
class="keyword">SELECT * class="keyword">FROM employee_hierarchy class="keyword">ORDER class="keyword">BY level, name;

Soft Deletes Pattern

Preserve data instead of hard deletes.

query.sql


class="keyword">CREATE class="keyword">TABLE products(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) class="keyword">NOT NULL,
  price DECIMAL(10,2) class="keyword">NOT NULL,
  deleted_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Only active products
class="keyword">CREATE VIEW active_products class="keyword">AS
class="keyword">SELECT * class="keyword">FROM products
class="keyword">WHERE deleted_at IS NULL;

-- Soft delete
class="keyword">UPDATE products SET deleted_at = NOW() class="keyword">WHERE id = 1;

-- Restore
class="keyword">UPDATE products SET deleted_at = NULL class="keyword">WHERE id = 1;

Audit Trail Pattern

Track all changes for compliance.

query.sql


class="keyword">CREATE class="keyword">TABLE audit_logs(
  id SERIAL PRIMARY KEY,
  table_name VARCHAR(100) class="keyword">NOT NULL,
  record_id INTEGER class="keyword">NOT NULL,
  action VARCHAR(20) class="keyword">NOT NULL, -- class="keyword">class="string">'class="keyword">INSERT', class="keyword">class="string">'class="keyword">UPDATE', class="keyword">class="string">'class="keyword">DELETE'
  old_values JSONB,
  new_values JSONB,
  user_id INTEGER,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Trigger class="keyword">class="keyword">function
class="keyword">CREATE class="keyword">OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER class="keyword">AS $$
BEGIN
  IF(TG_OP = class="keyword">class="string">'class="keyword">DELETE') THEN
    class="keyword">INSERT INTO audit_logs(table_name, record_id, action, old_values)
    VALUES(TG_TABLE_NAME, OLD.id, class="keyword">class="string">'class="keyword">DELETE', row_to_json(OLD));
    RETURN OLD;
  ELSIF(TG_OP = class="keyword">class="string">'class="keyword">UPDATE') THEN
    class="keyword">INSERT INTO audit_logs(table_name, record_id, action, old_values, new_values)
    VALUES(TG_TABLE_NAME, NEW.id, class="keyword">class="string">'class="keyword">UPDATE', row_to_json(OLD), row_to_json(NEW));
    RETURN NEW;
  ELSIF(TG_OP = class="keyword">class="string">'class="keyword">INSERT') THEN
    class="keyword">INSERT INTO audit_logs(table_name, record_id, action, new_values)
    VALUES(TG_TABLE_NAME, NEW.id, class="keyword">class="string">'class="keyword">INSERT', row_to_json(NEW));
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Attach trigger
class="keyword">CREATE TRIGGER audit_users
AFTER class="keyword">INSERT class="keyword">OR class="keyword">UPDATE class="keyword">OR class="keyword">DELETE class="keyword">ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

Partitioning for Scale

Handle large tables efficiently.

Range Partitioning

query.sql


-- Partition by date
class="keyword">CREATE class="keyword">TABLE events(
  id BIGSERIAL,
  event_type VARCHAR(50),
  data JSONB,
  created_at TIMESTAMP class="keyword">NOT NULL
) PARTITION class="keyword">BY RANGE(created_at);

-- Monthly partitions
class="keyword">CREATE class="keyword">TABLE events_2025_01 PARTITION OF events
  FOR VALUES class="keyword">FROM (class="keyword">class="string">'2025-01-01') TO(class="keyword">class="string">'2025-02-01');

class="keyword">CREATE class="keyword">TABLE events_2025_02 PARTITION OF events
  FOR VALUES class="keyword">FROM (class="keyword">class="string">'2025-02-01') TO(class="keyword">class="string">'2025-03-01');

-- Index on each partition
class="keyword">CREATE INDEX idx_events_2025_01_created class="keyword">ON events_2025_01(created_at);
class="keyword">CREATE INDEX idx_events_2025_02_created class="keyword">ON events_2025_02(created_at);

Hash Partitioning

query.sql


-- Partition by user_id hash
class="keyword">CREATE class="keyword">TABLE user_actions(
  id BIGSERIAL,
  user_id INTEGER class="keyword">NOT NULL,
  action VARCHAR(50),
  created_at TIMESTAMP DEFAULT NOW()
) PARTITION class="keyword">BY HASH(user_id);

-- Create 4 partitions
class="keyword">CREATE class="keyword">TABLE user_actions_0 PARTITION OF user_actions
  FOR VALUES WITH(MODULUS 4, REMAINDER 0);

class="keyword">CREATE class="keyword">TABLE user_actions_1 PARTITION OF user_actions
  FOR VALUES WITH(MODULUS 4, REMAINDER 1);

class="keyword">CREATE class="keyword">TABLE user_actions_2 PARTITION OF user_actions
  FOR VALUES WITH(MODULUS 4, REMAINDER 2);

class="keyword">CREATE class="keyword">TABLE user_actions_3 PARTITION OF user_actions
  FOR VALUES WITH(MODULUS 4, REMAINDER 3);

Indexing Strategies

Choose the right indexes for performance.

B-Tree Indexes (Default)

query.sql


-- Single column
class="keyword">CREATE INDEX idx_users_email class="keyword">ON users(email);

-- Composite index(order matters!)
class="keyword">CREATE INDEX idx_orders_user_status class="keyword">ON orders(user_id, status);

-- Query benefits class="keyword">from class="keyword">this index
class="keyword">SELECT * class="keyword">FROM orders class="keyword">WHERE user_id = 1 class="keyword">AND status = class="keyword">class="string">'completed';

Partial Indexes

query.sql


-- Index only active users
class="keyword">CREATE INDEX idx_active_users class="keyword">ON users(email)
class="keyword">WHERE deleted_at IS NULL;

-- Index only pending orders
class="keyword">CREATE INDEX idx_pending_orders class="keyword">ON orders(created_at)
class="keyword">WHERE status = class="keyword">class="string">'pending';

Expression Indexes

query.sql


-- Index class="keyword">class="keyword">for class="keyword">case-insensitive searches
class="keyword">CREATE INDEX idx_users_email_lower class="keyword">ON users(LOWER(email));

-- Query uses the index
class="keyword">SELECT * class="keyword">FROM users class="keyword">WHERE LOWER(email) = class="keyword">class="string">'john@example.com';

Full-Text Search

query.sql


-- Add tsvector column
class="keyword">ALTER class="keyword">TABLE posts ADD COLUMN search_vector tsvector;

-- Update with trigger
class="keyword">CREATE FUNCTION posts_search_trigger() RETURNS trigger class="keyword">AS $$
BEGIN
  NEW.search_vector := to_tsvector(class="keyword">class="string">'english', COALESCE(NEW.title, class="keyword">class="string">'') || class="keyword">class="string">' ' || COALESCE(NEW.content, class="keyword">class="string">''));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

class="keyword">CREATE TRIGGER tsvector_update BEFORE class="keyword">INSERT class="keyword">OR class="keyword">UPDATE
class="keyword">ON posts FOR EACH ROW EXECUTE FUNCTION posts_search_trigger();

-- Create GIN index
class="keyword">CREATE INDEX idx_posts_search class="keyword">ON posts USING GIN(search_vector);

-- Search
class="keyword">SELECT * class="keyword">FROM posts
class="keyword">WHERE search_vector @@ to_tsquery(class="keyword">class="string">'english', class="keyword">class="string">'postgresql & database');

Caching Strategies

Materialized Views

query.sql


-- Pre-computed aggregations
class="keyword">CREATE MATERIALIZED VIEW user_statistics class="keyword">AS
class="keyword">SELECT
  u.id,
  u.name,
  COUNT(o.id) class="keyword">AS total_orders,
  SUM(o.total) class="keyword">AS total_spent,
  MAX(o.created_at) class="keyword">AS last_order_date
class="keyword">FROM users u
class="keyword">LEFT class="keyword">JOIN orders o class="keyword">ON u.id = o.user_id
class="keyword">GROUP class="keyword">BY u.id, u.name;

class="keyword">CREATE INDEX idx_user_stats_id class="keyword">ON user_statistics(id);

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY user_statistics;

Redis Caching Pattern

script.js


class="keyword">import { Redis } class="keyword">from class="keyword">class="string">"ioredis";
class="keyword">import { prisma } class="keyword">from class="keyword">class="string">"./prisma";

class="keyword">class="keyword">const redis = class="keyword">new Redis(process.env.REDIS_URL);

class="keyword">class="keyword">async class="keyword">class="keyword">function getUserById(id: string) {
  class="keyword">class="keyword">const cacheKey = class="keyword">class="string">user:${id};

  class="keyword">class="comment">// Check cache
  class="keyword">class="keyword">const cached = class="keyword">class="keyword">await redis.get(cacheKey);
  class="keyword">class="keyword">if (cached) {
    class="keyword">class="keyword">return JSON.parse(cached);
  }

  class="keyword">class="comment">// Fetch class="keyword">from database
  class="keyword">class="keyword">const user = class="keyword">class="keyword">await prisma.user.findUnique({
    where: { id },
  });

  class="keyword">class="keyword">if (user) {
    class="keyword">class="comment">// Cache class="keyword">class="keyword">for 1 hour
    class="keyword">class="keyword">await redis.setex(cacheKey, 3600, JSON.stringify(user));
  }

  class="keyword">class="keyword">return user;
}

class="keyword">class="comment">// Invalidate on update
class="keyword">class="keyword">async class="keyword">class="keyword">function updateUser(id: string, data: any) {
  class="keyword">class="keyword">const user = class="keyword">class="keyword">await prisma.user.update({
    where: { id },
    data,
  });

  class="keyword">class="comment">// Invalidate cache
  class="keyword">class="keyword">await redis.del(class="keyword">class="string">user:${id});

  class="keyword">class="keyword">return user;
}

Data Integrity Constraints

Enforce business rules at the database level.

query.sql


-- Check constraints
class="keyword">CREATE class="keyword">TABLE products(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) class="keyword">NOT NULL,
  price DECIMAL(10,2) class="keyword">NOT NULL CHECK(price >= 0),
  stock INTEGER class="keyword">NOT NULL CHECK(stock >= 0),
  discount_percent INTEGER CHECK(discount_percent BETWEEN 0 class="keyword">AND 100)
);

-- Unique constraints
class="keyword">CREATE class="keyword">TABLE users(
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE class="keyword">NOT NULL,
  username VARCHAR(50) UNIQUE class="keyword">NOT NULL
);

-- Composite unique
class="keyword">CREATE class="keyword">TABLE product_reviews(
  id SERIAL PRIMARY KEY,
  product_id INTEGER class="keyword">NOT NULL,
  user_id INTEGER class="keyword">NOT NULL,
  rating INTEGER CHECK(rating BETWEEN 1 class="keyword">AND 5),
  UNIQUE(product_id, user_id) -- One review per user per product
);

-- Foreign key with cascade
class="keyword">CREATE class="keyword">TABLE order_items(
  id SERIAL PRIMARY KEY,
  order_id INTEGER class="keyword">NOT NULL REFERENCES orders(id) class="keyword">ON class="keyword">DELETE CASCADE,
  product_id INTEGER class="keyword">NOT NULL REFERENCES products(id) class="keyword">ON class="keyword">DELETE RESTRICT,
  quantity INTEGER class="keyword">NOT NULL CHECK(quantity > 0)
);

Performance Best Practices

1. Use EXPLAIN ANALYZE

query.sql


EXPLAIN ANALYZE
class="keyword">SELECT * class="keyword">FROM orders
class="keyword">WHERE user_id = 1 class="keyword">AND status = class="keyword">class="string">'pending';

2. Avoid N+1 Queries

script.js


class="keyword">class="comment">// ❌ Bad: N+1 queries
class="keyword">class="keyword">const users = class="keyword">class="keyword">await prisma.user.findMany();
class="keyword">class="keyword">for (class="keyword">class="keyword">const user of users) {
  class="keyword">class="keyword">const posts = class="keyword">class="keyword">await prisma.post.findMany({
    where: { authorId: user.id },
  });
}

class="keyword">class="comment">// ✅ Good: Single query with include
class="keyword">class="keyword">const users = class="keyword">class="keyword">await prisma.user.findMany({
  include: { posts: true },
});

3. Use Connection Pooling

script.js


class="keyword">import { Pool } class="keyword">from class="keyword">class="string">"pg";

class="keyword">class="keyword">const pool = class="keyword">new Pool({
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

4. Batch Operations

script.js


class="keyword">class="comment">// ✅ Batch insert
class="keyword">class="keyword">await prisma.user.createMany({
  data: [
    { email: class="keyword">class="string">"user1@example.com", name: class="keyword">class="string">"User 1" },
    { email: class="keyword">class="string">"user2@example.com", name: class="keyword">class="string">"User 2" },
    { email: class="keyword">class="string">"user3@example.com", name: class="keyword">class="string">"User 3" },
  ],
});

Conclusion

Effective database design requires understanding normalization, choosing appropriate patterns, implementing proper indexing, and maintaining data integrity. Start with a solid foundation, optimize iteratively, and always consider your application's specific requirements.

Remember: premature optimization is the root of all evil, but proper database design from the start saves countless hours of refactoring later.

Happy designing! 🗄️