Many:many basics

from the Artful MySQL Tips List

You have tables named people and events, and you need to record citations, each of which refers to one person and one event. That's a many:many relationship between people and events.

You do this with a bridge or link table. The citations table needs one column for each table it links in a many:many relationship. Then for ever people-events combo you wish to record, you create one citations row:

drop table if exists citations,people,events;
create table people(id int primary key) ENGINE=INNODB;
create table events(id int primary key) ENGINE=INNODB;
create table citations(
  id int primary key auto_increment,
  peopleID int,
  eventID int,
  KEY(peopleID),
  KEY(eventID),
  FOREIGN KEY(peopleID) REFERENCES people(ID),
  FOREIGN KEY(eventID) REFERENCES events(ID)
) ENGINE=INNODB;
insert into people values(1),(2),(3);
insert into events values(1),(2);
insert into citations(peopleID,eventID) values(1,1),(1,2),(3,1);

Now to fetch all existing people-event citations, you can write...

SELECT p.ID AS person,e.ID AS event
FROM citations c
JOIN people p ON c.peopleID=p.id
JOIN events e ON c.eventID=e.id;

You aren't limited to two tables; any number of tables can be in a M:M relationship, so long as the bridge table has a column for each linked table.
Return to the Artful MySQL Tips page