Entity Relationship Diagrams: A Complete Guide
An entity relationship diagram is the blueprint of a database before it exists. This guide covers entities, attributes, relationships, and cardinality, and how to turn a diagram into a real schema.
Every database that outlives its first month benefits from having been designed on paper first, and the tool for that design is the entity relationship diagram. An ERD captures the things your system stores, the facts you know about each of them, and how they connect, all before you write a single CREATE TABLE statement. Getting this right early is disproportionately valuable, because database structure is the hardest thing in a system to change once real data and real code depend on it.
This guide covers the three building blocks, entities, attributes, and relationships, and the cardinality notation that turns a vague sketch into a precise specification. It also covers the difference between a conceptual model and the physical schema you eventually build. You can draw ERDs directly in the editor at /diagrams, and the dedicated ERD tooling at /diagram-tools/erd-tool covers table-level modeling in more depth.
Entities: the things you store
An entity is a thing your system needs to remember: a Customer, a Product, an Order, an Invoice. In a diagram it is drawn as a rectangle labeled with a singular noun. Each entity typically becomes a table in the final database, and each instance of that entity, a specific customer, a specific order, becomes a row. Identifying the right entities is the first and most consequential modeling decision, because they define the shape of everything downstream.
A useful test for whether something should be an entity is whether you need to store multiple instances of it and refer to each one individually. A Customer qualifies because you have many customers and need to distinguish them. A customer's country of residence might not be a separate entity if you only ever store its name as a fact about the customer, though it becomes one the moment you need to store facts about countries themselves. This judgment, entity versus attribute, recurs constantly in data modeling and rewards thinking about how the data will actually be queried.
Attributes and keys
Attributes are the facts you know about an entity: a Customer has a name, an email, a signup date. In diagrams they are listed inside or beside the entity, often with their data types. One special attribute is the primary key, the attribute (or combination) that uniquely identifies each instance, usually shown with a key icon or underlined. A Customer's primary key might be a customer_id; no two customers share one, which is what lets other tables refer to a specific customer unambiguously.
When one entity refers to another, it does so through a foreign key: an attribute that holds the primary key value of a related entity. An Order has a customer_id foreign key pointing at the Customer that placed it. Foreign keys are the mechanism that turns a pile of separate tables into a connected model, and the relationships you draw in an ERD are ultimately realized as foreign keys in the physical schema. Distinguishing primary keys, foreign keys, and ordinary attributes is fundamental to reading any ERD.
Relationships and cardinality
Relationships are the lines connecting entities, and they state that instances of one entity are associated with instances of another. A Customer places Orders; an Order contains Products. The critical detail on each relationship is its cardinality, which specifies how many instances participate on each side. The three fundamental cardinalities are one-to-one, one-to-many, and many-to-many, and choosing correctly among them shapes the entire schema.
Most relationships in a typical system are one-to-many: one customer has many orders, one order has many line items, one author writes many posts. One-to-one relationships are rarer and often signal that two entities could be merged, though they are legitimate for optional or security-sensitive data split into a separate table. Many-to-many relationships, like students enrolling in many courses while each course has many students, cannot be represented with a single foreign key and require a junction table, a topic worth understanding thoroughly because it is where many beginners stumble. The cardinality you draw is not decoration; it dictates where foreign keys and junction tables go.
From diagram to schema
An ERD is a design, and the value comes from translating it faithfully into a database schema. The translation follows a small set of mechanical rules once the diagram is right, which is exactly why getting the diagram right first is worth the effort.
- Each entity becomes a table, and each attribute becomes a column with an appropriate data type.
- Each entity's primary key becomes the table's PRIMARY KEY constraint.
- A one-to-many relationship becomes a foreign key column on the "many" side pointing at the "one" side.
- A one-to-one relationship becomes a foreign key with a unique constraint, or the two entities merge into one table.
- A many-to-many relationship becomes a junction table with foreign keys to both entities, whose combination is the primary key.
- Required participation becomes a NOT NULL constraint on the relevant foreign key.
- Attribute constraints from the model, such as uniqueness or allowed values, become UNIQUE and CHECK constraints.