SQL - 我应该使用联结表吗？

I am creating a new SQL Server 2008 database. I have two two tables that are related.

我正在创建一个新的SQL Server 2008数据库。我有两个相关的表。

The first table looks like this:

第一个表看起来像这样：

 BRANDS // table name
 BrandID // pk
 BrandName // varchar

The second table looks like this:

第二个表看起来像这样：

 MODELS // table name
 ModelID // pk
 ModelDescription // varchar

Every brand will have at least one model and every model will belong to only one brand.

每个品牌至少有一个型号，每个型号只属于一个品牌。

The question is, should I create a junction table like this

问题是，我应该创建这样的联结表

 BRANDS_MODELS // table name
 RecordID // pk
 BrandID
 ModelID

Or should I modify the MODELS table to include the BrandID like this

或者我应该修改MODELS表以包含像这样的BrandID

 MODELS // table name
 BrandID // 
 ModelID // pk
 ModelDescription // varchar

Thanks!

谢谢！

3 个解决方案

#1

If a model belongs to only one brand then you can put the FK to brand on the model table (your second approach). The first way, with the junction table, is for a many-to-many relation.

如果模型只属于一个品牌，那么您可以将FK放在模型表上（第二种方法）。使用联结表的第一种方式是多对多关系。

#2

Based on what you've said so far, I would leave out the junction table and use an ordinary foreign key in the MODELS table.

根据你到目前为止所说的，我将省略联结表并在MODELS表中使用普通的外键。

But if a model could move brands and you needed to maintain a current junction and history, a junction table has advantages over keeping history of the entire MODELS row when just a foreign key changes. Also if other things exist which might be associated with the relationship "entity" more than the MODEL entity it might make more sense to have a junction table. You can always make a unique constraint on ModelID in the junction table to ensure that the same model is not linked to multiple brands. So although a junction table is required to effectively implement a many-to-many relationship, it can also be useful for one-to-many relationships where that relationship itself has attributes.

但是，如果模型可以移动品牌并且您需要维护当前的交汇点和历史记录，那么只有外键更改时，联结表比保留整个MODELS行的历史记录更具优势。此外，如果存在可能与关系“实体”相关联的其他事物而不是MODEL实体，则可能更有意义地建立联结表。您始终可以对联结表中的ModelID进行唯一约束，以确保相同模型未链接到多个品牌。因此，虽然需要联结表来有效地实现多对多关系，但它对于该关系本身具有属性的一对多关系也很有用。

#3

Junction tables are used for many-to-many relationships which does not seem to be a good fit here.

连接表用于多对多关系，这似乎不适合这里。

For example, you would not want to enable the creation of a Honda Civic and a Toyota Civic. That's an example of car's make/model relationship but should fit your brand/model relationship.

例如，您不希望创建本田思域和丰田思域。这是汽车制造/车型关系的一个例子，但应该适合您的品牌/车型关系。

#1