CODEWITHSUNDEEP
mysqlforeign-keysmany-to-manyrelational-databaseone-to-many
Dom
Dom

Reputation: 404

can a many to many relationship have another many to many relationship with another table?

Consider this case, a user can have many groups, groups can have many users

only people belonging to a particular group can have access to a car belonging to that group

So i am not sure how the tables will look like

below are the tables

-- Creating a new User
INSERT INTO Users (UserLogin, UserPassword, UserName)
VALUES ('SomeUser', 'SecretPassword', 'UserName');

-- Creating a new Groups
INSERT INTO Groups (GroupName, GroupDescription)
VALUES ('GroupName', 'GroupDescription');

-- Finally, updating the junction
INSERT INTO UserGroup (UserId, GroupId)
VALUES ('UserId', 'GroupId');

consider another table cars

INSERT INTO Cars (Name, Model)
VALUES ('SomeCar', 'Model');

only those people who belong to a group can have access to the carsS

So should cars have many to many relationships with UserGroups?

INSERT INTO CarUserGroup (UserGroupId, CarId)
VALUES ('UserGroupId', 'CarId');

OR Should group have a one-to-many relationship with cars? 

INSERT INTO Cars (Name, Model, GroupId)
VALUES ('GroupId','SomeCar', 'Model');

can u tell which approach is the best?

thanks

Upvotes: 0

Views: 115

Answers (2)

Kaustubh Kislay
Kaustubh Kislay

Reputation: 41

Since,

only people belonging to a particular group can have access to a car belonging to that group

So you should create a One-to-Many relationship between groups and cars. Now, why this should be done? The intuition behind this is that a user can have a car only if it belongs to a particular group, so the dependency of cars is on group. But since the car is a composite attribute of the group table, you make its owm table with a one-to-many relationship. Another point is, when you delete a group, all cars associated with this group should also be deleted ( this is optional, you can also have null foreign key, in which case it will be an orphan child if you dont want to delete these entries from cars table ).

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

If a given car can have only a single group relationship, then your second version in theory would work:

Cars (Name, Model, GroupId)

However, this approach becomes undesirable as soon as a car can be associated with more than one group. The reason is that then we would be duplicating the car's metadata:

SomeCar, SomeModel, Group1
SomeCar, SomeModel, Group2

In general, you would do better to go with the standard junction table approach, and have a table which keeps tracks only of the relationships between cars and groups:

SomeCar, Group1
SomeCar, Group2

Then, let the Cars table exist to store the metadata for each car, with one car occupying only a single record:

SomeCar, SomeModel, SomeOtherMetadata

Upvotes: 0

Related Questions