如果我在SQL中有两个表有多对多关系，我需要创建一个附加表吗？ [英] If I have two tables in SQL with a many-many relationship, do I need to create an additional table?

问题描述

以这些表为例。

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

create table bla bla

第三个表？

推荐答案

是的，你需要创建一个第三个表，列如下：

Yes, you need to create a third table with mappings of ids, something with columns like:

 item_id     (Foreign Key)
 category_id (Foreign Key)

编辑：您可以将item_id和category_id作为主键，它们唯一地标识记录。在某些应用程序中，我发现为记录本身添加一个附加的数字标识符很有用，如果你这样倾向，你可以选择包含一个。

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

表作为项目和类别之间的所有映射的列表。它很简单，很容易查询。

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.

这篇关于如果我在SQL中有两个表有多对多关系，我需要创建一个附加表吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！