MySQL: Понимание таблиц сопоставления

При использовании отношений "многие ко многим" единственный реалистичный способ справиться с этим - использовать таблицу сопоставления.

Допустим, у нас есть школа с учителями и учениками, у ученика может быть несколько учителей и наоборот.

Итак, мы составляем 3 таблицы

student
  id unsigned integer auto_increment primary key
  name varchar

teacher
  id unsigned integer auto_increment primary key
  name varchar

link_st
  student_id integer not null
  teacher_id integer not null
  primary key (student_id, teacher_id)

В таблице учащихся будет 1000 записей
В таблице учителей будет 20 записей
В таблице link_st будет столько записей, сколько есть ссылок (НЕ 20x1000, а только для фактического ссылки).

Выбор
Вы выбираете, например, учеников на одного преподавателя, используя:

SELECT s.name, t.name 
FROM student
INNER JOIN link_st l ON (l.student_id = s.id)   <--- first link student to the link-table
INNER JOIN teacher t ON (l.teacher_id = t.id)   <--- then link teacher to the link table.
ORDER BY t.id, s.id

Обычно здесь всегда следует использовать inner join.

Создание ссылки
Когда вы назначаете преподавателя ученику (или наоборот, это одно и то же). Вам нужно только сделать:

INSERT INTO link_st (student_id, teacher_id) 
   SELECT s.id, t.id 
   FROM student s 
   INNER JOIN teacher t ON (t.name = 'Jones')
   WHERE s.name = 'kiddo'

Это немного неправильное использование внутреннего соединения, но оно работает до тех пор, пока имена уникальны.
Если вы знаете идентификаторы, вы можете просто вставить их непосредственно из курс.
Если имена не уникальны, это будет ошибка и не должно использоваться.

Как избежать повторяющихся ссылок
Очень важно избегать повторяющихся ссылок, если они у вас есть, могут произойти всевозможные плохие вещи.
Если вы хотите предотвратить вставку повторяющихся ссылок в таблицу ссылок, вы можете объявить индекс unique для ссылки (рекомендуется)

ALTER TABLE link_st
  ADD UNIQUE INDEX s_t (student_id, teacher_id); 

Или вы можете выполнить проверку в инструкции insert (на самом деле не рекомендуется, но это работает).

INSERT INTO link_st (student_id, teacher_id) 
  SELECT s.id, t.id
  FROM student s
  INNER JOIN teacher t ON (t.id = 548)
  LEFT JOIN link_st l ON (l.student_id = s.id AND l.teacher_id = t.id)
  WHERE (s.id = 785) AND (l.id IS NULL)

Это приведет только к выбору 548, 785 , если эти данные еще не находятся в таблице link_st, и ничего не вернет, если эти данные уже находятся в link_st. Таким образом, он откажется вставлять повторяющиеся значения.

Если у вас есть таблица школ, это зависит от того, может ли ученик быть зачислен в несколько школ (маловероятно, но давайте предположим), а учителя могут быть зачислены в несколько школ. Очень возможно.

table school
  id unsigned integer auto_increment primary key
  name varchar

table school_members
  id id unsigned integer auto_increment primary key
  school_id integer not null
  member_id integer not null
  is_student boolean not null

Вы можете перечислить всех учащихся в школе, например итак:

SELECT s.name
FROM school i
INNER JOIN school_members m ON (i.id = m.school_id)
INNER JOIN student s ON (s.id = m.member_id AND m.is_student = true)