MySQL: понимание таблиц отображения

Ответ 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)

Ответ 2

Когда я присоединяюсь к таблице Category и Бизнес-таблица для создания сопоставления стол, это даст мне таблицу который содержит все возможные виды деятельности и отношения категории?

Да.

Должен ли я пройти через все списки (800 000), обозначая их как истинные или ложные?

Нет, вам нужно использовать ON -clause для установки условий соединения.

SELECT <columns> FROM categories as c 
INNER JOIN mapping AS m
    ON m.CategoryId = c.CategoryId
INNER JOIN businesses as b
    ON m.BusinessId = b.BusinessId

Ответ 3

вы помещаете только реальные отношения в таблицу сопоставления. поэтому, в среднем, бизнес находится в двух категориях, тогда в вашем примере в таблице сопоставления будет всего 2000 записей, а не 800 000

"Когда я присоединяюсь к таблице Category и бизнес-таблице для создания таблицы сопоставления, вы не присоединяетесь к этим двум таблицам, чтобы создать таблицу сопоставления. Вы создаете фактическую физическую таблицу.

Ответ 4

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

Например, в приложении адресной книги конкретный контакт может принадлежать нулю, одной или нескольким категориям. Если вы установили свою бизнес-логику, что контакт может принадлежать только одной категории, вы должны определить свой контакт следующим образом:

Contact
--------------
contactid (PK)
name
categoryid (FK)

Category
--------------
categoryid (PK)
categoryname

Но если вы хотите разрешить контакту иметь более одного адреса электронной почты, используйте таблицу сопоставления:

Contact
--------------
contactid (PK)
name

Category
--------------
categoryid (PK)
categoryname

Contact_Category
--------------
contactid (FK)
categoryid (FK)

Затем вы можете использовать SQL для получения списка категорий, которым назначен контакт:

выберите a.categoryname из категории a, Contact b, Contact_Category c, где a.categoryid = c.categoryid и b.contactid = c.contactid и b.contactid = 12345;

select a.categoryname 
from Category a
inner join Contact_Category c on a.categoryid=c.categoryid
inner join Contact b on b.contactid=c.contactid
where b.contactid=12345;