How to Create an Entity Relationship Diagram: Step-by-Step Guide

An entity relationship diagram (ERD) is a visual map of the tables, columns, and relationships in a database, built from entities, attributes, and connecting lines with cardinality notation. It's the standard way database designers, backend engineers, and architects communicate schema design before a single [[[CREATE](https://www.draft1.ai/blog/how-to-create-a-network-topology-diagram-step-by-step-guide)](https://www.draft1.ai/blog/how-to-create-a-cloud-infrastructure-diagram-step-by-step-guide)](https://www.draft1.ai/blog/how-to-create-a-business-process-flowchart-with-decision-points-step-by-step-gui) TABLE statement gets written.

This guide walks through the process end to end: identifying entities, defining attributes, choosing relationship types and notation, and drawing the diagram itself — with a worked entity relationship diagram example for an e-commerce order system. Along the way we'll compare notation styles, cover common mistakes, and look at where an entity relationship diagram maker actually saves time versus where it gets in the way.

What Is an Entity Relationship Diagram, Exactly?

An ERD is a structured diagram showing entities (things you store data about), their attributes (properties of those things), and the relationships between them, typically expressed with cardinality (one-to-one, one-to-many, many-to-many).

The concept dates to Peter Chen's 1976 paper "The Entity-Relationship Model," and it's still the primary tool for relational database design nearly fifty years later — proof that the underlying idea (model data as things and connections before you model it as tables) hasn't been superseded, even as NoSQL and graph databases have expanded the toolbox. An ERD sits one level of abstraction above the actual SQL schema. It's not a replacement for a data dictionary or migration scripts; it's the design artifact that lets a team argue about the model cheaply, before code exists.

There are three common levels of ERD, and it's worth knowing which one you're drawing before you start:

• Conceptual ERD — high-level entities and relationships only, no attributes or keys. Used for early stakeholder alignment.
• Logical ERD — adds attributes, primary keys, and relationship cardinality, but stays database-agnostic (no data types).
• Physical ERD — includes actual column data types, constraints, indexes, and foreign key definitions — essentially a diagram of the real schema.

Most teams jump straight to something between logical and physical, which is fine for small projects but can cause churn on larger ones because you're solving naming and normalization problems at the same time as data-type problems.

Step 1: Identify Your Entities

An entity is any distinct object, person, event, or concept you need to store data about — it becomes a table in a relational database. Start by listing nouns from your requirements, not verbs.

For an e-commerce order system, the obvious entities are: Customer, Order, Product, OrderItem, Payment, and Category. Notice OrderItem — this is a classic associative entity (also called a junction or bridge entity) that resolves a many-to-many relationship between Order and Product. Beginners frequently miss these because they don't appear as an obvious noun in requirements; they emerge only once you ask "can one order contain multiple products, and can one product appear in multiple orders?" If the answer to both is yes, you need a junction table.

A useful gut-check: if a "thing" only ever has one attribute worth tracking, it's probably an attribute of another entity, not a separate entity itself. A ShippingAddress might just be columns on Customer or Order rather than its own table — unless customers need multiple saved addresses, in which case it earns entity status.

Step 2: Define Attributes and Keys

Every entity needs a primary key — a column or set of columns that uniquely identifies each row — plus the descriptive attributes that make the entity useful. Get this right before drawing anything, because retrofitting keys later usually means redrawing relationship lines.

For each entity, classify attributes into a few buckets:

• Primary key (PK): customer_id, order_id, product_id — usually a surrogate integer or UUID, not a natural key like email (emails change; IDs shouldn't).
• Foreign key (FK): a column referencing another entity's PK, e.g., Order.customer_id pointing to Customer.customer_id.
• Regular attributes: first_name, email, order_date, unit_price.
• Derived/computed attributes (optional, and often deliberately left out of physical ERDs): order_total if it's calculated from OrderItem rows rather than stored.

At the logical level, just name the attribute and mark PK/FK. At the physical level, add data types and constraints, e.g., email VARCHAR(255) NOT NULL UNIQUE, order_date TIMESTAMP DEFAULT now(), unit_price DECIMAL(10,2). This is also the point to decide on normalization: Third Normal Form (3NF) is the common default for OLTP systems, meaning every non-key attribute depends on the whole primary key and nothing but the key. Deliberately denormalizing (e.g., storing a product_name snapshot on OrderItem so historical orders don't change when a product is renamed) is a legitimate trade-off — just make it a documented decision, not an accident.

Step 3: Define Relationships and Cardinality

A relationship describes how two entities are associated, and cardinality specifies how many instances of one entity relate to how many instances of another. Getting cardinality wrong is the single most common ERD error, because it silently determines whether you need a foreign key on one side or a whole new junction table.

The three cardinality types:

• One-to-one (1:1): one Customer has one CustomerProfile. Rare in practice; often a sign the two entities should just be merged into one table, unless you're splitting for security (e.g., isolating PII) or performance (rarely-accessed large columns).
• One-to-many (1:N): one Customer places many Orders, but each Order belongs to exactly one Customer. This is the most common relationship type and is implemented with a foreign key on the "many" side.
• Many-to-many (M:N): one Order contains many Products, and one Product appears in many Orders. Relational databases can't represent M:N directly — you always resolve it with a junction table (OrderItem), which turns one M:N relationship into two 1:N relationships.

You should also mark optionality (sometimes called modality): is the relationship mandatory or optional on each side? A Payment might be optional on an Order (an order can exist before payment clears), while OrderItem.order_id is mandatory (an order item can't exist without an order). Optionality affects whether a foreign key column allows NULL.

Step 4: Choose a Notation

Cardinality and structure are usually communicated using one of a handful of standard notations. Picking one and applying it consistently matters more than which one you pick — mixed notation within a single diagram is a common source of confusion in team reviews.

For most modern engineering teams, Crow's Foot notation is the practical default: it's compact, widely understood, and every mainstream diagramming tool supports it. Chen notation is more verbose (relationships get their own diamond shapes) but is still useful in academic settings because it makes the relationship itself a first-class, labelable object — handy when a relationship carries its own attributes (e.g., an Enrollment relationship between Student and Course that also stores a grade).

Step 5: Draw the Diagram

With entities, attributes, keys, and relationships defined on paper or in a spreadsheet, the actual drawing step should be fast — you're transcribing decisions, not making new ones. Here's the worked example for the e-commerce system, described in Crow's Foot terms:

Customer ||--o{ Order : places
Order ||--|{ OrderItem : contains
Product ||--o{ OrderItem : "ordered as"
Order ||--o| Payment : "paid by"
Product }o--|| Category : "belongs to"

Reading this Mermaid-style ER syntax: ||--o{ means "exactly one, to zero-or-many" — one Customer places zero-or-many Orders. ||--|{ means "exactly one, to one-or-many" — every OrderItem must belong to an Order, and an Order must have at least one OrderItem. ||--o| means "one, to zero-or-one" — an Order may or may not yet have a Payment.

Each entity box, at the physical level, would list its columns:

Customer                Order                  OrderItem
--------                -----                  ---------
PK customer_id          PK order_id             PK order_item_id
   first_name           FK customer_id          FK order_id
   last_name               order_date           FK product_id
   email (unique)          status                  quantity
   created_at                                      unit_price

Note that unit_price is duplicated on OrderItem even though Product already has a price — this is the deliberate denormalization mentioned earlier, protecting historical order data from future price changes.

When you draw this by hand or in a tool, place the "one" side entities toward the outside and the junction/many-side entities in the middle — it keeps the crow's-foot lines from crossing and makes the diagram easier to review in a pull request or design doc.

Choosing an Entity Relationship Diagram Maker

The right tool depends on whether you're modeling from scratch, reverse-engineering an existing database, or generating diagrams from code/prompts as part of documentation. No single entity relationship diagram maker wins on every axis.

A few practical notes on trade-offs:

• Code-first tools like dbdiagram.io let you write a DSL (Table order { id int [pk] ... }) and get a diagram rendered automatically — good for version-controlling the diagram alongside migrations, since the source is plain text and diffable in Git.
• Native database tools (MySQL Workbench, pgAdmin, DataGrip) reverse-engineer directly from a live connection, which is the most accurate way to document an existing system, but they're tied to one database engine and less pleasant for greenfield brainstorming.
• General diagramming tools (Lucidchart, draw.io, Visio) are notation-flexible and great for stakeholder-facing diagrams but don't validate your model — nothing stops you from drawing an invalid relationship, so review is manual.
• AI/prompt-based generators, including natural-language-to-diagram tools, are increasingly used for the first draft: describe the domain in a sentence or two and get an initial entity-and-relationship layout to refine, which is faster than starting from a blank canvas but still needs a human pass for normalization and edge cases like the OrderItem junction table.

Whichever entity relationship diagram maker you pick, insist on two things: it should export to a plain format (SQL DDL, PNG, or SVG) so the diagram isn't trapped in a proprietary format, and it should let you version or timestamp revisions, because schemas change and a stale ERD is worse than no ERD.

Common Mistakes to Avoid

Most ERD problems fall into a handful of recurring patterns, and catching them at design time is far cheaper than fixing them after migrations have run in production.

• Modeling many-to-many without a junction table. Relational databases have no native way to store M:N directly; if you see a crow's foot on both ends of a single relationship line, that's a sign you need an associative entity.
• Using natural keys as primary keys. Emails, SSNs, and product SKUs can change or collide; surrogate keys (auto-increment integers or UUIDs) are safer defaults, with the natural key kept as a unique constraint if needed.
• Skipping optionality. Marking a relationship as just "one-to-many" without noting whether it's mandatory or optional leaves the question of NULL-ability unanswered, which becomes a bug found in production instead of in review.
• Over-normalizing early. Strict 3NF everywhere sounds correct but can create excessive joins for read-heavy systems; it's fine to note planned denormalizations directly on the diagram.
• Letting the diagram drift from the schema. An ERD that isn't regenerated or reviewed after migrations quickly becomes documentation debt — worse than no diagram, because people trust it.
• Mixing notations. Combining Chen diamonds with Crow's Foot forks in one diagram confuses reviewers who know one convention but not the other.

Key Takeaways

• An entity relationship diagram models entities, attributes, and relationships before physical schema design, and comes in conceptual, logical, and physical variants.
• Identify entities from nouns in your requirements, but watch for hidden associative entities (junction tables) needed to resolve many-to-many relationships.
• Every entity needs a primary key; foreign keys implement one-to-many relationships, and junction tables implement many-to-many ones.
• Crow's Foot notation is the most widely used standard for relational database ERDs today, though Chen notation remains common in academic contexts.
• Mark cardinality and optionality on every relationship — cardinality alone doesn't tell you whether a foreign key column can be NULL.
• Choosing an entity relationship diagram maker depends on your workflow: reverse-engineering favors native DB tools, greenfield design favors code-first or AI-assisted generators, stakeholder communication favors general diagramming tools.
• Treat the ERD as a living artifact — version it, export it in a portable format, and update it alongside schema migrations.

Frequently Asked Questions

What's the difference between an ER diagram and a database schema diagram?

An ER diagram is a design-level model of entities, attributes, and relationships, while a schema diagram (or physical ERD) shows the actual implemented tables, data types, and constraints. In practice, a physical-level ERD and a schema diagram often converge into the same artifact once a system is built.

Do I need special software to make an ERD, or can I draw it by hand?

You can absolutely sketch an ERD by hand or on a whiteboard, and many teams do this for the conceptual stage. Digital tools become valuable once you need to reverse-engineer from a live database, share with a distributed team, or keep the diagram in sync with schema migrations over time.

What is the difference between Crow's Foot and Chen notation?

Crow's Foot notation shows cardinality with forked line endings directly on the relationship line between two entity boxes, making it compact and common in modern relational database design. Chen notation represents relationships as separate diamond shapes with their own labels, which is more verbose but useful when a relationship itself needs attributes.

Can an entity relationship diagram model NoSQL databases?

Not directly in the traditional sense, since document and key-value stores don't enforce foreign keys or joins the way relational databases do. Teams still use ERD-like diagrams to model the conceptual relationships between document types, then translate that into embedding or referencing decisions within the NoSQL schema.

How detailed should attributes be in an early-stage ERD?

Early conceptual ERDs should skip attributes almost entirely and focus on entities and relationships to validate the overall model with stakeholders. Add attributes and keys once you move to the logical stage, and add data types and constraints only at the physical stage, to avoid getting bogged down in details before the structure is agreed on.

What's an associative entity, and when do I need one?

An associative entity (or junction table) resolves a many-to-many relationship by sitting between two entities and holding foreign keys to both, such as OrderItem connecting Order and Product. You need one whenever a single instance of Entity A can relate to multiple instances of Entity B, and vice versa.

How often should an ERD be updated?

An ERD should be updated whenever a schema migration adds, removes, or changes a table, column, or relationship, ideally as part of the same pull request. Some teams automate this by regenerating the diagram from the live database schema on each deploy, which prevents the diagram from drifting out of sync with reality.

Draw this in seconds with draft1. Describe your architecture in plain English and draft1 generates an editable AWS/cloud diagram plus documentation — no dragging boxes around. Try it free.