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 it Review. Besides the two foreign keys, a review has also other attributes: classification, comments, and submission date.
Let’s create the Review entity:
- In the Data tab, open the GoOutWebDataModel entity diagram;
- Drag the User system entity and the Place entity to the diagram;
- Right-click the diagram canvas and select ‘Add Entity’;
- Name the entity as ‘Review’;
- Drag the User.Id attribute to the Review;
- Drag the Place.Id attribute to the Review;
- Add the remaining attributes:
- Classification: Integer type;
- Comments: Text type;
- SubmittedOn: Date type.