Design a simple data model with scaling hooks

1. Map access patterns to your schema

You’re designing a tiny “social posts” feature. Before you draw any tables or collections, nail what the code will actually do with the data.

Think in terms of user actions → queries. The schema should make those queries simple and fast.

Step 1: List concrete access patterns

For a micro post feature, start with a minimal but realistic set:

1. User timeline

   • “Show me my own posts, newest first.”
   • Query shape:
     • Filter: author_id = ?
     • Sort: created_at DESC
     • Pagination: LIMIT 20 OFFSET ? or created_at < ? LIMIT 20

2. Profile post list

   • “Show this user’s posts on their profile.”
   • Almost identical to the timeline above. You might add:
     • Optional filter: visibility IN (...)

3. Post detail

   • “Open this specific post.”
   • Query shape:
     • Filter: post_id = ?
     • Fetch exactly 1 row/document.

4. Basic moderation / admin

   • “Find posts by id or by user for moderation.”
   • Query shape:
     • Filter: post_id = ?
       or author_id = ? with date filters

5. Simple feed

   • “Show posts from users I follow” (we’ll keep it naive).
   • Query shape:
     • Input: a list of author_ids you follow (say up to 100)
     • Filter: author_id IN (...)
     • Sort: created_at DESC
   • (This is not hyper-scalable yet, but good enough for a micro feature.)

From these, you can see that every important query is centered on:

• post_id
• author_id
• created_at

Those three should be first-class citizens in your schema.

Step 2: Choose relational vs document shape

For this micro feature, a single main entity — Post — is enough.

You could do:

• Relational (SQL):

  • Table: posts
  • Columns: id, author_id, body, created_at, visibility, etc.

• Document (NoSQL):

  • Collection: posts
  • Document fields: _id, author_id, body, created_at, visibility, etc.

In both cases, the logical shape is the same: 1 row/document per post.

Step 3: Draft the basic Post structure

Propose a minimal but practical structure:

How this maps to access patterns:

• User timeline / profile: filter by author_id, sort by created_at.
• Post detail: filter by id.
• Moderation: filter by id or by author_id + date.
• Simple feed: filter by author_id IN (...), sort by created_at.

You haven’t written the schema “in the abstract”; you’ve wired it to queries.