Write the operations before naming a database#
A schema is useful only when it supports the product's reads, writes, invariants, and retention. Start with entities and relationships, then list the operations that must remain fast and correct at the expected scale.
Model chat from its access patterns
Entities
User · Conversation · ConversationMember · Message
Access patterns
Get conversations for a user
Get recent messages for a conversation
Append a new message
Find conversation membersconversation_id is a natural lookup and partition candidate for message history; (conversation_id, created_at, message_id) supports ordered pagination. A separate membership lookup keyed by user may be justified because “conversations for a user” points in the opposite direction.
Turn each operation into a storage requirement#
- Identify lookup keys, filters, sort order, ranges, and pagination direction.
- Estimate read/write frequency, payload size, growth, retention, and deletion behavior.
- State which changes form one transactional boundary.
- Notice queries that cross the likely partition key or require relationships.
- Choose normalization or duplication from update and read behavior.
Exercise: model a News Feed#
Before choosing storage, support creating and retrieving a post, posts by author, a paginated home timeline, high read volume, and celebrity accounts.
Reveal one access-pattern-first answer
Keep canonical posts addressable by post_id and an author timeline ordered by creation time. Treat the home timeline as a separate access pattern: ordinary authors may fan out references to follower timelines, while celebrity posts can be merged at read time to avoid explosive writes. Use stable cursor pagination and keep the source post authoritative even if feed entries duplicate display metadata.
- Choosing a product before stating queries.
- Using one table or collection for unrelated access patterns merely to avoid duplication.
- Ignoring retention, deletion, or cross-tenant boundaries.
Related practice problems
Use the manifest-backed prompts below to apply this lesson in a complete design.
- DynamoDB data-modeling best practicesAWS Documentation
- PostgreSQL data definitionPostgreSQL Documentation