Many-to-Many Relationships in an ER Diagram (Junction Table in Visio)
An order has many products and a product is on many orders. Learn why a database can't store that directly, and how a junction entity fixes it, drawn in Visio with crow's foot notation.
Step by step
The problem
- Many-to-many. One order can have many products, and one product can be on many orders. That is a many-to-many relationship, and a database can't store it directly.
- Crow's Foot stencil. The diagram uses Visio's Crow's Foot Database Notation stencil, which you open from the Shapes pane.
The two entities
- The two entities. The first entity is Order, with the attributes OrderID, OrderDate and CustomerID. OrderID is its key. The second entity is Product, placed on the far side, with ProductID, Name and Price.
The junction entity
- The junction entity. The fix is a third entity in the middle, called a junction or associative entity. Each row is one product on one order, so it is named OrderLine. It holds OrderLineID, OrderID and ProductID.
Two one-to-many
- Two one-to-many. Use a Relationship connector to link Order to OrderLine. One order has zero or many lines, so the crow's foot goes on the OrderLine end. Then link Product to OrderLine the same way. One product appears on zero or many lines, so again the crow's foot goes on the OrderLine end.
- Done. Click away from the shapes to see the finished diagram. The one many-to-many relationship is now 2 simple one-to-many relationships, both pointing into OrderLine.
The rule
- The rule. The rule: whenever you find a many-to-many relationship, add a junction entity that holds both keys. It is also the right place for facts about the pair, such as the quantity of each product on an order.
Common mistakes
- Drawing a crow's foot on both ends between Order and Product. That is fine as a rough early sketch, but it can't be built as tables. Before you hand the model to developers, replace the line with a junction entity such as OrderLine.
- Putting the crow's foot on the wrong end. The crow's foot sits next to the entity that has many rows. Here that is OrderLine on both connectors, not Order or Product.
- Storing a list of product IDs in one Order field. A field like "P1, P4, P7" is hard to search, count or check. Give each product on an order its own row in OrderLine.
- Putting the quantity on Product or Order. Quantity belongs to one product on one order, so it goes on OrderLine. On Product it would be one number for every order, which makes no sense.
When a business analyst uses this
In a data requirements workshop, stakeholders say one requirement is covered by many test cases and one test case covers many requirements. The BA draws a junction entity called RequirementTestCase holding RequirementID and TestCaseID, plus a test result, so developers build a table the traceability report can use.
Questions people ask
What is a many-to-many relationship in an ER diagram?
It means one row of an entity can link to many rows of another entity, and the other way round too. For example, one order has many products and one product is on many orders.
How do you show a many-to-many relationship in an ER diagram?
In crow's foot notation, add a junction entity between the two entities that holds both of their keys. Then connect each entity to it with a one-to-many relationship, with the crow's foot on the junction end.
How is a one-to-many relationship represented in an ER diagram?
In crow's foot notation, the line has a crow's foot (3 prongs) at the many end and a single bar at the one end. Order to OrderLine is an example: one order, many lines.
