php - MySQL:了解映射表

Question

在为具有多对多关系的业务目录构建类别导航系统时，我理解创建映射表是一种很好的做法。

类别表( CategoryId, CategoryName )
业务表(BusinessId、BusinessName)
类别映射表(BusinessId、CategoryId)

当我加入 Category 表和 Business 表来创建映射表时，这会给我一个包含所有可能的业务和类别关系的表吗？

我有 800 个类别和 1000 个商家信息。那会不会给我一个包含 800,000 个可能关系的表格。如果是这样，我将如何只关注现有的关系？我是否必须浏览所有列表(800,000)，将它们标记为真或假？

我对此感到非常困惑，因此非常感谢任何帮助。

Answer 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条记录
teacher表会有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'

这是对内部连接的一种误用，但只要名称是唯一的，它就可以工作。
如果您知道 ID，当然可以直接插入这些 ID。
如果名称不是唯一的，这将是失败，不应使用。

如何避免重复链接
避免重复链接非常重要，如果您有这些链接，将会发生各种不好的事情。
如果你想防止在你的链接表中插入重复的链接，你可以在链接上声明一个unique索引(推荐)

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

或者您可以在插入语句中进行检查(不是很推荐，但可以)。

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)