Drawing an ERD is mostly a matter of order: pull the entities out of your requirements, pin down the relationships, settle the keys, then put it all into notation. Whether it's your first class assignment or your first real design at work, the order is the same.
Reading that sentence won't make it stick, though — so let's design a small forum database end to end. By the time you're done, you'll have runnable DDL and a checklist you can use to review your own designs.
Step 1: Pull entities out of the requirements
When you're staring at a blank page, start by circling the nouns.
A member writes posts. A post receives comments. Each post belongs to one category.
Member, post, comment, category. Those are the candidates.
Not every noun survives, of course. The test is simple: if you store many of them and need to tell them apart, it's an entity; if it's a single value describing something else, it's an attribute.
Run the nouns you'd meet in real requirements through that test and you get something like this:
| Noun | Verdict | Why |
|---|---|---|
| Member, post, comment | Entity | Many rows accumulate and each must be identified |
| Category | Entity | Managed as its own list, shared by many posts |
| Title, created date | Attribute | A single value describing one post |
| Comment count | Neither | An aggregate you can compute — storing it means maintaining it |
Values you can compute, like a comment count, stay out by default. Storing them for performance is denormalization, and that can wait until the need is proven.
Roles like "admin" sit in the same gray zone. If there are only a few role values with no data attached, a role column on members is enough. The moment each role starts carrying its own data — a permission list, say — promote it to an entity.
This is the mistake I see most often in design reviews: category never becomes its own entity and ends up as category VARCHAR(50) inside the posts table. It works fine at first, which is exactly why nobody notices.
Then one day someone renames a category, you're running an UPDATE across tens of thousands of posts, and the lesson finally lands. When many rows share the same value, treat it as a signal to split.
Step 2: Pin down relationships and cardinality
With the entities in hand, look at the verbs. "Writes," "receives," and "belongs to" each become a relationship. For each one, decide how many rows correspond on either side.
- One member writes many posts → member 1 : N post
- One post receives many comments → post 1 : N comment
- One category contains many posts → category 1 : N post
Posts and tags are a different story: both sides can have many. Relational databases can't store many-to-many directly, so you add a junction table like post_tags and split it into two one-to-manys. This comes up in interviews constantly, so remember the why, not just the trick.
The part people skip is the minimum. A post can exist with zero comments, so from the post's side, comments are 0..N. It looks like a detail, but it's what later decides your NOT NULL constraints and whether you reach for a LEFT JOIN.
Going further: three variations you'll meet early
One-to-one. Some relationships map exactly one to one, like a member and their profile. You'll reach for 1:1 to separate hot login data from rarely-read bios and settings, or to isolate sensitive data behind different access rules. That said, if your diagram is full of 1:1 lines, first ask whether you've split tables that wanted to stay together.
Many-to-many with attributes. The moment post_tags gains "tagged at" or "tagged by," the junction table stops being a mere connector and becomes an entity with meaning of its own. When that happens, give it a real name instead of a mechanical A_B compound.
Self-referencing. Threaded replies are the classic case. Add a parent_id to comments that references the same table, and allow NULL for top-level comments. We left it out of this walkthrough for simplicity, but on a real forum you'll meet it almost immediately.
Step 3: Settle attributes and keys
Don't overthink primary keys: use a surrogate id. Here's what happens if you make email the key — the moment a user changes their address, every reference to it wobbles with it.
The counterargument is "what about keys that never change, like ISBNs?" In practice, "never" breaks more often than you'd expect. A UNIQUE constraint on email keeps the duplicate protection without the fragility.
Foreign keys place themselves: always on the N side. Member 1 : N post means the posts table carries member_id.
Junction table keys come in two flavors. A composite primary key (post_id, tag_id) structurally blocks duplicate pairs; a separate id with a UNIQUE constraint on the pair makes the row easier to reference from other tables. Neither is wrong — decide by whether anything else will ever point at that row.
Data types can stay rough for now. Tuning lengths and indexes goes faster once the design has been through one full pass.
A design isn't finished until you've decided deletion
Once the relationships are drawn, there's a question to ask of every single one: "when the parent goes away, what happens to the children?"
If a member deletes their account, should their posts and comments vanish too? Careless ON DELETE CASCADE is behind more than a few incidents where deleting one member silently wiped their posts and every comment under them.
The safe default is RESTRICT, with CASCADE spelled out only where children genuinely must die with the parent. For a forum, soft deletion — flagging the member inactive instead of deleting the row — is a common alternative.
How to read crow's foot (IE) notation
There are more notations out there than anyone needs. In practice, you'll almost only ever meet IE — crow's foot. The symbols at the end of each line encode cardinality:
|— exactly one○— zero (optional)- Crow's foot (three prongs) — many
Real line endings combine two of these. |○ reads "zero or one," |< reads "one or more," and ○< reads "zero or more."
Practice on our forum:
- Member ─ post:
||on the member end,○<on the post end — "every post belongs to exactly one member; a member may have no posts or many" - Category ─ post: same shape — "every post sits in exactly one category"
Solid lines mark identifying relationships and dashed lines non-identifying ones — but reading the crow's feet correctly matters before that distinction does. For reference: coursework sometimes asks for Chen notation with its diamond relationship symbols, and Oracle-flavored documents use Barker. You'll rarely draw either, but being able to read them helps. The full symbol set and a three-notation comparison, with reading drills, is in our ERD notation guide.
One more habit worth starting now: keep two sets of names. A logical name for humans ("Member") and a physical name for the database (members) let designers and developers talk over the same diagram. It's why ERD tools ship separate Logical and Physical views.
Practice: the forum schema as DDL
Here's everything above as MySQL DDL. Copy it and run it as is.
CREATE TABLE members (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
nickname VARCHAR(50) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE posts (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
member_id BIGINT NOT NULL,
category_id INT NOT NULL,
title VARCHAR(200) NOT NULL,
content MEDIUMTEXT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_posts_member FOREIGN KEY (member_id) REFERENCES members (id),
CONSTRAINT fk_posts_category FOREIGN KEY (category_id) REFERENCES categories (id)
);
CREATE TABLE comments (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
post_id BIGINT NOT NULL,
member_id BIGINT NOT NULL,
content TEXT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_comments_post FOREIGN KEY (post_id) REFERENCES posts (id),
CONSTRAINT fk_comments_member FOREIGN KEY (member_id) REFERENCES members (id)
);A few of these choices are deliberate:
- Posts and comments use BIGINT ids while categories use INT — tables that grow at different speeds don't need the same key size.
- Post bodies are
MEDIUMTEXT— TEXT's 64KB cap arrives sooner than you'd think for long-form content. TEXT is plenty for comments. created_at DEFAULT CURRENT_TIMESTAMP— even if the application forgets, the record remembers.- MySQL (InnoDB) automatically indexes foreign key columns, so the everyday "posts by member" lookup is covered without extra work.
posts.member_id is the N-side foreign key of member 1 : N post. If any foreign key doesn't line up with a relationship from step 2, you've drawn a picture, not a design.
A self-review checklist before you move on
Design done? Put the diagram in front of you and check just these. Even without a reviewer, these six kinds of checks catch the big accidents.
- Does every table have a primary key?
- Is every foreign key on the N side of its relationship?
- Is any N:M still sitting there without a junction table? A comma-separated list inside one column is the warning sign.
- Are you storing anything you could compute?
- Is naming consistent — singular vs. plural, case, separators?
- Did you decide a deletion policy for every relationship?
Which tool should you draw with?
A whiteboard is a fine way to start. The trouble comes after.
Keep maintaining the diagram in a general-purpose drawing tool and it slowly drifts away from the real schema, until nobody trusts the picture anymore. That's why a dedicated ERD tool — column-level connections, DDL in and out — ends up being the comfortable choice, doubly so once the diagram needs to be shared and reviewed by a team.
If you already have DDL like the schema above, pasting it into WorksCove ERD builds the forum diagram for you.
Before building this tool, we managed everything in two places ourselves — a drawing tool for the diagram, a spreadsheet for the specs. A few releases in, the picture and the schema started telling different stories, and every meeting included someone saying "ignore the diagram, check the database."
A diagram that isn't the schema itself will drift, always. That's why WorksCove ERD is built around DDL.
FAQ
Which ERD notation should I use?
IE (crow's foot) notation is the de facto standard in practice. Unless a course specifically requires Chen notation, draw with crow's foot.
How do I model a many-to-many relationship?
Relational databases cannot store N:M directly. Create a junction table that holds both primary keys as foreign keys, and split the relationship into two 1:N relationships.
Should table names be singular or plural?
Consistency within the team matters more than the choice itself. Plural names (members, posts) are slightly more common in practice because they rarely collide with reserved words like user. Either way, never mix both styles in one project.
I already have a running database. Do I need to draw the ERD from scratch?
No. Export the DDL and paste it into an ERD tool to generate the diagram automatically. Tools with reverse engineering can also connect to the database and import the schema directly.
The order is the whole trick: entities → relationships → keys → notation. You've run the loop on a forum schema — the next rep is running it on your own requirements.
When you're ready for what comes next, two topics follow naturally: normalization, which decides how far to split your tables, and table specification automation, which turns the finished schema into documentation.