Database Design Best Practices: From Schema to Performance

Introduction

Database design is foundational to application performance and scalability. Poor database design leads to slow queries, data inconsistencies, and scaling challenges. Here's how to design databases right.

Schema Design Principles

1. Normalization

• 1NF: Eliminate duplicate columns
• 2NF: Remove partial dependencies
• 3NF: Remove transitive dependencies

• Read-heavy workloads
• Performance critical queries
• Reporting/analytics

2. Primary Keys

• Use surrogate keys (auto-increment, UUID)
• Keep primary keys simple
• Avoid composite keys when possible
• Use UUIDs for distributed systems

3. Foreign Keys

• Maintain referential integrity
• Prevent orphaned records
• Database-level constraints

• CASCADE: Delete related records
• SET NULL: Set foreign key to null
• RESTRICT: Prevent deletion

Indexing Strategy

Types of Indexes

• Automatically created for primary key
• Unique and clustered

• Created on frequently queried columns
• Improves SELECT performance
• Slows down INSERT/UPDATE

• Multiple columns
• Order matters (leftmost prefix)

• Enforces uniqueness
• Can be on multiple columns

Index Best Practices

• Foreign keys
• Frequently filtered columns
• Columns in WHERE clauses
• Columns in JOIN conditions
• Columns in ORDER BY

• Each index slows writes
• Takes storage space
• Requires maintenance

-- Good: Index on foreign key
CREATE INDEX idx_user_id ON orders(user_id);
-- Good: Composite index for common query pattern
CREATE INDEX idx_status_date ON orders(status, created_at);
-- Bad: Index on rarely queried column
CREATE INDEX idx_color ON products(color); -- if color is rarely filtered

Query Optimization

1. Avoid SELECT *

SELECT * FROM users;

SELECT id, name, email FROM users;

2. Use EXPLAIN

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

• Full table scans
• Missing indexes
• Expensive operations

3. Avoid N+1 Queries

const users = await db.users.findAll();
for (const user of users) {
  const orders = await db.orders.findAll({ where: { userId: user.id } });
}

const users = await db.users.findAll({
  include: [{ model: Order }]
});

4. Use JOINs Efficiently

-- Good
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;
-- Less efficient
SELECT name, (SELECT total FROM orders WHERE user_id = u.id)
FROM users u;

Performance Tuning

1. Connection Pooling

• Creating connections is expensive
• Reuse connections efficiently

const pool = new Pool({
  max: 20, // Maximum connections
  min: 5,  // Minimum connections
  idleTimeoutMillis: 30000
});

2. Query Caching

• Expensive queries
• Frequently accessed data
• Data that doesn't change often

• Application-level caching (Redis)
• Database query cache
• CDN for static data

3. Partitioning

• Very large tables
• Time-based data
• Geographic data

• Range partitioning
• Hash partitioning
• List partitioning

4. Read Replicas

• Distribute read load
• Improve performance
• Geographic distribution

• Read-heavy workloads
• Reporting/analytics
• Backup queries

Data Types

• Use smallest type that fits
• Use INT instead of BIGINT when possible
• Use VARCHAR with appropriate length
• Use DATE/TIME types, not strings

-- Good
created_at TIMESTAMP
status ENUM('active', 'inactive')
price DECIMAL(10, 2)
-- Bad
created_at VARCHAR(50)
status VARCHAR(20)
price VARCHAR(20)

Migration Strategy

• Version control migrations
• Test migrations in staging
• Backward compatible changes
• Rollback strategy
• Zero-downtime migrations

• Liquibase
• Flyway
• Rails migrations
• Custom scripts

Monitoring

• Query execution time
• Slow query log
• Connection pool usage
• Database size
• Index usage

• Database monitoring tools
• APM solutions
• Custom dashboards

Conclusion

Good database design is crucial for application performance and scalability. Focus on proper normalization, strategic indexing, query optimization, and continuous monitoring. Remember: premature optimization is the root of all evil, but good design prevents most problems.

*Need help optimizing your database? Contact us for a database audit.*