Entity Relationship Diagram Example: Model a Database
You've been asked to "design the database" for a side project — an online bookstore, say — and you're staring at a blank doc wondering where to start. Do you start writing CREATE TABLE statements? Sketch boxes on paper? Most people open SQL too early, build three tables, then realize halfway through that a book can have two authors and now the whole shape is wrong.
An entity relationship diagram fixes that. It's the picture you draw before you touch SQL — the one that catches "wait, can a customer have more than one order?" while it's still a five-second edit instead of a migration. Here's a worked example you can actually copy.
What an entity relationship diagram example actually shows
An ERD has three moving parts, and that's it:
- Entities — the nouns you store. Customer, Order, Book, Author. Each becomes a table. You draw them as boxes.
- Attributes — the fields inside each entity. A Book has a title, an ISBN, a price. These are the columns.
- Relationships — the lines connecting boxes, with little symbols on the ends that say how many. This is the part people get wrong, and it's the part that matters most.
That last bit — the "how many" — is called cardinality, and it's the whole reason an ERD beats a list of tables. A list tells you what exists. An ERD tells you how the pieces connect, which is where database bugs are born.
The worked example: an online bookstore
Let's model four entities for a bookstore: Customer, Order, Book, and Author. I'll build the relationships one at a time, because that's how you should think about it — one connection at a time, asking "one or many?" in both directions.
Customer → Order. One customer can place many orders. One order belongs to exactly one customer. That's a one-to-many relationship — the bread and butter of database design. In crow's-foot notation (the standard you'll see everywhere), the "many" end gets a little three-pronged fork that genuinely looks like a crow's foot. So the line from Customer to Order has a single bar near Customer (one) and a crow's foot near Order (many).
How do you store this? The Order table gets a customer_id column — a foreign key pointing back at the Customer it belongs to. The "many" side always holds the foreign key. Get this rule into your bones and half of database design stops being mysterious.
Order → Book. Here's where beginners trip. An order can contain many books. And a single book (say, a popular novel) appears in many different orders. That's a many-to-many relationship, and you cannot store it with a single foreign key — there's nowhere to put it.
The fix is a third entity sitting in the middle, usually called a join table or junction table. Call it OrderItem. It holds two foreign keys — order_id and book_id — plus anything specific to that line, like quantity. Now the many-to-many becomes two clean one-to-many relationships: one Order has many OrderItems, and one Book appears in many OrderItems. Every time you see "many-to-many," reach for a join table. It's not optional; it's how the data physically fits.
Book → Author. Tempting to put author as a text column on Book and move on. But a book can have co-authors, and an author writes many books — that's another many-to-many. So it gets its own join table too, BookAuthor, with book_id and author_id. This is exactly the trap from the opening: if you'd started in SQL with author VARCHAR on the books table, you'd be rewriting it the moment a co-authored book showed up.
Put those four entities and their connections on one canvas and the shape of the whole bookstore is visible at a glance — including the two join tables you'd have missed if you'd jumped straight into code.
Reading crow's-foot notation without memorizing a chart
You only need four symbols, and they always sit at the end of a relationship line, describing the entity on that side:
- A single bar
|— "one." Exactly one. - A crow's foot
<— "many." Zero or more on this side. - Bar + crow's foot — "one or many" (at least one).
- Circle + crow's foot — "zero or many" (optional, can be none).
Read a line by looking at both ends. Customer—Order is "one" (bar) on the Customer end and "many" (crow's foot) on the Order end, so: one customer, many orders. That's the entire language. People act like ERD notation is arcane; it's four symbols and a direction.
The mistakes that make an ERD useless
A cluttered ERD is worse than no ERD, so a few things to avoid:
- Storing a list in one column. If you ever think "I'll just comma-separate the authors in one field," stop — that's a many-to-many begging for a join table. Comma-separated fields are the #1 sign someone skipped the diagram.
- Forgetting the optional case. Can an order exist before it has any items? Can a customer exist with zero orders? The circle-vs-bar distinction (zero-or-many vs. one-or-many) is where real rules live. Decide it on the diagram, not in a 2 a.m. bug.
- Drawing 15 entities at once. Model the core four or five first, get the relationships right, then add the peripheral stuff (addresses, payments, reviews). An ERD that tries to show everything shows nothing.
If you've read our sequence diagram example, notice the contrast: that diagram answers "in what order do things happen?" An ERD answers "how is the data shaped?" Most real systems need both — one for behavior, one for structure — and they're genuinely different tools, not two views of the same thing.
Let AI draw the boxes so you can think about the model
The tedious part of an ERD isn't the thinking — it's the drawing. Aligning boxes, routing lines so they don't cross, redoing the layout every time you add an entity. That's exactly the work to hand off.
Describe your data model in plain English — "a bookstore with customers, orders, books, and authors; orders contain many books; books can have multiple authors" — and Ridvay turns it into a clean ERD with the entities, foreign keys, and crow's-foot relationships already laid out. Then you do the part that matters: look at it and ask "is that cardinality actually right?" Tweak a relationship, add an entity, and the layout reflows on its own.
You stay in charge of the model. The tool just handles the boxes and lines — which is the whole point of drawing it before you write the SQL.