Database Design Technical.
Interview cheat sheet.

Database design encompasses the principles and practices of structuring data for efficient storage, retrieval, and management. Key concepts include relational database design with normalization, SQL proficiency, indexing strategies, transaction management (ACID properties), and performance optimization. Essential for system design interviews covering scalability, data modeling, and choosing between SQL vs NoSQL solutions. Understanding includes entity-relationship modeling, query optimization, concurrency control, and distributed database concepts like CAP theorem.

What is a Database?

• Definition: Organized collection of structured data
• DBMS: Database Management System - software to manage databases
• Purpose: Store, retrieve, and manage data efficiently

Types of Databases

1. Relational (SQL): Tables with rows and columns
   • Examples: MySQL, PostgreSQL, Oracle, SQL Server
2. NoSQL: Non-relational, flexible schemas
   • Document: MongoDB, CouchDB
   • Key-Value: Redis, DynamoDB
   • Column-Family: Cassandra, HBase
   • Graph: Neo4j, Amazon Neptune

Tables and Schema

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Keys

• Primary Key: Unique identifier for each row
• Foreign Key: References primary key in another table
• Composite Key: Multiple columns as primary key
• Candidate Key: Could be primary key
• Super Key: Set of attributes that uniquely identifies rows

Relationships

1. One-to-One: User ↔ Profile
2. One-to-Many: User → Posts
3. Many-to-Many: Students ↔ Courses (requires junction table)

-- Junction table for many-to-many
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Normal Forms

1. 1NF: Atomic values, no repeating groups
2. 2NF: 1NF + no partial dependencies
3. 3NF: 2NF + no transitive dependencies
4. BCNF: 3NF + every determinant is a candidate key

Example: Denormalized → 3NF

-- Denormalized
Orders: OrderID, CustomerName, CustomerAddress, ProductName, ProductPrice

-- 3NF
Customers: CustomerID, CustomerName, CustomerAddress
Products: ProductID, ProductName, ProductPrice
Orders: OrderID, CustomerID, ProductID, Quantity

Benefits vs Drawbacks

• Benefits: Less redundancy, data integrity, easier updates
• Drawbacks: More joins, potentially slower reads

Basic Operations

-- SELECT with JOIN
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
HAVING COUNT(p.id) > 5
ORDER BY post_count DESC
LIMIT 10;

-- INSERT
INSERT INTO users (name, email) VALUES ('John', 'john@email.com');

-- UPDATE
UPDATE users SET email = 'newemail@email.com' WHERE id = 1;

-- DELETE
DELETE FROM users WHERE id = 1;

Join Types

• INNER JOIN: Only matching records
• LEFT JOIN: All from left table
• RIGHT JOIN: All from right table
• FULL OUTER JOIN: All from both tables
• CROSS JOIN: Cartesian product

Advanced SQL

-- Window Functions
SELECT name, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rank
FROM employees;

-- CTE (Common Table Expression)
WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 100000
)
SELECT dept, AVG(salary) FROM high_earners GROUP BY dept;

-- Subqueries
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Types

1. B-Tree: Default, good for range queries
2. Hash: Exact match queries only
3. Bitmap: Low cardinality columns
4. Full-text: Text search

Best Practices

-- Create index
CREATE INDEX idx_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_user_created ON posts(user_id, created_at);

-- Unique index
CREATE UNIQUE INDEX idx_unique_email ON users(email);

When to Use

• Columns in WHERE, JOIN, ORDER BY
• High selectivity columns
• Foreign keys

When to Avoid

• Small tables
• Frequently updated columns
• Low selectivity columns

ACID Properties

• Atomicity: All or nothing
• Consistency: Valid state to valid state
• Isolation: Concurrent transactions don't interfere
• Durability: Committed data persists

Transaction Example

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- or ROLLBACK if error

Isolation Levels

1. Read Uncommitted: Dirty reads possible
2. Read Committed: No dirty reads
3. Repeatable Read: No phantom reads
4. Serializable: Highest isolation

Design Principles

1. Start with ERD: Entity-Relationship Diagram
2. Choose appropriate data types: INT vs BIGINT, VARCHAR vs TEXT
3. Use constraints: NOT NULL, CHECK, DEFAULT
4. Implement proper indexing strategy
5. Consider denormalization for read-heavy workloads

Common Patterns

-- Soft deletes
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;

-- Audit trails
CREATE TABLE user_audit (
    id SERIAL PRIMARY KEY,
    user_id INT,
    action VARCHAR(50),
    changed_by INT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Versioning
CREATE TABLE product_versions (
    id SERIAL PRIMARY KEY,
    product_id INT,
    version INT,
    data JSONB,
    created_at TIMESTAMP
);

Query Optimization

1. Use EXPLAIN: Analyze query execution plan
2. **Avoid SELECT ***: Specify needed columns
3. Limit results: Use LIMIT/OFFSET or pagination
4. Optimize JOINs: Join on indexed columns
5. Use appropriate data types: Smaller is faster

Example Optimization

-- Bad
SELECT * FROM orders o 
JOIN users u ON o.user_email = u.email;

-- Good
SELECT o.id, o.total, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2024-01-01'
LIMIT 100;

Database-Level Optimization

• Partitioning: Split large tables
• Sharding: Distribute data across servers
• Replication: Master-slave for read scaling
• Caching: Redis/Memcached for frequent queries

When to Use NoSQL

• Flexible/changing schema
• Massive scale requirements
• Geographic distribution
• Specific data models (graph, time-series)

CAP Theorem

Choose 2 of 3:

• Consistency: All nodes see same data
• Availability: System remains operational
• Partition Tolerance: System continues despite network failures

MongoDB Example

// Insert
db.users.insertOne({
    name: "John",
    email: "john@email.com",
    tags: ["premium", "active"],
    profile: {
        age: 30,
        city: "NYC"
    }
});

// Query
db.users.find({
    "profile.age": { $gte: 25 },
    tags: "premium"
}).limit(10);

Common Design Challenges

URL Shortener (e.g., bit.ly)

-- Core tables
CREATE TABLE urls (
    id BIGINT PRIMARY KEY,
    original_url TEXT NOT NULL,
    short_code VARCHAR(7) UNIQUE,
    user_id INT,
    created_at TIMESTAMP,
    expires_at TIMESTAMP,
    INDEX idx_short_code (short_code),
    INDEX idx_user_created (user_id, created_at)
);

CREATE TABLE url_stats (
    short_code VARCHAR(7),
    date DATE,
    clicks INT DEFAULT 0,
    PRIMARY KEY (short_code, date)
);

Social Media Feed

-- Pull model (read-time aggregation)
CREATE TABLE posts (
    id BIGINT PRIMARY KEY,
    user_id INT,
    content TEXT,
    created_at TIMESTAMP,
    INDEX idx_user_time (user_id, created_at DESC)
);

CREATE TABLE follows (
    follower_id INT,
    following_id INT,
    created_at TIMESTAMP,
    PRIMARY KEY (follower_id, following_id),
    INDEX idx_following (following_id)
);

-- Push model (write-time fanout)
CREATE TABLE user_feeds (
    user_id INT,
    post_id BIGINT,
    created_at TIMESTAMP,
    PRIMARY KEY (user_id, created_at, post_id)
);

E-commerce Inventory

-- Handle concurrent purchases
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10,2),
    stock_quantity INT,
    version INT DEFAULT 1  -- Optimistic locking
);

-- Reservation system for high-concurrency
CREATE TABLE inventory_reservations (
    id BIGINT PRIMARY KEY,
    product_id INT,
    user_id INT,
    quantity INT,
    expires_at TIMESTAMP,
    status ENUM('pending', 'confirmed', 'expired')
);

Scalability Patterns

Read Replicas

• Master-slave replication for read scaling
• Route writes to master, reads to replicas
• Handle replication lag

Database Sharding

-- Horizontal partitioning strategies
-- 1. Hash-based: user_id % num_shards
-- 2. Range-based: user_id ranges per shard
-- 3. Directory-based: lookup table for shard mapping

-- Example: User sharding by ID
Shard 1: user_id 1-1000000
Shard 2: user_id 1000001-2000000
Shard 3: user_id 2000001-3000000

Caching Strategies

• Cache-Aside: Application manages cache
• Write-Through: Write to cache and DB
• Write-Behind: Write to cache, async DB
• Refresh-Ahead: Proactive cache refresh

Advanced Concepts

Database Locks

• Pessimistic: Lock before read/write
• Optimistic: Check for conflicts at commit
• Deadlock Prevention: Lock ordering, timeouts

Connection Pooling

# Connection pool configuration
pool_size = 20  # Max connections
max_overflow = 10  # Additional connections
pool_timeout = 30  # Wait time for connection
pool_recycle = 3600  # Refresh connections

OLTP vs OLAP

• OLTP: Online Transaction Processing (operational)
• OLAP: Online Analytical Processing (reporting)
• Data Warehouse: ETL from OLTP to OLAP systems

Performance Metrics

• QPS: Queries Per Second
• Latency: p50, p95, p99 response times
• Throughput: Data processed per unit time
• IOPS: Input/Output Operations Per Second
• Connection Pool: Active, idle, waiting connections

Schema Design Anti-Patterns

-- ❌ EAV (Entity-Attribute-Value) Anti-Pattern
CREATE TABLE properties (
    entity_id INT,
    attribute_name VARCHAR(100),
    attribute_value TEXT
);

-- ✅ Better: Proper columns
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10,2),
    category VARCHAR(100)
);

Query Anti-Patterns

-- ❌ N+1 Query Problem
-- Fetching users and their posts separately
for user in users:
    posts = query("SELECT * FROM posts WHERE user_id = ?", user.id)

-- ✅ Better: Single query with JOIN
SELECT u.*, p.* FROM users u 
LEFT JOIN posts p ON u.id = p.user_id;

-- ❌ Implicit ORDER BY with LIMIT
SELECT * FROM large_table LIMIT 10;  -- Unpredictable results

-- ✅ Better: Explicit ordering
SELECT * FROM large_table ORDER BY created_at DESC LIMIT 10;

Performance Anti-Patterns

• Missing Indexes: WHERE, JOIN, ORDER BY columns without indexes
• Over-Indexing: Too many indexes slow down writes
• Function in WHERE: WHERE YEAR(date) = 2024 prevents index usage
• SELECT * Syndrome: Fetching unnecessary columns
• Cursor Loops: Row-by-row processing instead of set operations

Design Mistakes to Avoid

• UUID as Clustering Key: Poor performance due to randomness
• Storing Files in Database: Use file storage + DB references
• Generic Schema: Over-flexible designs that become complex
• No Data Validation: Relying only on application-level validation
• Ignoring Data Growth: Not planning for scale

SQL Order of Execution

1. FROM (including JOINs)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT/OFFSET

Index Selection Rules

Selectivity = Unique Values / Total Rows
High Selectivity (>0.9) → Good for indexing
Low Selectivity (<0.1) → Poor for indexing

Data Type Sizes (MySQL)

• TINYINT: 1 byte (-128 to 127)
• SMALLINT: 2 bytes (-32,768 to 32,767)
• INT: 4 bytes (-2.1B to 2.1B)
• BIGINT: 8 bytes (-9.2E18 to 9.2E18)
• VARCHAR(n): n+1 or n+2 bytes
• TEXT: 2 bytes + actual data

Remember

• Normalize until it hurts, denormalize until it works
• Indexes speed up reads but slow down writes
• Measure before optimizing
• Design for your access patterns