ER Diagram Examples: Blog, E-commerce, and SaaS
The fastest way to learn ER modeling is to study real examples. This guide walks through three annotated schemas, a blog, an e-commerce store, and a SaaS app, and the decisions behind each.
Modeling advice in the abstract only goes so far; at some point you learn by seeing complete, realistic schemas and understanding why they are shaped the way they are. This guide walks through three ER diagrams for domains you probably know well: a blogging platform, an e-commerce store, and a multi-tenant SaaS application. Each introduces new modeling challenges, so by the end you will have seen one-to-many relationships, junction tables for many-to-many, self-referential relationships, and the tenant-isolation pattern that underpins most SaaS products.
For each example we name the entities, describe the key relationships and their cardinality, and call out the design decisions that matter. You can recreate any of these in the editor at /diagrams, or generate a starting point from a plain-English description with the AI generator at /diagram-tools/ai-diagram-generator and refine it in the ERD tooling at /diagram-tools/erd-tool. Treat them as templates to adapt rather than blueprints to copy verbatim.
Example 1: A blogging platform
The blog schema centers on four entities: User, Post, Comment, and Tag. A User writes many Posts, so Post carries an author_id foreign key: a straightforward one-to-many. A Post has many Comments, so Comment carries a post_id, another one-to-many. Comments also have authors, so Comment carries an author_id to User as well, meaning a single Comment participates in two relationships. Already this small model shows how a table can point at multiple parents.
Tags introduce the first many-to-many relationship: a Post can have many Tags and a Tag applies to many Posts. This requires a junction table, PostTag, holding post_id and tag_id with their combination as the primary key. A subtler decision appears if you allow threaded comments, where a comment can reply to another comment. That is a self-referential relationship: Comment gets a nullable parent_comment_id foreign key pointing back at Comment. The nullability matters, top-level comments have no parent, so the column must allow null. This example alone covers one-to-many, many-to-many, and self-referential patterns.
Example 2: An e-commerce store
The e-commerce schema is richer. Core entities include Customer, Product, Order, OrderItem, Category, and Address. A Customer places many Orders (one-to-many via customer_id on Order). An Order contains many products, but this is a many-to-many that carries data, so it needs an explicit OrderItem junction table with order_id, product_id, quantity, and unit_price. Storing unit_price on OrderItem rather than reading it from Product is a deliberate and important choice: it captures the price at the time of purchase, so later price changes do not rewrite historical orders.
Products belong to Categories, and if a product can be in one category it is one-to-many, but real stores usually allow many categories per product, making it another many-to-many with a ProductCategory junction. Categories are also frequently self-referential, a Category has a nullable parent_category_id to model subcategories. Addresses illustrate a design fork: a Customer has many Addresses (shipping, billing), so Address carries a customer_id, and each Order references the specific shipping and billing Address used, capturing them at order time much like the unit price. These "snapshot at transaction time" decisions are the hallmark of a well-designed commerce schema.
Example 3: A multi-tenant SaaS application
The SaaS schema adds the defining challenge of multi-tenancy: many customer organizations share one database, and each organization's data must be isolated from every other's. The top entity is Organization (the tenant). Users belong to an Organization, and almost every other entity, Project, Task, Comment, carries an organization_id foreign key so that every row is owned by exactly one tenant. This tenant key threaded through the schema is what enables queries to be scoped to a single organization and prevents data from leaking across tenants.
Beyond tenancy, the SaaS model shows a many-to-many with attributes in its membership design: a User can belong to an Organization with a specific role (admin, member, viewer), so a Membership junction table holds user_id, organization_id, and role. The role attribute belongs on the membership because the same user might be an admin in one organization and a viewer in another. Projects have many Tasks (one-to-many), Tasks can be assigned to Users (a nullable assignee_id, since a task may be unassigned), and Tasks may relate to each other as dependencies (a self-referential many-to-many via a TaskDependency junction). This example combines every pattern from the earlier two with the tenant-isolation column that defines SaaS data design.
Patterns worth carrying forward
Across the three examples, a handful of recurring patterns do most of the work. Recognizing them means you can model most new domains by composing familiar pieces rather than starting from nothing.
- One-to-many via a foreign key on the child is the default relationship; reach for it first.
- Many-to-many needs a junction table; if the relationship has its own attributes, they live there.
- Snapshot transactional data (price, address) at the time of the event rather than reading it live later.
- Self-referential relationships model hierarchies and threads via a nullable parent foreign key.
- Multi-tenant SaaS threads a tenant (organization) foreign key through nearly every table for isolation.
- Roles and permissions usually belong on a membership junction, not on the user or the organization alone.
- A nullable foreign key expresses an optional relationship, like an unassigned task.