Ответ 1
При использовании отношений "многие ко многим" единственный реальный способ справиться с этим - это таблица сопоставления.
Допустим, у нас есть школа с преподавателями и студентами, у студента может быть несколько учителей и наоборот.
Итак, мы делаем 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'
Это немного неправильное использование внутреннего соединения, но оно работает до тех пор, пока имена уникальны.
Если вы знаете идентификаторы, вы можете просто вставить их прямо, конечно.
Если имена не уникальны, это будет fail и не должно использоваться.
Как избежать дублирования ссылок
Очень важно избегать дублирования ссылок, всевозможные плохие вещи произойдут, если у вас их есть.
Если вы хотите запретить вставлять повторяющиеся ссылки в вашу таблицу ссылок, вы можете объявить индекс 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)