При создании навигационной системы категории для бизнес-каталога со многими отношениями многие понимают, что создание таблицы сопоставления является хорошей практикой.
Таблица категорий (CategoryId, CategoryName)
Бизнес-таблица (BusinessId, BusinessName)
Таблица сопоставления категорий (BusinessId, CategoryId)
Когда я присоединяюсь к таблице Category и таблице Business, чтобы создать таблицу сопоставления, это даст мне таблицу, которая содержит все возможные отношения бизнеса и категории?
У меня 800 категорий и 1000 компаний. Тогда это даст мне таблицу, содержащую 800 000 возможных отношений. Если да, то как бы я сосредоточился только на существующих отношениях? Должен ли я пройти через все списки (800 000), обозначив их как истинные или ложные?
Я очень запутался в этом, поэтому любая помощь была бы очень оценена.
При использовании отношений «многие-ко-многим» единственный реальный способ справиться с этим – с таблицей сопоставления.
Допустим, у нас есть школа с преподавателями и студентами, у студента может быть несколько учителей и наоборот.
Итак, мы делаем 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 будет столько записей, сколько ссылок (НЕ 20×1000, но только для реальных ссылок).
выбор
Вы выбираете, например, учащихся на одного учителя, используя:
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 , и ничего не вернет, если эти данные уже находятся в 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)
Когда я присоединяюсь к таблице Category и таблице Business, чтобы создать таблицу сопоставления, это даст мне таблицу, которая содержит все возможные отношения бизнеса и категории?
Да.
Должен ли я пройти через все списки (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
вы ставите только реальные отношения в таблицу сопоставления. поэтому, в среднем, бизнес находится в двух категориях, тогда в вашем примере в таблице сопоставления будет только 2000 записей, а не 800 000
«Когда я присоединяюсь к таблице Category и таблице Business для создания таблицы сопоставления, вы не присоединяетесь к этим двум таблицам, чтобы создать таблицу сопоставления. Вы создаете фактическую физическую таблицу.
Вы должны использовать таблицы сопоставления, когда пытаетесь моделировать отношения «многие-ко-многим» или «один ко многим».
Например, в приложении адресной книги конкретный контакт может принадлежать нулю, одной или нескольким категориям. Если вы установили свою бизнес-логику, что контакт может принадлежать только одной категории, вы должны определить свой контакт следующим образом:
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;