Tables, relationships, indexes, and normalization—design databases that scale with your API.
Good database design makes your API fast and maintainable. Here are the essentials.
Database design and SQL
Core concepts
Tables and columns — One entity per table. Columns with clear types; avoid storing multiple values in one column. Use UUID or bigint for primary keys when you need distribution or scale.
Relationships — One-to-many (foreign key on the “many” side). Many-to-many via a join table. Model real-world relationships; don’t over-normalize or under-normalize.
Indexes — Index columns you filter or join on. Composite index for common query patterns (e.g. user_id + created_at). Don’t index everything; writes get costlier.
Normalization — Reduce duplication; split into tables and reference by ID. Denormalize only when you’ve measured and need read performance (e.g. cached aggregates).
Design practices (backend survey):
Database design practices
For APIs
Design for the access patterns your API needs. Avoid N+1: fetch with joins or batch. Use pagination (cursor or offset) for lists. Consider read replicas and caching when write volume is low and read volume is high.
Database design intro:
Takeaway
Start with a clear schema and relationships. Add indexes based on queries; use migrations for every change. Iterate with real usage; measure before denormalizing.
