Database Design Fundamentals — Building Scalable Data Systems

Introduction: The Foundation of Reliable Applications

Database design is the foundation of every scalable application. Poor database design leads to performance issues, data inconsistencies, and maintenance nightmares. Good design, on the other hand, ensures your application can scale, perform efficiently, and maintain data integrity.

This comprehensive guide covers everything you need to understand and implement solid database design principles.

Table of Contents

• Why Database Design Matters
• Core Concepts
• Normalization
• Primary Keys & Foreign Keys
• Indexing
• Query Optimization
• Database Types
• Schema Design Best Practices
• Handling Scale
• Migration Strategies
• Conclusion

Why Database Design Matters

Good database design provides:

• Data Integrity: Ensures accuracy and consistency
• Performance: Optimized queries and efficient storage
• Scalability: Handles growth without rebuilding
• Maintainability: Easier to modify and extend
• Efficiency: Reduced storage and faster queries

Core Concepts

Understanding foundational database concepts is essential before moving to more complex design principles.

Data Types

-- String types
VARCHAR(255)    -- Variable length string
TEXT            -- Large text
CHAR(50)        -- Fixed length string

-- Numeric types
INTEGER         -- Whole numbers
DECIMAL(10, 2)  -- Decimal with precision
FLOAT           -- Floating point numbers
BOOLEAN         -- True/False

-- Date/Time
DATE            -- Date only
TIMESTAMP       -- Date and time with timezone
TIME            -- Time only

-- Other
JSON            -- JSON data type
UUID            -- Unique identifier
ENUM            -- Fixed set of values

Entity Relationship Diagram (ERD)

Entity Relationship Diagrams visually represent the structure of your database, showing tables, their attributes, and the relationships between them.

User (1) ----< (Many) Post
  ├─ id (PK)
  ├─ name
  ├─ email (UNIQUE)
  └─ created_at

Post
  ├─ id (PK)
  ├─ user_id (FK)
  ├─ title
  ├─ content
  └─ created_at

Normalization

Normalization is a systematic process of organizing data into well-structured tables, eliminating redundancy and improving data integrity. It follows normal forms (1NF, 2NF, 3NF) that progressively enforce stricter rules.

First Normal Form (1NF)

Each field contains only atomic (indivisible) values. No repeating groups.

-- ❌ Bad - Violates 1NF (repeating groups)
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  phone_numbers VARCHAR(100) -- "123-456-7890, 098-765-4321"
);

-- ✅ Good - Follows 1NF
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE user_phone (
  user_id INT FOREIGN KEY REFERENCES users(id),
  phone_number VARCHAR(20),
  PRIMARY KEY (user_id, phone_number)
);

Second Normal Form (2NF)

Builds on 1NF by ensuring no partial dependencies exist on composite keys. All non-key attributes must depend on the entire primary key.

-- ❌ Bad - Partial dependency (instructor_office depends only on instructor_id)
CREATE TABLE course_schedule (
  course_id INT,
  instructor_id INT,
  instructor_office VARCHAR(100),
  meeting_time TIME,
  PRIMARY KEY (course_id, instructor_id)
);

-- ✅ Good - 2NF compliant
CREATE TABLE instructors (
  instructor_id INT PRIMARY KEY,
  name VARCHAR(100),
  office VARCHAR(100)
);

CREATE TABLE course_schedule (
  course_id INT,
  instructor_id INT FOREIGN KEY REFERENCES instructors(id),
  meeting_time TIME,
  PRIMARY KEY (course_id, instructor_id)
);

Third Normal Form (3NF)

Builds on 2NF by eliminating transitive dependencies. Non-key attributes must depend directly on the primary key, not on other non-key attributes.

-- ❌ Bad - Transitive dependency (city/state depend on country, not directly on id)
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(100),
  country VARCHAR(50),
  country_code VARCHAR(2)
);

-- ✅ Good - 3NF compliant
CREATE TABLE countries (
  country_id INT PRIMARY KEY,
  name VARCHAR(50),
  code VARCHAR(2)
);

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(100),
  country_id INT FOREIGN KEY REFERENCES countries(country_id)
);

Primary Keys & Foreign Keys

Primary keys and foreign keys form the foundation of relational database integrity, establishing unique identification and relationships between tables.

Primary Keys

Uniquely identify each row in a table.

-- Simple primary key
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(100) UNIQUE NOT NULL,
  name VARCHAR(100)
);

-- Composite primary key
CREATE TABLE user_roles (
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  assigned_at TIMESTAMP,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- UUID as primary key
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title VARCHAR(255) NOT NULL,
  content TEXT
);

Foreign Keys & Relationships

Foreign keys create relationships between tables, enforcing referential integrity and enabling queries across related data.

-- One-to-Many: User has many Posts
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE posts (
  id INT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(255),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Many-to-Many: User has many Roles, Role has many Users
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE roles (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE user_roles (
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

Indexing

Indexes are data structures that dramatically improve query performance by creating efficient lookup paths, though they require maintenance overhead.

Types of Indexes

-- Single column index
CREATE INDEX idx_user_email ON users(email);

-- Composite index (for queries on multiple columns)
CREATE INDEX idx_post_user_date ON posts(user_id, created_at);

-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_user_email ON users(email);

-- Full-text index (for text search)
CREATE FULLTEXT INDEX idx_post_content ON posts(title, content);

-- Partial index (for specific conditions)
CREATE INDEX idx_active_users ON users(id) WHERE status = 'active';

Index Design Best Practices

Strategic index design balances query performance gains against storage and write overhead.

-- Index columns frequently used in WHERE clauses
-- Index columns used in JOIN conditions
-- Index columns used in ORDER BY

-- Good indexes
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_posts_published ON posts(published_at) WHERE published = true;

-- Avoid excessive indexes
-- Index large columns (text, varchar)
-- Keep indexes maintained (periodic rebuilds)

-- Check index usage
SELECT * FROM pg_stat_user_indexes ORDER BY idx_scan DESC;

Query Optimization

Execution Plans

-- Analyze query performance
EXPLAIN ANALYZE
SELECT p.title, u.name
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.created_at > '2025-01-01';

-- Check for sequential scans (bad) vs index scans (good)

Common Optimization Patterns

-- ❌ N+1 Problem: Multiple queries instead of one
-- SELECT * FROM users; -- Then loop and query each user's posts

-- ✅ Use JOIN instead
SELECT u.*, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

-- ❌ SELECT * fetches unnecessary columns
SELECT * FROM users;

-- ✅ Select specific columns
SELECT id, name, email FROM users;

-- ❌ Subqueries in SELECT clause
SELECT *, (SELECT COUNT(*) FROM posts WHERE user_id = users.id) FROM users;

-- ✅ Use aggregation with JOIN
SELECT u.*, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

Database Types

SQL (Relational)

Best for: Structured data, complex queries, ACID transactions

-- PostgreSQL example
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT REFERENCES users(id),
  total DECIMAL(10, 2),
  status VARCHAR(20),
  created_at TIMESTAMP
);

Popular: PostgreSQL, MySQL, MariaDB, SQL Server

NoSQL (Document)

Best for: Flexible schemas, hierarchical data, high volume

// MongoDB example
db.users.insertOne({
  _id: ObjectId(),
  name: "John",
  email: "[email protected]",
  posts: [
    { title: "First Post", content: "..." },
    { title: "Second Post", content: "..." }
  ]
});

Popular: MongoDB, Firebase, DynamoDB

Graph Database

Best for: Relationship-heavy data, recommendation systems

// Neo4j example
CREATE (user:User { name: "John" })
CREATE (post:Post { title: "My Post" })
CREATE (user)-[:WROTE]->(post)

Popular: Neo4j, Amazon Neptune

Search Database

Best for: Full-text search, analytics

Popular: Elasticsearch, Apache Solr, MeiliSearch

Schema Design Best Practices

Naming Conventions

-- Tables: plural, snake_case
CREATE TABLE users;
CREATE TABLE user_posts;

-- Columns: snake_case
CREATE TABLE users (
  user_id INT,
  first_name VARCHAR(100),
  created_at TIMESTAMP
);

-- Primary key: {table}_{id} or just {id}
CREATE TABLE users (
  user_id INT PRIMARY KEY
);

-- Foreign key: {table}_{id}
CREATE TABLE posts (
  post_id INT PRIMARY KEY,
  user_id INT REFERENCES users(user_id)
);

Timestamps

-- Always include timestamps
CREATE TABLE posts (
  id INT PRIMARY KEY,
  title VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Soft Deletes

-- Use soft deletes for audit trail
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  deleted_at TIMESTAMP NULL
);

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

Handling Scale

Partitioning

-- Range partitioning by date
CREATE TABLE orders (
  id INT,
  user_id INT,
  amount DECIMAL,
  order_date DATE
) PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026)
);

Replication

Master DB (writes)
  ↓
Read Replicas (read-only copies)
  - Slave 1
  - Slave 2
  - Slave 3

Sharding

Shard 1: Users A-M
Shard 2: Users N-Z

Route requests based on user ID

Migration Strategies

Safe Migrations

-- Add new column with default
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- Back-fill data
UPDATE users SET status = 'active' WHERE status IS NULL;

-- Add NOT NULL constraint
ALTER TABLE users MODIFY status VARCHAR(20) NOT NULL;

-- Remove old column (after verification)
ALTER TABLE users DROP COLUMN old_column;

Conclusion

Good database design is critical for building scalable, maintainable applications. By mastering normalization, indexing, query optimization, and choosing the right database type, you can build systems that perform well and scale with your business needs. Remember: database design decisions made early are expensive to change later, so invest time upfront to get it right.