Question 2
Designing a scalable data model
You're designing a local database for a feature with relationships (such as users, posts, and comments). How do you model it to balance performance, flexibility, and future changes?
Follow-ups
- When do you denormalize?
Answer outline
Model around what the app actually loads and mutates. Start from the screens you have and the queries they run, rather than from a textbook entity relationship diagram (ERD). Performance, flexibility, and migration safety all follow from that.
Keep the model normalized, so each fact has one place to update, and add indexes on the filters and sorts you hit most. Denormalize only as a deliberate choice with explicit update rules, once profiling shows a hot read needs it.
Give every major entity a stable ID and plan for change from day one. Adding an optional field is a cheap migration, and a clear owner for each write path keeps the store consistent as the schema evolves.
Principles
- Let the screens you have shape the model, because an abstract ERD optimizes for queries you may never run.
- Give every major entity a stable ID so merges, deduplication, and updates stay reliable.
- Normalize first so each fact has one home, and denormalize one field at a time after measuring.
- Plan migrations early, since a schema with room for optional additions is far cheaper to evolve than a rigid one.
- Keep write ownership clear, because undefined write paths are the fastest route to corruption.
A minimal normalized schema for a social feed, with indexes on the most common filter and sort keys:
User(id, name)
Post(id, authorId, createdAt, body, mediaPath?)
Comment(id, postId, authorId, createdAt, text)
// Index examples:
// Post(createdAt), Comment(postId, createdAt)
Follow-up angles
- Denormalize when a measured hot read is worth the extra write, and give the copied field one owner who updates it.
