数据库设计问题：歧视列与外键 [英] Database design question: discriminatory column vs foreign key

问题描述

所以让我们说我有一个管理客户的数据库。客户拥有人们可能期望的所有基本属性，然而，有特殊类型的客户称为经销商。分销商本身没有任何特殊的属性，除了它可以将产品分发到存储在市场表中的某些市场。

So let's say that I have a database for managing customers. A customer has all the basic properties that one might expect — however, there are special kinds of customers called distributors. A distributor doesn't have any special properties in and of itself, except that it can distribute products to certain markets which are stored in a market table.

我可以想到有两种方法来区分经销商和普通客户：

I can think of two ways to go about distinguishing distributors from "regular" customers:

1. 在客户表中创建一个名为customer_type的歧视列。该列将包含一个可以区分常规和经销商客户的值。

1. Create a discriminatory column in the customer table called "customer_type". This column would contain a value that would distinguish between "regular" and "distributor" customers.

• 这种方法的好处是它非常简单，如果需要，可以轻松添加新的客户类型。

• The benefit of this approach is that it's very simple, and new customer types can be added easily if needed.

这种方法的缺点是，在这种情况下，将市场和经销商联系在一起的市场表将真正将市场与客户联系起来。没有办法强制市场与分销商类型的客户相关联。

The drawback of this approach is that my market table which links markets and "distributors" would in this case would really just be linking markets to "customers". There is no way to enforce that a market is linked to a customer of the distributor type.

离开单独的客户表，并创建一个基本上只有一个id列和一个外键列的客户表的分销商表。

Leave the customers table alone, and create a distributor table that basically just has an id column and a foreign key column to the customer table.

• 这种方法的好处是，我的市场表现在可以链接到只包含经销商的分销商表。没有机会链接到不是分销商的客户（如果客户类型从分销商更改为常规可能会发生）。

• The benefit of this approach is that my market table can now link to the distributor table, which only contains distributors. There is no chance of linking to a customer that is not a distributor (as might occur if a customer "type" were changed from distributor to regular).

这种方法的缺点是复杂得多，添加新客户类型非常困难。

The drawback of this approach is that it's much more complicated, and adding new customer types is very difficult.

还有什么可能丢失，关于这个问题的意见？

What else might I be missing, and what are your opinions on this matter?

推荐答案

我会带有一个独立的表，用于具有自己的键/ ID的分发者。如果所有分销商都是客户，那么它可以将外键插入客户表。

I'd go with having a separate table for distributors with its own key/id. If all distributors are customers then it can foreign key into the customers table.

最终，您可能需要向经销商添加属性。然后我会有一个单独的表格，将市场与经销商联系起来（其本身可能会随着时间的推移而变化）。

Eventually you may want to add properties to distributors. Then I'd have a separate table linking markets to distributors (which itself might change over time).

通常，只有少数几个属性最终有更多的。

Often entities (e.g., distributors) that start out with only a few properties end up having a lot more.

这篇关于数据库设计问题：歧视列与外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！