Take these tables for example.
以这些表格为例。
Item
id
description
category
Category
id
description
An item can belong to many categories and a category obviously can be attached to many items.
一个项目可以属于许多类别，一个类别显然可以附加到许多项目。
How would the database be created in this situation? I'm not sure. Someone said create a third table, but do I need to do that? Do I literally do a
在这种情况下如何创建数据库？我不确定。有人说创建了第三张桌子，但是我需要这样做吗？我真的会做一个
create table bla bla
创建表bla bla
for the third table?
对于第三张桌子？
3 个解决方案
#1
4
Yes, you need to create a third table with mappings of ids, something with columns like:
是的，您需要创建一个带有ids映射的第三个表，其中包含以下列：
item_id (Foreign Key)
category_id (Foreign Key)
edit: you can treat item_id and category_id as a primary key, they uniquely identify the record alone. In some applications I've found it useful to include an additional numeric identifier for the record itself, and you might optionally include one if you're so inclined
编辑：您可以将item_id和category_id视为主键，它们可以单独唯一地标识记录。在某些应用程序中，我发现为记录本身包含一个额外的数字标识符很有用，如果您愿意，可以选择包含一个
Think of this table as a listing of all the mappings between Items and Categories. It's concise, and it's easy to query against.
可以将此表视为项目和类别之间所有映射的列表。它简洁明了，很容易查询。
edit: removed (unnecessary) primary key.
编辑：删除（不必要的）主键。
#2
3
Yes, you cannot form a third-normal-form many-to-many relationship between two tables with just those two tables. You can form a one-to-many (in one of the two directions) but in order to get a true many-to-many, you need something like:
是的，你不能只用这两个表在两个表之间形成第三范式的多对多关系。你可以形成一对多（在两个方向中的一个方向），但为了获得真正的多对多，你需要这样的东西：
Item
id primary key
description
Category
id primary key
description
ItemCategory
itemid foreign key references Item(id)
categoryid foreign key references Category(id)
You do not need a category in the Item table unless you have some privileged category for an item which doesn't seem to be the case here. I'm also not a big fan of introducing unnecessary primary keys when there is already a "real" unique key on the joining table. The fact that the item and category IDs are already unique means that the entire record for the ItemCategory table will be unique as well.
您不需要Item表中的类别，除非您对某个项目具有某些特权类别，而这似乎不是这种情况。当连接表上已经存在“真正的”唯一键时，我也不是引入不必要的主键的忠实粉丝。项目和类别ID已经是唯一的这一事实意味着ItemCategory表的整个记录也将是唯一的。
Simply monitor the performance of the ItemCategory table using your standard tools. You may require an index on one or more of:
只需使用标准工具监控ItemCategory表的性能即可。您可能需要以下一项或多项的索引：
- itemid
- 的itemid
- categoryid
- 类别ID
- (itemid,categoryid)
- （商品ID，类别ID）
- (categoryid,itemid)
- （类别ID，项ID）
depending on the queries you use to join the data (and one of the composite indexes would be the primary key).
取决于您用于加入数据的查询（其中一个复合索引将是主键）。
The actual syntax for the entire job would be along the lines of:
整个工作的实际语法将是：
create table Item (
id integer not null primary key,
description varchar(50)
);
create table Category (
id integer not null primary key,
description varchar(50)
);
create table ItemCategory (
itemid integer references Item(id),
categoryid integer references Category(id),
primary key (itemid,categoryid)
);
There's other sorts of things you should consider, such as making your ID columns into identity/autoincrement columns, but that's not directly relevant to the question at hand.
您应该考虑其他类型的事情，例如将您的ID列设置为标识/自动增量列，但这与手头的问题没有直接关系。
#3
2
Yes, you need a "join table". In a one-to-many relationship, objects on the "many" side can have an FK reference to objects on the "one" side, and this is sufficient to determine the entire relationship, since each of the "many" objects can only have a single "one" object.
是的，你需要一个“连接表”。在一对多关系中，“多”侧的对象可以对“一”侧的对象具有FK引用，这足以确定整个关系，因为每个“很多”对象只能有一个“一”对象。
In a many-to-many relationship, this is no longer sufficient because you can't stuff multiple FK references in a single field. (Well, you could, but then you would lose atomicity of data and all of the nice things that come with a relational database).
在多对多关系中，这已不再足够，因为您无法在单个字段中填充多个FK引用。 （好吧，你可以，但是你会丢失数据的原子性和关系数据库带来的所有好东西）。
This is where a join table comes in - for every relationship between an Item and a Category, the relation is represented in the join table as a pair: Item.id x Category.id.
这是连接表的来源 - 对于Item和Category之间的每个关系，该关系在连接表中表示为一对：Item.id x Category.id。
#1
4
Yes, you need to create a third table with mappings of ids, something with columns like:
是的，您需要创建一个带有ids映射的第三个表，其中包含以下列：
item_id (Foreign Key)
category_id (Foreign Key)
edit: you can treat item_id and category_id as a primary key, they uniquely identify the record alone. In some applications I've found it useful to include an additional numeric identifier for the record itself, and you might optionally include one if you're so inclined
编辑：您可以将item_id和category_id视为主键，它们可以单独唯一地标识记录。在某些应用程序中，我发现为记录本身包含一个额外的数字标识符很有用，如果您愿意，可以选择包含一个
Think of this table as a listing of all the mappings between Items and Categories. It's concise, and it's easy to query against.
可以将此表视为项目和类别之间所有映射的列表。它简洁明了，很容易查询。
edit: removed (unnecessary) primary key.
编辑：删除（不必要的）主键。
#2
3
Yes, you cannot form a third-normal-form many-to-many relationship between two tables with just those two tables. You can form a one-to-many (in one of the two directions) but in order to get a true many-to-many, you need something like:
是的，你不能只用这两个表在两个表之间形成第三范式的多对多关系。你可以形成一对多（在两个方向中的一个方向），但为了获得真正的多对多，你需要这样的东西：
Item
id primary key
description
Category
id primary key
description
ItemCategory
itemid foreign key references Item(id)
categoryid foreign key references Category(id)
You do not need a category in the Item table unless you have some privileged category for an item which doesn't seem to be the case here. I'm also not a big fan of introducing unnecessary primary keys when there is already a "real" unique key on the joining table. The fact that the item and category IDs are already unique means that the entire record for the ItemCategory table will be unique as well.
您不需要Item表中的类别，除非您对某个项目具有某些特权类别，而这似乎不是这种情况。当连接表上已经存在“真正的”唯一键时，我也不是引入不必要的主键的忠实粉丝。项目和类别ID已经是唯一的这一事实意味着ItemCategory表的整个记录也将是唯一的。
Simply monitor the performance of the ItemCategory table using your standard tools. You may require an index on one or more of:
只需使用标准工具监控ItemCategory表的性能即可。您可能需要以下一项或多项的索引：
- itemid
- 的itemid
- categoryid
- 类别ID
- (itemid,categoryid)
- （商品ID，类别ID）
- (categoryid,itemid)
- （类别ID，项ID）
depending on the queries you use to join the data (and one of the composite indexes would be the primary key).
取决于您用于加入数据的查询（其中一个复合索引将是主键）。
The actual syntax for the entire job would be along the lines of:
整个工作的实际语法将是：
create table Item (
id integer not null primary key,
description varchar(50)
);
create table Category (
id integer not null primary key,
description varchar(50)
);
create table ItemCategory (
itemid integer references Item(id),
categoryid integer references Category(id),
primary key (itemid,categoryid)
);
There's other sorts of things you should consider, such as making your ID columns into identity/autoincrement columns, but that's not directly relevant to the question at hand.
您应该考虑其他类型的事情，例如将您的ID列设置为标识/自动增量列，但这与手头的问题没有直接关系。
#3
2
Yes, you need a "join table". In a one-to-many relationship, objects on the "many" side can have an FK reference to objects on the "one" side, and this is sufficient to determine the entire relationship, since each of the "many" objects can only have a single "one" object.
是的，你需要一个“连接表”。在一对多关系中，“多”侧的对象可以对“一”侧的对象具有FK引用，这足以确定整个关系，因为每个“很多”对象只能有一个“一”对象。
In a many-to-many relationship, this is no longer sufficient because you can't stuff multiple FK references in a single field. (Well, you could, but then you would lose atomicity of data and all of the nice things that come with a relational database).
在多对多关系中，这已不再足够，因为您无法在单个字段中填充多个FK引用。 （好吧，你可以，但是你会丢失数据的原子性和关系数据库带来的所有好东西）。
This is where a join table comes in - for every relationship between an Item and a Category, the relation is represented in the join table as a pair: Item.id x Category.id.
这是连接表的来源 - 对于Item和Category之间的每个关系，该关系在连接表中表示为一对：Item.id x Category.id。