Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – What are the best practices for a many-many relationship?

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

Tags:

2

Answers


  1. 0

    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?

    CREATE TABLE person(
    id int AUTO_INCREMENT,
    name varchar(255),
    role varchar(255),
    PRIMARY KEY(ID)
);

CREATE TABLE location(
    id int AUTO_INCREMENT,
    name varchar(255),
    PRIMARY KEY(ID)
);

CREATE TABLE person_location(
    id_person int,
    id_location int,
    PRIMARY KEY(id_person, id_location)
);

    And here are examples how to query the tables:

    SELECT *
FROM person
WHERE id IN
(
  SELECT pl.id_person
  FROM person_location pl
  JOIN location l ON l.id = pl.id_location
  WHERE l.name = 'Rome'
);

    or

    SELECT *
FROM person
WHERE id IN
(
  SELECT pl.id_person
  FROM person_location pl
  WHERE pl.id_location = (SELECT l.id FROM location l WHERE l.name = 'Rome')
);

    and

    SELECT p.name AS person_name, l.name AS location_name
FROM person p
JOIN person_location pl ON pl.id_person = p.id
JOIN location l ON l.id = pl.id_location;
    Login or Signup to reply.
  2. 0

    this approach lets every user have only one compny and also no duplicatev roles

    CREATE TABLE person(
    id int AUTO_INCREMENT,
    name varchar(255),
    role varchar(255),
    PRIMARY KEY(ID)
  ,
CONSTRAINT uc_name UNIQUE (name)
);

    CREATE TABLE location(
    id int AUTO_INCREMENT,
    name varchar(255),
    PRIMARY KEY(ID)
);


    CREATE TABLE role(
    id int AUTO_INCREMENT,
    name varchar(255),
    PRIMARY KEY(ID)
);


    create table person_role
(id_role int, id_person int, PRIMARY KEY(id_role,id_person ),
  
    FOREIGN KEY fkperson(id_person)
        REFERENCES person(id)
      ,
    FOREIGN KEY fk_role(id_role)
        REFERENCES role(id)
)

    create table person_location
(id_loaction int, id_person int, PRIMARY KEY(id_person ),
  
    FOREIGN KEY fkperson(id_person)
        REFERENCES person(id)
      ,
    FOREIGN KEY fk_locationry(id_loaction)
        REFERENCES location(id)
)

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search