Database design best practices

Key takeaway

In one line: Schema starts from domain invariants. Normalization, indexes, and partitioning come next; skip multi-tenant boundaries or idempotency keys in reward/event systems and migrations get expensive.

---

Introduction

Changing schema late is painful—migrations and downtime add up. After fixing normalization, indexes, and relationships on reward, event, and user data, we wrote down what we wish we had done earlier.

Normalization

Normalization removes redundancy and protects integrity.

First normal form (1NF)

Each column holds atomic values only.

Second normal form (2NF)

Remove partial functional dependencies.

Third normal form (3NF)

Remove transitive dependencies.

Indexing strategy

Indexes speed reads but can slow writes if overused.

Basic indexing

Choosing indexes

1. Columns in WHERE
2. JOIN keys
3. ORDER BY / GROUP BY
4. High-cardinality columns (many distinct values)

Composite column order

Put the most selective column first.

Relationships

One-to-many

Many-to-many

Use a junction table.

One-to-one

Choosing data types

Types affect storage and performance.

Numeric types

String types

Date and time

Constraints

Constraints enforce integrity.

Primary key

Foreign key

Check constraints

Unique constraints

Performance

Partitioning

Split very large tables.

Query patterns

Security

SQL injection

Permissions

Monitoring and maintenance

Slow query logging

Statistics

Conclusion

1. Normalize to cut duplication and protect integrity
2. Index wisely for read paths you actually use
3. Pick types that match cardinality and precision needs
4. Use constraints so the database enforces rules
5. Monitor and tune continuously

These practices help you build databases that scale and stay maintainable.

References

• PostgreSQL DDL
• MySQL optimization
• SQL style guide