Data modeling, SQL vs NoSQL, and indexing

Relational vs document data models

You’ll see the “users and posts” example everywhere, because it highlights when normalized tables with joins shine vs when flexible documents are better.

Relational model: normalized tables

In a relational (SQL) design, you split data into related tables and connect them with keys.

Example schema:

sqlCREATE TABLE users ( id BIGSERIAL PRIMARY KEY, username TEXT UNIQUE NOT NULL, email TEXT UNIQUE NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE posts ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id), title TEXT NOT NULL, body TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() );

Usage example: “get the latest posts with their authors”:

sqlSELECT p.id, p.title, p.created_at, u.username FROM posts p JOIN users u ON p.user_id = u.id ORDER BY p.created_at DESC LIMIT 20;

Why this works well

• Normalization: user data (email, username) is stored once, in users.
  • Update email in one row → all related queries see the change.
• Joins: you combine tables on keys at query time.
  • You don’t duplicate user info into every post.
• Consistency: foreign keys ensure every posts.user_id points to a real user.
• Strong querying: you can easily add more related tables later (comments, likes, etc.) and join them.

Where it can be awkward

• Very document-like fields (deeply nested JSON, lots of optional properties) can be clumsy to model as many small tables.
• Heavy cross-table joins at massive scale can become a bottleneck if not indexed well.
• Schema changes (adding columns, changing types) are more controlled, sometimes slower to roll out at huge scale.

Document model: one document, nested data

In a document database (like MongoDB), you often store a user and related posts together, or at least use flexible nested structures.

Example 1: embed small posts inside user:

json{ "_id": "user_123", "username": "alice", "email": "alice@example.com", "created_at": "2024-01-01T10:00:00Z", "posts": [ { "post_id": "p_1", "title": "Hello", "body": "First post", "created_at": "2024-01-02T09:00:00Z" }, { "post_id": "p_2", "title": "Another", "body": "More text", "created_at": "2024-01-03T10:00:00Z" } ] }

Usage example: “get one user and all their posts”:

jsdb.users.findOne({ _id: "user_123" })

That’s a single document read, no join.

Example 2: separate users and posts collections, but keep posts flexible:

json// users collection { "_id": "user_123", "username": "alice", "email": "alice@example.com" } // posts collection { "_id": "p_1", "user_id": "user_123", "title": "Hello", "body": "First post", "tags": ["intro", "fun"], "metadata": { "likes": 10, "views": 200, "language": "en" } }

You can add metadata.language, tags, or any new field without a schema migration.

Pros of document style

• Flexible schema: each document can have slightly different fields (metadata, settings, etc.).
• Good for aggregates you almost always load together (user profile + preferences + small list of recent posts).
• Fewer joins: data that naturally “belongs together” can live together.

Cons / tradeoffs

• Duplication risk: if each post embeds a full copy of the user profile, updating user info means touching many documents.
• Atomicity scope: a single document is typically the atomic unit; multi-document transactions are possible in some systems but more complex/slower.
• Complex queries that join many different entity types can be harder or less efficient.

When normalization and joins vs documents matter

Use normalized relational tables when:

• Many entities reuse shared data (one user, many posts, many comments).
• You care strongly about referential integrity (posts.user_id must always point to a real user).
• You run lots of different ad-hoc queries (analytics, reporting) over many relationships.

Use documents when:

• Each “thing” is mostly self-contained (a user profile with settings, a post with comments limited in number).
• The structure evolves often and you don’t want frequent schema migrations.
• Your main access pattern is “load a whole aggregate by id” (one read, no joins).