Entity Relationship Diagrams: What They Are and How to Read Them
An entity relationship diagram (ERD) is a visual map of the data your system stores: the things it tracks (entities), the facts it records about them (attributes), and how those things connect (relationships). You use one when you need to agree on a schema before writing tables — for a new database, a migration, or a review of an existing one. You read one by starting at the entities, checking their keys, then following the lines to see how rows in one table point to rows in another.
The three building blocks
Entities
An entity is a category of thing you store data about — a customer, an order, a product. In a physical ERD it becomes a table. Entities are drawn as rectangles, usually labeled with a singular noun.
Attributes
Attributes are the columns of that table. Each one has a type (integer, varchar, timestamp, enum) and may be optional or required. In most diagram tools you list them inside the entity box, often with a marker for the primary key.
Relationships
A relationship is a line between two entities that says how their rows relate. The line carries two pieces of information: which entities are involved, and the cardinality — how many rows on each side can pair with how many on the other.
Cardinality, in plain terms
Cardinality is the part people misread most often. Read each line as a sentence from one entity to the other.
| Notation on the line | Meaning | Example |
|---|---|---|
| One-to-one (1:1) | Each row on the left matches at most one row on the right | users ↔ user_profiles |
| One-to-many (1:n) | One row on the left matches many on the right | users → orders |
| Many-to-many (n:n) | Many on both sides | orders ↔ products |
A many-to-many relationship cannot be stored directly in a relational table. It is normally resolved with a junction (associative) table that holds a foreign key to each side — for example order_items linking orders and products.
Keys and how they show up
- Primary key (PK): the attribute that uniquely identifies each row in an entity. Every table should have one.
- Foreign key (FK): an attribute in one entity that references the primary key of another. The FK is what makes a relationship real in the database rather than just a line on a drawing.
- Composite key: a primary key made of two or more attributes together, common in junction tables.
When you read an ERD, find the PK first, then look for FKs. The FK tells you the direction of the dependency: the table holding the FK depends on the table it points to.
Reading a simple example
Take a small schema with users, orders, and order_items:
usershasid(PK),username,email.ordershasid(PK),user_id(FK →users.id),status,created_at.order_itemshasorder_id(FK →orders.id) andproduct_id(FK →products.id), together forming a composite PK.
Reading the lines: one user has many orders (1:n), one order has many order items (1:n), and each order item points to one product. The order_items table exists specifically to turn the many-to-many between orders and products into two one-to-many relationships.
From diagram to actual tables
An ERD is a specification, not the database itself. Once the entities, attributes, keys, and cardinalities are settled, the diagram can be translated into DDL — CREATE TABLE statements with the right column types, primary keys, and foreign key constraints.
Tools like drawDB do this translation for you: you build the diagram on a canvas, and it generates DDL for MySQL, PostgreSQL, SQLite, MariaDB, SQL Server, or Oracle. The same tool can go the other direction — import an existing DDL script and it rebuilds the diagram, which is useful when you inherit a schema and need to see its shape. It also imports and exports DBML, so diagrams can move between tools as text.
The practical value of generating DDL from the diagram is consistency: the foreign keys and types in the script match what you drew, so the diagram and the database do not drift apart.
Notation styles you will encounter
| Style | Looks like | Where you see it |
|---|---|---|
| Crow's foot | Lines ending in a three-pronged "foot" for "many", a bar for "one" | Most modern schema tools |
| Chen | Diamonds for relationships, ovals for attributes | Academic and textbook diagrams |
| UML class diagram | Boxes with attribute lists, lines with multiplicity labels like 1..* |
Software design, object modeling |
The notation changes the symbols, not the underlying meaning. Crow's foot is the most common in database tooling because it shows cardinality compactly at the ends of each line.
When an ERD is the right tool — and when it is not
Use an ERD when the question is what data do we store and how does it relate. It is the right artifact for designing a new schema, reviewing a proposed change, onboarding someone onto an unfamiliar database, or documenting a data model for a team.
It is less useful when the question is about query performance, access patterns, or runtime behavior — an ERD says nothing about indexes, query plans, or how often a table is read. For those, you want query analysis or load testing, not a diagram. Similarly, if your data has no fixed structure (documents, events, graphs), a relational ERD may not describe it well; a different model is often clearer.