数据库理论-两个表之间的关系 [英] Database theory - relationship between two tables

问题描述

我有一个包含两个表的数据库-我们称它们为Foo和Bar.每个foo可能与任意数量的bar相关，并且每个bar可能与任意数量的foo相关.我希望能够通过一个查询来检索与特定栏相关联的foo，以及与特定foo相关联的栏.

I have a database with two tables - let's call them Foo and Bar. Each foo may be related to any number of bars, and each bar may be related to any number of foos. I want to be able to retrieve, with one query, the foos that are associated with a certain bar, and the bars that are associated with a certain foo.

我的问题是，记录这些关系的最佳方法是什么?我是否应该有一个单独的表来记录每个关系的记录(例如，两列foo和bar)? foo表中是否应有一列以列的形式显示，反之亦然?我还有其他选择吗?

My question is, what is the best way of recording these relationships? Should I have a separate table with records of each relationship (e.g. two columns, foo and bar)? Should the foo table have a column for a list of bars, and vice versa? Is there another option that I'm overlooking?

推荐答案

这称为多对多关系. 标准"解决方案是建立第三个表，并使用每行中每个表中存在关系的主键.

That's called a many-to-many relationship. The "standard" solution is to set up a third table, with the primary key from each table in each row where there is a relationship.

第三个表称为联结表. Wikipedia的连接表": http://en.wikipedia.org/wiki/Junction_table

The third table is called a junction table. "Junction table" from Wikipedia: http://en.wikipedia.org/wiki/Junction_table

例如:

Foo
UID
Col1
Col2

Bar
UID
Col1
Col2

Foo_Bar
UID
Foo_UID
Bar_UID

因此，在上面，可能有很多foo和很多小节.每个与一个小节相关的foo和每个与一个小节相关的bar都将存在于Foo_Bar表中.要获取与给定栏相关的所有foo，可以使用以下SQL:

So, in the above, there could be many foos and many bars. Each foo that relates to a bar and each bar that relates to a foo would exist in the Foo_Bar table. To get all the foos that relate to a given bar, you could use the following SQL:

select *
from foo
where uid in (
    select foo_uid
    from foo_bar
    where bar_uid=<some bar uid>)

(找不到该问题的确切重复，但以下问题围绕该主题展开.)

(Didn't find any exact dupes of this question, but the following questions expand on the topic.)

许多表设计问题
多对多关系设计-交叉表设计

这篇关于数据库理论-两个表之间的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！