Entity-Relationship Diagrams and Data Modeling

A one-to-many relationship connects one entity instance to multiple instances of another entity.

Programming

Interactive lab

Try it: From an ER Diagram to Tables

How the cardinality on an entity-relationship diagram decides the schema: each entity becomes a table with a primary key, a one-to-many relationship puts the foreign key on the many side, and a many-to-many relationship needs a junction table — and how sample rows that do not fit a too-narrow cardinality are rejected.

How it works

Ask the cardinality question from both sides: can one row here link to many rows there, and the other way round? Many on both sides is many-to-many.
Each entity becomes a table with an INTEGER PRIMARY KEY id.
One-to-many: the many side gets a foreign-key column referencing the one side's primary key. One-to-one: that foreign key is also UNIQUE.
Many-to-many: one foreign-key column holds one value per row, so a junction table with a foreign key to each side (and a composite primary key) stores every pairing — two one-to-many relationships.
Load the sample rows: a second link for the same row would need a second row with the same id, so SQLite rejects it (UNIQUE constraint failed); junction rows always fit.

Default run (17 steps): Model: Customer, Order, Product with Customer places Order (1:N) and Order contains Product (M:N). … Every active link is stored: the schema produced by the mapping rules can hold these rows.

Simplified: A toy relational engine in the browser, not SQLite itself: statements are built from the controls (no SQL text is parsed or executed) and tables have a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Three entities with one text attribute each and two relationships; participation (optional vs mandatory) is not modelled, so foreign keys allow NULL. The one-to-one rule (UNIQUE foreign key on the second entity) is the common textbook mapping.

Educational simulation

Loading the simulation…

From One Record to Many

A one-to-many relationship describes a connection in which one instance of an entity is associated with multiple instances of another entity. The relationship has two different sides: the one side and the many side. Learning to identify those sides is the first step toward placing keys correctly in a data model.

Key point

The central question is: can one instance on this side connect to multiple instances on the other side? If so, the side with the single instance is the one side, and the side with multiple associated instances is the many side.

Following the Relationship

Imagine two entities named Customer and Order. In this generated example, one Customer instance can be connected to several Order instances. The Customer instance is therefore on the one side, while the related Order instances are on the many side. The relationship does not mean that every customer must have several orders; it identifies the direction of the connection: one instance of the first entity can be associated with multiple instances of the second entity.

How does one instance of one entity connect to multiple instances of another entity?

Worked example

Identifying the two sides

A generated data model connects Customer and Order. One Customer instance is associated with Order 1, Order 2, and Order 3. Which entity is on the one side, and which is on the many side?

1
Count the Customer instances in the relationship
The example focuses on one Customer instance.
2
Count the associated Order instances
That Customer instance is associated with multiple Order instances.
3
Assign the sides
Customer is the one side because one Customer instance is involved. Order is the many side because multiple Order instances are involved.

Result

Customer is on the one side, and Order is on the many side.

Reading Crow's Foot Symbols

Crow's Foot notation visually represents cardinality, meaning how many instances participate at each end of a relationship. The one end uses a simple vertical line. The many end uses a crow's foot symbol made of three lines spreading outward from the relationship line. The spreading shape resembles a bird's foot and visually suggests many connections branching out.

Which end of the relationship represents one, which represents many, and how can the symbols be distinguished?

Symbol	Meaning	Side identified
Simple vertical line	Exactly one instance is involved at that end	One side
Crow's foot with three spreading lines	Multiple instances are involved at that end	Many side

The two Crow's Foot symbols used to identify the ends of a one-to-many relationship.

Best practice

Read the symbols before deciding where the keys go. First locate the simple vertical line and the crow's foot. Then label the corresponding entities as one and many. This prevents the common error of placing a foreign key on the wrong side.

Placing Primary and Foreign Keys

In a one-to-many relationship, the primary key is placed on the one side. A primary key uniquely identifies each instance of that entity. The foreign key is placed on the many side. It stores the primary key value from the one side, creating the link back to the related one-side instance.

Where does the primary key go, where does the foreign key go, and how does the foreign key connect each many-side record to one record on the one side?

Worked example

Connecting orders back to a customer

Use the generated Customer and Order relationship to determine where each key belongs.

1
Place the primary key
Customer is on the one side, so its primary key belongs in the Customer entity and uniquely identifies each Customer instance.
2
Place the foreign key
Order is on the many side, so its foreign key belongs in the Order entity.
3
Establish the reference
Each Order record stores the primary key value of the Customer instance to which it is related.

Result

The primary key is on Customer, and the foreign key is on Order. The foreign key connects each many-side Order record back to a Customer record on the one side.

Checking a Data Model

A reliable check follows the relationship in order: identify the one and many ends, locate the primary key on the one side, and locate the foreign key on the many side. The following mistakes reverse one of those steps.

Common mistake
Treating the crow's foot as the one side.
What it looks like
A learner sees the three spreading lines and labels that end one.
Why it fails
The crow's foot represents many. The simple vertical line represents one.
Do this instead
Use the vertical line to identify the one end and the crow's foot to identify the many end.
Common mistake
Putting the foreign key on the one side.
What it looks like
In the generated Customer and Order model, the learner places the foreign key in Customer.
Why it fails
The foreign key belongs on the many side and references the primary key on the one side.
Do this instead
Place the primary key in Customer, the one-side entity, and the foreign key in Order, the many-side entity.
Common mistake
Assuming the foreign key identifies the many-side entity by itself.
What it looks like
A learner treats the foreign key as the key that uniquely identifies every Order instance.
Why it fails
The source describes the primary key as the value that uniquely identifies each instance of its entity, while the foreign key establishes the link back to the one side.
Do this instead
Keep the roles distinct: the primary key uniquely identifies an entity instance, and the foreign key stores a referenced primary key value.

Practice the Key Placement

EASY

A generated diagram shows Department on the one side and Employee on the many side. Identify which entity receives the primary key and which entity receives the foreign key. Then explain what the foreign key connects.

Hints

Start with the relationship sides, not the key names.
The primary key belongs on the one side.
The foreign key belongs on the many side and references the primary key on the one side.

Your attempt (not saved)

What do you think happens?

For the generated Department and Employee relationship, where should the keys be placed?

Primary key in Department; foreign key in Employee
Foreign key in Department; primary key in Employee
Both keys in Department
Both keys in Employee

Reveal answer

Answer:

Primary key in Department; foreign key in Employee

Department is on the one side, so its primary key uniquely identifies each Department instance. Employee is on the many side, so its foreign key stores the primary key value that links each Employee record back to a Department record.

Essential Rules

1
A one-to-many relationship connects one instance of one entity to multiple instances of another entity.
2
In Crow's Foot notation, a simple vertical line identifies the one end.
3
A crow's foot with three spreading lines identifies the many end.
4
The primary key belongs on the one side and uniquely identifies each instance there.
5
The foreign key belongs on the many side and references the primary key on the one side.

Key Takeaways

One-to-many relationships connect one entity instance with multiple instances of another entity.
Crow's Foot notation uses a vertical line for one and a crow's foot for many.
The primary key is placed on the one side of the relationship.
The foreign key is placed on the many side and references the primary key on the one side.
Correct key placement helps maintain referential integrity and reliable connections between related data.

Related concepts

More concepts starting with E