ER Diagram Example: Designing an Online Store Database, Step by Step

An ER diagram is where a database design gets checked before it becomes migrations. A list of tables tells you what exists. The diagram shows how the tables depend on each other: which row needs which, what can be missing, and where a many-to-many hides.

This guide designs a small online store database from plain requirements to a finished entity relationship diagram, and explains the notation as it comes up. The shop is only the example; the method works for any schema.

When you need an ER diagram

A table list is enough when a schema has a handful of tables and one team works on it. Draw the diagram when people outside that team need to understand the data, when you review a design before writing migrations, or when a schema has grown past the point where anyone holds it in their head.

There are two common notations. Chen notation, from Peter Chen's 1976 paper that introduced the entity-relationship model, draws entities as rectangles and relationships as diamonds. It suits conceptual modeling. Crow's foot notation draws tables as boxes and puts the cardinality on the ends of the lines. It is usually credited to Gordon Everest in 1976 and is the notation most database design tools use. This guide uses crow's foot.

Step 1: Requirements in plain sentences

Write the rules as sentences first. Each one becomes a table, a column or a line.

1. A customer signs up with an email address and can save several addresses.
2. A customer places orders. Each order ships to one of the customer's addresses.
3. An order contains one or more products, each with a quantity and the price paid.
4. Each product belongs to one category.
5. An order can have several payment attempts, and most have one that succeeded.

Step 2: Entities and keys

The nouns become entities: customers, addresses, orders, order items, products, categories and payments. Each gets a primary key. Use surrogate keys (an identity column) for most tables, and natural unique keys where the business already has one: a customer's email, a product's SKU.

Order items are the exception. An order item has no identity of its own; it is the link between one order and one product. Its primary key is the pair (order_id, product_id).

Step 3: Relationships and cardinality

Every foreign key is a line. The symbols at each end say how many rows can sit on that side:

Read a relationship from one table across the line to the symbol at the far end: "one customer places zero or many orders", and back, "each order is placed by exactly one customer".

A NOT NULL foreign key means "exactly one" on the referenced side; a nullable one means "zero or one". "One or many" on the other side is a business rule, not something a foreign key enforces, so write it on the diagram where readers will see it.

Step 4: The many-to-many

Orders and products are many-to-many: an order holds many products, and a product appears in many orders. Relational databases model that with a table in the middle. Here that table already exists for a better reason than plumbing: order_items carries the quantity and the unit price at checkout, facts that belong to the pair and to neither side alone.

Step 5: Decisions worth writing down

Some choices are invisible in the column list and cause bugs when nobody knows them:

• Price snapshot. order_items.unit_price copies the product price at checkout. Changing a price later must not change old orders.
• Addresses are not edited in place. Once an order points to an address, editing that row would rewrite where past orders went. An edit creates a new address row.
• Payments are attempts. One row per attempt, with a status, so failed and retried payments stay visible.
• Money is numeric. The PostgreSQL documentation recommends numeric for monetary amounts because it is exact.

Step 6: The DDL

The two central tables in PostgreSQL:

CREATE TABLE orders (
  id                  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id         bigint NOT NULL REFERENCES customers (id),
  shipping_address_id bigint NOT NULL REFERENCES addresses (id),
  status              text NOT NULL DEFAULT 'pending'
                      CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  currency            char(3) NOT NULL,
  total               numeric(12,2) NOT NULL CHECK (total >= 0),
  placed_at           timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id   bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  product_id bigint NOT NULL REFERENCES products (id),
  quantity   integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(12,2) NOT NULL,  -- copied from products.price at checkout
  PRIMARY KEY (order_id, product_id)
);

CREATE INDEX ON orders (customer_id);
CREATE INDEX ON order_items (product_id);

PostgreSQL does not create an index for a foreign key column by itself, so the two CREATE INDEX lines matter for joins and for deletes on the referenced tables. order_items.order_id is covered by the primary key, whose index starts with it. ON DELETE CASCADE says an order item cannot outlive its order.

The diagram

How to read it:

• Customers (top left) have zero or many addresses and place zero or many orders.
• Orders (center) ship to exactly one address, contain one or many order items and have zero or many payments.
• Order items link each order to products, and each product belongs to exactly one category.
• The note holds the decisions from step 5, next to the tables they affect.

How to draw a readable ER diagram

• Name relationships with verbs. "Placed by", "ships to", "pays for" turn lines into sentences.
• Mark keys in the attributes. PK and FK markers show at a glance which column a line belongs to.
• Group tables by domain. Customers and addresses, ordering and payments, the catalog. Keep each group close together so the lines stay short.
• Leave out noise. Audit columns such as created_by and updated_at, soft-delete flags on every table and technical tables add rows without adding understanding.
• Draw the many-to-many table. Hiding it behind a direct line between orders and products hides the quantity and the price snapshot.
• Show business rules the schema cannot. "One or many" items per order and "addresses are not edited" are rules people must know.

Keep it in sync with the schema

A hand-drawn ER diagram is out of date after the next migration. Generate a first draft from the schema instead. The free SQL to ER diagram tool reads CREATE TABLE statements or a whole pg_dump and draws a card per table, with PK, FK and UK markers and a line per foreign key; there are also tools for PostgreSQL, Prisma and DBML. The generated crow's foot ends follow the columns: a NOT NULL foreign key is required and a nullable one optional. Rules the SQL does not hold, such as "one or many" items, you add by hand.

Build it in Archboard

1. Sign in with Google or GitHub and create a diagram.
2. To start from the schema, paste the CREATE TABLE statements into the SQL to ER diagram tool and select Open in Archboard. To draw by hand, add an Entity Relation pack Entity for each table, with one attribute per line and PK and FK markers.
3. Connect each foreign key to the table it references, and set the line ends to crow's foot markers: exactly one at the referenced table, zero or many at the foreign key side, and one or many where a business rule says so.
4. Label each line with a verb, such as "placed by" or "ships to".
5. Add a Core Markdown block with the design decisions.

The example uses 8 diagram nodes, within the Free plan. For how a schema fits into the rest of a system, see the system design diagram example.

FAQ

What is the difference between an ER diagram and a database schema diagram? In practice they overlap. An ER diagram can be conceptual, showing entities and relationships before tables exist; a schema diagram shows the actual tables, columns and keys. The crow's foot diagram in this guide is both.

Should every table have a surrogate key? Most tables benefit from one, because natural keys change. Link tables such as order_items often use the pair of foreign keys as the primary key instead.

How do I show a many-to-many relationship? With the table in the middle and two one-to-many lines. Name the middle table after what it represents, such as order items, not after the two tables it joins.

Do I draw every column? Draw keys and the columns that explain the design. Leave audit and technical columns out unless the diagram is about them.

Chen or crow's foot? Chen for conceptual discussions with people who do not think in tables, crow's foot for anything that will become a database.

Start free — one seat, no credit card.