Im wondering what the best practice is for my table layouts to be for a many to many realtionship.
To set the scene we have 2 tables. A table of people, and a table of locations defined as follows
CREATE TABLE person(
id int AUTO_INCREMENT,
company_id int,
name varchar(255),
role varchar(255),
PRIMARY KEY(ID)
);
CREATE TABLE location(
id int AUTO_INCREMENT,
name varchar(255),
PRIMARY KEY(ID)
);
person
filled with the following dummy data
id | company_id | name | role |
---|---|---|---|
1 | 1 | Jake | Role |
2 | 1 | Blake | Role |
3 | 2 | Larry | Role |
4 | 2 | Mike | Role |
5 | 1 | Hall | Role |
location
filled with the following dummy data
id | name |
---|---|
1 | Company A |
2 | Company B |
Currently each row on the person
table only has one company_id
associated with it.
Is it possible to have multiple people be accounted for at different locations?
For example…
Company A
currently has Jake, Blake, Hall
Company B
has Larry, Mike
.
But what if Company A
and Company B
both had Blake
?
Is the proper solution to create a new row in person
that has the exact same values as Blake
but just with a new company ID?. This would look like the following
id | company_id | name | role |
---|---|---|---|
1 | 1 | Jake | Role |
2 | 1 | Blake | Role |
3 | 2 | Larry | Role |
4 | 2 | Mike | Role |
5 | 1 | Hall | Role |
6 | 2 | Blake | Role |
Note the last row and the addition of an identical Blake entry except for id
and company_id
or is it possible to have a rolling list in person.company_id
to query against such that when you run either of the below, Blake
would show up in both
SELECT * FROM person WHERE person.company_id = 1
SELECT * FROM person WHERE person.comapny_id = 2
Blake would appear in both results
2
Answers
An m:n relation in an RDBMS should be modeled with a bridge table (aka association table). And you should be consistent in your naming. Is it a company or a location?
And here are examples how to query the tables:
or
and
this approach lets every user have only one compny and also no duplicatev roles
fiddle