Data Modeling

Before choosing a database technology, first decide what your application must do. Data modeling is the process of deciding what information to store, how to break it into pieces, and how those pieces connect. Every downstream decision about schema, indexes, and database choice depends on a clear data model and the access patterns it must support.

The image illustrates a data modeling concept, showing how photos, users, likes, and comments feed into a database, accompanied by questions on storing and shaping data.

Start with questions—what will users repeatedly ask this app to do? These repeated queries are the access patterns. Only after listing access patterns should you choose how to shape and store data.

The image illustrates a basic system architecture with a mobile device, an app server, and a database, accompanied by the question, "What will people ask this app to do?"

Common access patterns for a photo-sharing app

Show me a user’s profile.
Show me all photos uploaded by a user.
Show me my home feed (photos from people I follow, newest first).
Upload a photo.
Like a photo.
Comment on a photo.
Follow another user.

These access patterns are essentially the app’s functional requirements written as data queries. Capturing them up front ensures your schema and indexes are optimized for the most frequent operations.

The image shows a list of user actions titled "Access Patterns," alongside an illustration of a social media app interface on a smartphone. The actions include viewing profiles, liking, commenting, uploading photos, and following users.

Designing the data model: entities and relationships Below are the primary entities for this app and the minimal fields they need to support the access patterns.

Users
- When a user signs up (e.g., Alan), create a user record with a unique user ID, name, email, and timestamps.
Photos
- When a user uploads a photo, create a photo record with a unique photo ID, poster user ID, caption, upload timestamp, and a pointer/URL to the binary image file stored in object storage (e.g., Amazon S3).
- The binary image itself is kept outside the main record in object storage; the photo record stores its URL or object key.
Connections (relationships)
- Likes: store a record linking user ID and photo ID plus the like timestamp. To get the like count for a photo, count likes where photo ID = X (or maintain a counter if reads are much more frequent than writes).
- Comments: a comment record links commenter user ID, photo ID, comment text, and timestamp.
- Follows: a follow record links follower user ID and followee user ID; query follows by follower ID to find who a user follows.

Notice the pattern: relationships are small records that link unique IDs (user IDs, photo IDs) with minimal metadata like timestamp or text. Unique IDs are the glue that ties the model together. Entity summary

Entity	Purpose	Key fields
Users	Identity and profile data	`user_id`, `name`, `email`, `created_at`
Photos	Metadata for each uploaded image	`photo_id`, `user_id` (poster), `caption`, `url`, `uploaded_at`
Likes	Track who liked what and when	`like_id` (optional), `user_id`, `photo_id`, `liked_at`
Comments	Text and metadata for comments	`comment_id`, `user_id`, `photo_id`, `text`, `commented_at`
Follows	Who follows whom	`follower_id`, `followee_id`, `followed_at`

Example schemas

SQL (relational) example — minimal CREATE TABLE statements

CREATE TABLE users (
  user_id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT now()
);

CREATE TABLE photos (
  photo_id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES users(user_id),
  caption TEXT,
  url TEXT NOT NULL,          -- points to object storage (e.g., S3)
  uploaded_at TIMESTAMP DEFAULT now()
);

CREATE TABLE likes (
  like_id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES users(user_id),
  photo_id BIGINT REFERENCES photos(photo_id),
  liked_at TIMESTAMP DEFAULT now()
);

CREATE TABLE comments (
  comment_id BIGSERIAL PRIMARY KEY,
  user_id BIGINT REFERENCES users(user_id),
  photo_id BIGINT REFERENCES photos(photo_id),
  text TEXT NOT NULL,
  commented_at TIMESTAMP DEFAULT now()
);

CREATE TABLE follows (
  follower_id BIGINT REFERENCES users(user_id),
  followee_id BIGINT REFERENCES users(user_id),
  followed_at TIMESTAMP DEFAULT now(),
  PRIMARY KEY (follower_id, followee_id)
);

NoSQL (document) example — a photo document in MongoDB-like style

{
  "photo_id": "507f1f77bcf86cd799439011",
  "user_id": "507f191e810c19729de860ea",
  "caption": "Golden hour",
  "url": "https://s3.amazonaws.com/bucket/obj-key.jpg",
  "uploaded_at": "2024-06-01T15:23:45Z"
}

Index strategy driven by access patterns Because access patterns determine which queries must be efficient, design indexes to match them:

Show all photos by user: index photos(user_id, uploaded_at DESC) to support quick retrieval and ordering.
Home feed (photos from followed users sorted by time): either
- Query recent photos for each followee (requires index photos(user_id, uploaded_at)), then merge in application code, or
- Precompute feeds (fan-out on write) into a per-user feed store to optimize for fast reads at the cost of write amplification.
Likes count: either COUNT(*) on likes WHERE photo_id = X (requires index on likes(photo_id)), or maintain a denormalized counter on the photos row for O(1) reads.
Follow lookups: index follows(follower_id) to find who a user follows, and follows(followee_id) to find a user’s followers.

To illustrate the feed assembly for a user who follows 200 people, the system typically:

Query follows to list the 200 followees.
Query recent photos for those followees (use an index on photos.user_id and uploaded_at).
Merge and sort results by time to build a timeline.

Because “scrolling the feed” is the single most common action, make sure the feed-query path is efficient—this often drives denormalization or precomputed feeds.

The image depicts a data representation for arranging a home feed, showing photos sorted by time from newest to oldest. It includes a list of questions users ask, with "My Home Feed" highlighted, alongside visual components for users, likes, comments, and follows.

Key takeaways

Start with access patterns (the questions users ask) before choosing a database engine.
Model entities (users, photos, likes, comments, follows) and their relationships using unique IDs as links.
Design indexes and consider denormalization according to the most frequent, latency-sensitive queries (e.g., home feed).
Store large binaries (images) in object storage (e.g., Amazon S3) and keep only pointers in your records.

With access patterns and data shape defined, you can evaluate SQL vs NoSQL based on consistency, transactional needs, query complexity, and scaling requirements.

Design data shape from access patterns first. Only after you know which queries must be fast should you decide how to store and index the data.

Links and references

AWS S3 (object storage): https://aws.amazon.com/s3/
Choosing SQL vs NoSQL: https://www.mongodb.com/nosql-explained (overview)
Database index fundamentals: https://www.postgresql.org/docs/current/indexes.html

Foundations & One Server

Data: Reads and Writes

Media, APIs, and Async Work

Failure, Protection, and Operations

Put It All Together

Watch Video