A many-to-many relationship happens when one entity has a one-to-many relationship with another entity, and vice-versa. For example, an
Author can write several
Books, and a
Book can be written by several
Authors. This kind of relationship is also known as N to M relationship.
Resolve this kind of relationships by adding a third entity, called junction entity. It must have at least two foreign keys, one to each entity in the relationship. Other attributes can also be added if needed (see the example).
To create a many-to-many relationship, follow these steps:
- Create a relationship entity;
- Add an attribute and set the data type to the identifier of the first entity;
- Add another attribute and set the data type to the identifier of the second entity.
If you want to ensure that the new entity has unique records (like an
Author can only write the same
Book once), then add a unique index to the two foreign keys.
You have a mobile application called GoOut, where end-users can find and review places like restaurants, hotels, etc. An end-user can review many places and a place can have reviews from many end-users, i.e. a many-to-many relationship. We will call these two entities
Place. Besides the two foreign keys, a review has also other attributes: classification, comments, and submission date.
Let's create the
- In the Data tab, open the GoOutWebDataModel entity diagram;
- Drag the User system entity and the
Placeentity to the diagram;
- Right-click the diagram canvas and select 'Add Entity';
- Name the entity as
- Drag the
User.Idattribute to the
- Drag the
Place.Idattribute to the
- Add the remaining attributes:
Classification: Integer type;
Comments: Text type;
SubmittedOn: Date type.