Data modeling means deciding which facts a system retains, how they relate, and which rules must be protected. Tables or documents express those decisions. The model also determines how much work a query requires and which changes must be coordinated.
A relational database such as Postgres is often a practical starting point when relationships, constraints, and local transactions are clear. The choice must be justified by the requirements; other structures may better fit particular access patterns and units of update.
Start with access patterns and invariants
Before choosing storage, describe the operations: retrieve a user, list their posts, read a post’s comments, and create or delete those entities. Each access has filters, ordering, result size, and frequency. It also matters whether it needs current data or can accept a delayed projection.
Estimates include record counts, growth, field sizes, reads and writes, concurrency, and load distribution. One highly active user may cost more than many occasional users. Entity counts alone do not describe the work queries perform.
Invariants complete the contract: each post has an existing author, each comment belongs to a post, and the email identifier follows a uniqueness policy. Next, define who can modify each piece of data. Referential integrity and authorization answer different questions.
These requirements support comparing models and designing indexes before introducing duplication or distribution. The goal is to retain coherent facts and serve specific access patterns within measured capacity.
Storage models and contracts
| Model | Access it may favor | Required decisions |
|---|---|---|
| Relational | Relationships, filters, joins, and transactions across multiple rows. | Constraints, isolation, indexes, and query costs. |
| Document | Reading and updating an aggregate with a variable structure. | Aggregate boundaries, validation, references, and schema evolution. |
| Key-value | Access through keys and associated structures. | Key design, available operations, durability, and secondary access patterns. |
| Wide-column | Queries organized by partition and clustering keys. | Locality, load distribution, and how data is retrieved. |
Documents also have schema contracts. Validation and migrations must account for older versions and field changes. Postgres can combine structured columns with JSONB when part of the content needs flexibility; this combination still requires decisions about validation, indexes, and updates.
Key-value also means more than caching or exact lookups. Redis offers several data types. DynamoDB can serve as a durable primary database and offer partition and sort keys and secondary indexes. Each implementation’s contract determines the available queries and guarantees.
Wide-column is different from columnar storage designed for analytics. Its capacity depends on key design and workload; the model’s name does not establish universal throughput.
A relational edge table can represent certain relationships between users. If variable-depth traversals or specific path queries dominate, a graph model merits evaluation. The decision depends on those traversals and their costs, rather than treating every social network the same way.
Entities, keys, and constraints
The example uses three entities: Users, Posts, and Comments. A user can publish many posts and write many comments. Each post and comment has an author; each comment belongs to a post.
--- config: look: handDrawn --- erDiagram USERS ||..o{ POSTS : publishes USERS ||..o{ COMMENTS : writes POSTS ||..o{ COMMENTS : receives USERS { bigint id PK text email UK } POSTS { bigint id PK bigint user_id FK timestamptz created_at text body } COMMENTS { bigint id PK bigint post_id FK bigint user_id FK timestamptz created_at text body }
An illustrative local schema can be expressed as follows:
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE
);
CREATE TABLE posts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
created_at timestamptz NOT NULL DEFAULT now(),
body text NOT NULL
);
CREATE TABLE comments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
post_id bigint NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
user_id bigint NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
created_at timestamptz NOT NULL DEFAULT now(),
body text NOT NULL
);The bigint identities suit this example within a single database. A PK identifies a row and creates a unique index. FKs protect references because they are declared and enforced; a field named user_id without that constraint does not provide the same guarantee.
NOT NULL makes the diagram’s references mandatory. Allowing a null author would change the contract. Here, deleting a post deletes its comments through CASCADE, while deleting a user with references is restricted. Another product may need anonymization or a different retention policy, which must be chosen explicitly. An FK does not authorize a client to publish or delete someone else’s content.
For email, the example requires a canonical form: trim surrounding whitespace and treat the identifier as case-insensitive, storing it in lowercase. This policy must apply to every write path. The DDL shown does not perform these transformations: UNIQUE on text compares values according to its configuration and does not imply that case is ignored. If the database must enforce this equivalence, it needs a normalized key or an expression index consistent with the policy.
Indexes that follow the APIs
To list a user’s posts by created_at DESC, id DESC, a composite B-tree index can align the equality filter and ordering. Comments on a post follow a similar access pattern:
CREATE INDEX posts_user_created_id_idx
ON posts (user_id, created_at DESC, id DESC);
CREATE INDEX comments_post_created_id_idx
ON comments (post_id, created_at DESC, id DESC);
CREATE INDEX comments_user_id_idx
ON comments (user_id);Postgres does not automatically create an index on an FK’s referencing column. The first two indexes already include the FKs used in these access patterns as their leading columns; adding an equivalent standalone index may be redundant. The third supports access by comment author, which is also relevant when locating references during operations on users. Its usefulness is evaluated against the actual workload.
A page after the cursor in the post listing can query:
SELECT id, body, created_at
FROM posts
WHERE user_id = $1
AND (created_at, id) < ($2::timestamptz, $3::bigint)
ORDER BY created_at DESC, id DESC
LIMIT $4;The cursor contains the timestamp and identifier of the last result. id breaks ties between equal timestamps; the first page omits the cursor predicate. This deterministic ordering does not guarantee a snapshot across requests: inserts, deletes, or changes to ordering fields may alter what is observed.
Each index adds space and write work. The plan is validated with EXPLAIN, data distribution, and selectivity; measuring actual execution requires an appropriate environment, considering that EXPLAIN ANALYZE executes the query. An available index does not force the optimizer to choose it or guarantee a particular latency.
Normalization and projections
Normalization allows each source fact to be retained in a defined place. In this example, email belongs to the user; copying it into every post would require deciding what to update when it changes. References avoid that duplication for operations that need the current value.
Other requirements may call for a historical copy, such as the name displayed at publication. That copy must be declared as a historical fact with its own meaning, rather than confused with the author’s current identity.
Denormalization can speed up a feed, a counter, or an aggregated view when it delivers a measured benefit. It does not require exhausting every possible index, but it does require understanding its cost and defining updates, freshness, and reconciliation. A delayed projection or a duplicated counter needs a recovery policy.
SD: Caching distinguishes a reusable copy from the data’s authority. If publishing changes requires coordinating writes and events, SD: 2PC vs Saga explains the outbox and its delivery limits. Neither mechanism removes the need to define the source fact.
Locality, partitions, and sharding
After measuring capacity, distribution is evaluated alongside access patterns. Partitioning tables within a single Postgres installation does not automatically imply distributing them across independent servers.
An assignment by post_id can place a post and its comments together if both use that routing unit. The feed by user_id might then require fanout or an additional projection. An assignment by user or tenant favors other access patterns, at the cost of concentrating the data and traffic of a highly active entity.
The local example’s FKs do not automatically transfer across shards. Global uniqueness, references, and transactions need guarantees from the engine or an architecture that maintains them. Generated identities also require a uniqueness contract at the distributed scope; starting independent local generators is not enough to assume they will never collide.
SD: Sharding develops this choice among locality, hotspots, and distributed operations. The data model must make clear which rules remain within one authority and which need coordination.
What to verify
The model is reviewed by walking through the APIs and their writes: rows touched, references checked, and constraints that prevent invalid states. Deletes, duplicates, case variations in email, and unauthorized requests must also be considered.
Plans and capacity are checked with representative data, including highly active users, tied timestamps, large histories, and concurrent writes. Pagination must declare how it behaves when the dataset changes. Projections are reviewed with repeated or delayed events and pending recovery.
The choice is justified when it is possible to explain where each fact lives, what protects its integrity, which path answers each query, and what happens when the model changes or its data is distributed.