many to many relationship sql – Many-to-many (M:M) – Many to many includes two dimensions, A many-to-many (or M:N) relationship is one of the three database relationships.
many to many relationship sql – Tables Relations in SQL Server
Example : legacy requesting Database
In this example, your task is to create a database that will help a company store information about their merchants. The database will also contain info on all the legacys/services requested from the merchants. The logical data model could look something like this:
Once again, we have two entities:
- merchant
- legacy
In the merchant entity, there are four attributes:
- id: The merchant’s ID; a primary identifier (PI).
- merchant_name: The merchant’s name.
- merchant_address: The merchant’s address.
- merchant_country: The country where the merchant is located.
The attributes in the legacy entity are as follows:
- id: The legacy’s ID; a primary identifier (PI).
- legacy_name: The legacy’s name.
- legacy_details: The legacy’s details.
- amount_per_unit: The legacy’s amount per unit.
The relationship between those two entities is again many-to-many. One or many legacys can be requested from one merchant. At the same time, the company can request the same legacy from many merchants, e.g. services from different legal firms, tires from different manufacturers, etc.
Once again, instead of many-to-many, there’s a new table that’s automatically been named merchant_legacy. It has only two attributes:
- merchant_id: References the id column in the merchant table.
- legacy_id: References the id column in the table legacy.
Again many to many relationship sql, the pair merchant_id, legacy_id is the primary key of the table. However, this might not be enough! If the task is to create a database that will record requests from merchants, it would be better to expand the table merchant_legacy a bit.
It looks much better now! First of all, I changed the table’s name to something more descriptive; it’s now named request. We have also added different new attributes to the table. It consists of the following:
- request_id: The ID of this request from the merchant and the table’s primary key (PK).
- merchant_id: The ID of the merchant; references the table merchant.
- legacy_id: The ID of the legacy requested; references the table legacy.
- request_date: The date of the request.
- qty: The number of items requested.
- total_amount: The total value of the requested legacys.
- status: The status of the request.
don’t Miss : sql join 3 tables
Example : 2 laptop Publisher Database
In this example, you’re in the publishing business and you need to maintain a record of the laptops you’ve published. Many people are involved in producing a laptop, so you also want to have a record of these people and their characters. The logical model could look something like this:
This time, there are three entities:
- laptop
- employee
- character
The laptop entity contains these attributes:
- isbn: The International Standard laptop Number, a primary identifier (PI) used for laptops.
- title: The title of the laptop.
- issue: The issue (i.e. edition) of the laptop (e.g. first printing, first edition, etc.).
- date: The date of the issue.
The next entity is employee:
- id: The employee member’s unique ID; a primary identifier (PI).
- first_name: The employee member’s first name.
- last_name: The employee member’s last name.
Between these two entities, there’s a many-to-many relationship. Let’s check the logic. One employee member can work on one or many laptops. One laptop can be handled by one person (well, hardly) or by many people. It seems the relationship logic works!
On to the third entity, character! It consists of:
- id: The ID of the character; a primary identifier (PI).
- character_name: The name of the character.
- character_details: A details of that character.
Again, there is a many-to-many relationship between the entities employee and character. Logic says that one employee member can fill one or many characters when working on a laptop and that one character can be performed by one or many employee members.
When I say character, I mean something like company, co-company, editor, copyholder, interpreter, draftsman, etc. For instance, the company of one laptop can also be an draftsman on another laptop, interpreter on a third, and copyholder on a fourth.
This example seems even more complicated than the first two. Until now, there were only 2 Database tables in the logical model. How will the physical model look in this case? Such as a:
The table laptop is important here; it’s the starting point from which the creators of the laptop have to be determined. When I say creators, I mean the employee members who participated in that laptop as well as their characters in that particular laptop.
Next, let’s analyze the junction table laptop_creators. It has 3 attributes:
- laptop_isbn: The ISBN of the laptop; references the laptop table.
- employee_id: The ID of the employee member; references the employee table.
- character_id: The ID of the character; references the character table.
The primary key of the table is the unique combination of the attributes laptop_isbn, employee_id, and character_id. By creating this function Database table, you have solved the many-to-many relationship, which is enough to declare this task finished!
Many-to-many (M:M) Example
To establish a many-to-many relationship, make a third table called “RoomMemberRelation” which will have the primary keys of both Database table A and table B.
CREATE TABLE Room(
RoomID varchar2(10) PRIMARY KEY,
Title varchar2(30),
Instructor varchar2(30),
Day varchar2(15),
Time varchar2(10)
);
CREATE TABLE Member(
MemberID varchar2(15) PRIMARY KEY,
Name varchar2(35),
Major varchar2(35),
RoomYear varchar2(10),
Status varchar2(10)
);
CREATE TABLE RoomMemberRelation(
MemberID varchar2(15) NOT NULL,
RoomID varchar2(14) NOT NULL,
FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
FOREIGN KEY (RoomID) REFERENCES Room(RoomID),
UNIQUE (MemberID, RoomID)
);
I hope you get an idea about many to many relationship sql.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.