Data modeling

A data model is a set of decisions about what is true, how you find it, and what you are willing to recompute. Start from the requirements you already wrote, not from a favorite database.

Entities and keys

List the nouns. For each one, name the primary key and the two or three ways you will look it up. If you cannot name the lookup, you do not yet know the entity.

Prefer opaque ids (uuid or a snowflake) that you generate, so you are not stuck with a single-node sequence when you shard. Put the tenant id in the key when every query is scoped to a customer, a lab, or a restaurant. That keeps one tenant’s rows together and makes a bad query obvious.

Relationships are foreign keys while you are in one database. They become ids plus a background check once you split services. Do not split services in the first drawing unless the boundary is the point of the problem.

Normalize, then denormalize a specific read

Third normal form is the right default for data that changes: a user’s country lives once. Denormalize when a read is hot and the join is the cost. A feed item can store the author’s display name at fan-out time. Say what you do when the name changes: either you accept staleness for a while, or you have a repair job. “I’ll just denormalize” without that sentence is incomplete.

Facts you must be able to replay

Some rows are not “the current value.” A grade depends on the rubric version, the model version, and the prompt version. A price depends on when it was quoted. Store those as columns, and unique-key the combination you must not double-insert. Overwriting a score in place destroys the audit.

Status columns want a constrained set (open, claimed, submitted, canceled) and a partial index on the state the workers poll. A lease column (lease_until) belongs on the row if a human or a worker can crash mid-task.

What does not belong in the row store

Bytes, video, and large documents go to object storage. The row keeps the pointer, size, checksum, and ACL. Search documents, embeddings, and analytics rollups are derived. Write the source of truth once, in the same transaction as an outbox row, and let a consumer update the derived view. Dual writes from the application will drift the first time a process dies between them.

flowchart LR
  API[API] --> TX[Transaction]
  TX --> Rows[(Orders)]
  TX --> Outbox[(Outbox)]
  Outbox --> Pub[Publisher]
  Pub --> Search[(Search index)]
  API --> Blob[(Object storage)]