MySQL：了解映射表 [英] MySQL: Understanding mapping tables

问题描述

在构建具有多对多关系的业务目录的类别导航系统时，我明白创建映射表是个好习惯。

When building a category navigation system for a business directory with a many to many relationship, I understand that it is good practise to create a mapping table.

类别表（CategoryId，CategoryName）

业务表（BusinessId，BusinessName）

类别映射表（BusinessId，CategoryId）

Category Table ( CategoryId, CategoryName )
Business Table ( BusinessId, BusinessName )
Category Mapping Table ( BusinessId, CategoryId )

我加入Category表和Business表创建映射表，然后给我一个包含每一个可能的业务和类别关系的表？

When I join the Category table and Business table to create the mapping table would this then give me a table which contains every possible business and category relationship?

我有800个类别和1000个商家信息那么会给我一张含有80万可能关系的表。如果是这样，我怎么只关注存在的关系？我必须通过所有上市（80万），将其标记为真或假？

I have 800 categories and 1000 business listings. Would that then give me a table containing 800,000 possible relationships. If so how would I focus on only the relationships that exist? Would I have to go through all listings (800,000) marking them as true or false?

我一直对此感到困惑，所以任何帮助将不胜感激。

I have been getting really confused about this so any help would be much appreciated.

推荐答案

当使用多对多关系时，处理此事的唯一现实的方法是使用映射表。

When using many-to-many relationships, the only realistic way to handle this is with a mapping table.

说我们有一所有老师和学生的学校，一个学生可以有多个教师，反之亦然。

Lets say we have a school with teachers and students, a student can have multiple teachers and visa versa.

所以我们做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表将具有与链接相同的记录（NOT 20x1000，但仅适用于实际链接）。

The student table will have 1000 records
The teacher table will have 20 records
The link_st table will have as many records as there are links (NOT 20x1000, but only for the actual links).

选择

您选择例如每位老师使用的学生：

Selection
You select e.g. students per teacher using:

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 这里。

Normally you should always use an inner join here.

进行链接

当您分配一个学生的老师（或反之亦然，这是一样的）。
你只需要做：

Making a link
When you assign a teacher to a student (or visa versa, that's the same). You only need to do:

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，你可以直接插入那些。

如果这个名称不是唯一的，那么这将是一个失败，不应该被使用。

This is a bit of a misuse of an inner join, but it works as long as the names are unique.
If you know the id's, you can just insert those directly of course.
If the names are not unique this will be a fail and should not be used.

如何避免重复链接

避免重复链接非常重要，如果你有这些。

如果你想防止插入链接表的重复链接，你可以在链接上声明一个唯一的索引（推荐） / p>

How to avoid duplicate links
It's very important to avoid duplicate links, all sorts of bad things will happen if you have those.
If you want to prevent inserting duplicate links to your link table, you can declare a unique index on the link (recommended)

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

或者您可以在insert语句中进行检查（不是真的推荐，但它可以工作） / p>

Or you can do the check in the insert statement (not really recommended, but it works).

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中，该数据将不会返回。所以它将拒绝插入重复的值。

This will only select 548, 785 if that data is not already in the link_st table, and will return nothing if that data is in link_st already. So it will refuse to insert duplicate values.

如果你有一张桌子学校，这取决于一个学生是否可以注册在多个学校（不太可能，但是假设）和教师可以入读多所学校。非常可能。

If you have a table schools, it depends if a student can be enrolled in multiple schools (unlikely, but lets assume) and teachers can be enrolled in multiple schools. Very possible.

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

您可以列出学校中的所有学生：

You can list all students in a school like so:

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)

这篇关于MySQL：了解映射表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！