I'm defining a database for a customer/ order system where there are two highly distinct types of customers. Because they are so different having a single customer table would be very ugly (it'd be full of null columns as they are pointless for one type).
我正在为客户/订单系统定义一个数据库，其中有两种高度不同类型的客户。因为它们是如此不同而只有一个客户表会非常难看（它充满了空列，因为它们对于一种类型来说毫无意义）。
Their orders though are in the same format. Is it possible to have a CustomerId column in my Order table which has a foreign key to both the Customer Types? I have set it up in SQL server and it's given me no problems creating the relationships, but I'm yet to try inserting any data.
他们的订单虽然格式相同。是否可以在Order表中有一个CustomerId列，该列具有两个客户类型的外键？我已经在SQL服务器中进行了设置，它给我创建关系没有问题，但我还没有尝试插入任何数据。
Also, I'm planning on using nHibernate as the ORM, could there be any problems introduced by doing the relationships like this?
另外，我打算使用nHibernate作为ORM，通过这样的关系可以引入任何问题吗？
7 个解决方案
#1
21
No, you can't have a single field as a foreign key to two different tables. How would you tell where to look for the key?
不，您不能将单个字段作为两个不同表的外键。你怎么知道在哪里寻找钥匙？
You would at least need a field that tells what kind of user it is, or two separate foreign keys.
您至少需要一个字段来告诉它是什么类型的用户，或两个单独的外键。
You could also put the information that is common for all users in one table and have separate tables for the information that is specific for the user types, so that you have a single table with user id as primary key.
您还可以将所有用户通用的信息放在一个表中，并为特定于用户类型的信息设置单独的表，以便您有一个用户ID为主键的表。
#2
4
A foreign key can only reference a single primary key, so no. However, you could use a bridge table:
外键只能引用一个主键，所以没有。但是，您可以使用桥接表：
CustomerA <---- CustomerA_Orders ----> Order
CustomerB <---- CustomerB_Orders ----> Order
So Order doesn't even have a foreign key; whether this is desirable, though...
所以Order甚至没有外键;这是否可取，但......
#3
1
You can create a foreign key referencing multiple tables. This feature is to allow vertical partioining of your table and still maintain referential integrity. In your case however, this is not applicable.
您可以创建引用多个表的外键。此功能允许对表进行垂直分区，并仍保持参照完整性。但是，在您的情况下，这不适用。
Your best bet would be to have a CustomerType table with possible columns - CustomerTypeID, CustomerID, where CustomerID is the PK and then refernce your OrderID table to CustomerID.
您最好的选择是拥有一个CustomerType表，其中包含可能的列 - CustomerTypeID，CustomerID，其中CustomerID是PK，然后将您的OrderID表引用到CustomerID。
Raj
拉吉
#4
1
I inherited a SQL Server database where this was done (a single column used in four foreign key relationships with four unrelated tables), so yes, it's possible. My predecessor is gone, though, so I can't ask why he thought it was a good idea.
我继承了一个SQL Server数据库，在那里完成了这个（一个列用于四个外键关系，四个不相关的表），所以是的，这是可能的。不过，我的前任已经不在了，所以我不敢问他为什么认为这是个好主意。
He used a GUID column ("uniqueidentifier" type) to avoid the ambiguity problem, and he turned off constraint checking on the foreign keys, since it's guaranteed that only one will match. But I can think of lots of reasons that you shouldn't, and I haven't thought of any reasons you should.
他使用GUID列（“uniqueidentifier”类型）来避免歧义问题，并且他关闭了对外键的约束检查，因为它保证只有一个匹配。但是我可以想到很多你不应该这样做的原因，我没有想到你应该有任何理由。
Yours does sound like the classical "specialization" problem, typically solved by creating a parent table with the shared customer data, then two child tables that contain the data unique to each class of customer. Your foreign key would then be against the parent customer table, and your determination of which type of customer would be based on which child table had a matching entry.
您的确听起来像经典的“专业化”问题，通常通过创建包含共享客户数据的父表来解决，然后通过两个包含每个客户类唯一数据的子表来解决。您的外键将针对父客户表，并确定哪种类型的客户将基于哪个子表具有匹配的条目。
#5
0
As noted, if the key is, say, 12345, how would you know which table to look it up in? You could, I suppose, do something to insure that the key values for the two tables never overlapped, but this is too ugly and painful to contemplate. You could have a second field that says which customer type it is. But if you're going to have two fields, why not have one field for customer type 1 id and another for customer type 2 id.
如上所述，如果密钥是12345，您如何知道查找哪个表？我想，你可以做一些事情来确保两张桌子的关键值永远不会重叠，但这太难看了，难以思考。您可以使用第二个字段来说明它是哪种客户类型。但是，如果您要有两个字段，为什么不为客户类型1 id指定一个字段，为客户类型2 id指定另一个字段。
Without knowing more about your app, my first thought is that you really should have a general customer table with the data that is common to both, and then have two additional tables with the data specific to each customer type. I would think that there must be a lot of data common to the two -- basic stuff like name and address and customer number at the least -- and repeating columns across tables sucks big time. The additional tables could then refer back to the base table. As there is then a single key for the base table, the issue of foreign keys having to know which table to refer to evaporates.
在不了解您的应用程序的情况下，我首先想到的是，您确实应该拥有一个通用客户表，其中包含两者共有的数据，然后还有两个附加表，其中包含特定于每种客户类型的数据。我认为必须有两个常见的数据 - 至少是名称和地址以及客户编号等基本内容 - 而且跨表的重复列很费时间。然后，附加表可以引用回基表。由于基表有一个单键，外键必须知道要引用哪个表的问题就会消失。
#6
0
Two distinct types of customer is a classic case of types and subtypes or, if you prefer, classes and subclasses. Here is an answer from another question.
两种不同类型的客户是类型和子类型的经典案例，或者，如果您愿意，还可以是类和子类。这是另一个问题的答案。
Essentially, the class-table-inheritance technique is like Arnand's answer. The use of the shared-primary-key technique is what allows you to get around the problems created by two types of foreign key in one column. The foreign key will be customer-id. That will identify one row in the customer table, and also one row in the appropriate kind of customer type table, as the case may be.
本质上，类表继承技术就像Arnand的答案。使用shared-primary-key技术可以让您在一列中解决由两种类型的外键创建的问题。外键将是customer-id。这将标识customer表中的一行，并在适当类型的客户类型表中标识一行，视情况而定。
#7
-1
- Create a "customer" table include all the columns that have same data for both types of customer.
- 创建“客户”表包括两种类型客户具有相同数据的所有列。
- Than create table "customer_a" and "customer_b"
- 比创建表“customer_a”和“customer_b”
-
Use "customer_id" from "consumer" table as foreign key in "customer_a" and "customer_b"
使用“consumer”表中的“customer_id”作为“customer_a”和“customer_b”中的外键
customer | --------------------------------- | | cusomter_a customer_b
#1
21
No, you can't have a single field as a foreign key to two different tables. How would you tell where to look for the key?
不，您不能将单个字段作为两个不同表的外键。你怎么知道在哪里寻找钥匙？
You would at least need a field that tells what kind of user it is, or two separate foreign keys.
您至少需要一个字段来告诉它是什么类型的用户，或两个单独的外键。
You could also put the information that is common for all users in one table and have separate tables for the information that is specific for the user types, so that you have a single table with user id as primary key.
您还可以将所有用户通用的信息放在一个表中，并为特定于用户类型的信息设置单独的表，以便您有一个用户ID为主键的表。
#2
4
A foreign key can only reference a single primary key, so no. However, you could use a bridge table:
外键只能引用一个主键，所以没有。但是，您可以使用桥接表：
CustomerA <---- CustomerA_Orders ----> Order
CustomerB <---- CustomerB_Orders ----> Order
So Order doesn't even have a foreign key; whether this is desirable, though...
所以Order甚至没有外键;这是否可取，但......
#3
1
You can create a foreign key referencing multiple tables. This feature is to allow vertical partioining of your table and still maintain referential integrity. In your case however, this is not applicable.
您可以创建引用多个表的外键。此功能允许对表进行垂直分区，并仍保持参照完整性。但是，在您的情况下，这不适用。
Your best bet would be to have a CustomerType table with possible columns - CustomerTypeID, CustomerID, where CustomerID is the PK and then refernce your OrderID table to CustomerID.
您最好的选择是拥有一个CustomerType表，其中包含可能的列 - CustomerTypeID，CustomerID，其中CustomerID是PK，然后将您的OrderID表引用到CustomerID。
Raj
拉吉
#4
1
I inherited a SQL Server database where this was done (a single column used in four foreign key relationships with four unrelated tables), so yes, it's possible. My predecessor is gone, though, so I can't ask why he thought it was a good idea.
我继承了一个SQL Server数据库，在那里完成了这个（一个列用于四个外键关系，四个不相关的表），所以是的，这是可能的。不过，我的前任已经不在了，所以我不敢问他为什么认为这是个好主意。
He used a GUID column ("uniqueidentifier" type) to avoid the ambiguity problem, and he turned off constraint checking on the foreign keys, since it's guaranteed that only one will match. But I can think of lots of reasons that you shouldn't, and I haven't thought of any reasons you should.
他使用GUID列（“uniqueidentifier”类型）来避免歧义问题，并且他关闭了对外键的约束检查，因为它保证只有一个匹配。但是我可以想到很多你不应该这样做的原因，我没有想到你应该有任何理由。
Yours does sound like the classical "specialization" problem, typically solved by creating a parent table with the shared customer data, then two child tables that contain the data unique to each class of customer. Your foreign key would then be against the parent customer table, and your determination of which type of customer would be based on which child table had a matching entry.
您的确听起来像经典的“专业化”问题，通常通过创建包含共享客户数据的父表来解决，然后通过两个包含每个客户类唯一数据的子表来解决。您的外键将针对父客户表，并确定哪种类型的客户将基于哪个子表具有匹配的条目。
#5
0
As noted, if the key is, say, 12345, how would you know which table to look it up in? You could, I suppose, do something to insure that the key values for the two tables never overlapped, but this is too ugly and painful to contemplate. You could have a second field that says which customer type it is. But if you're going to have two fields, why not have one field for customer type 1 id and another for customer type 2 id.
如上所述，如果密钥是12345，您如何知道查找哪个表？我想，你可以做一些事情来确保两张桌子的关键值永远不会重叠，但这太难看了，难以思考。您可以使用第二个字段来说明它是哪种客户类型。但是，如果您要有两个字段，为什么不为客户类型1 id指定一个字段，为客户类型2 id指定另一个字段。
Without knowing more about your app, my first thought is that you really should have a general customer table with the data that is common to both, and then have two additional tables with the data specific to each customer type. I would think that there must be a lot of data common to the two -- basic stuff like name and address and customer number at the least -- and repeating columns across tables sucks big time. The additional tables could then refer back to the base table. As there is then a single key for the base table, the issue of foreign keys having to know which table to refer to evaporates.
在不了解您的应用程序的情况下，我首先想到的是，您确实应该拥有一个通用客户表，其中包含两者共有的数据，然后还有两个附加表，其中包含特定于每种客户类型的数据。我认为必须有两个常见的数据 - 至少是名称和地址以及客户编号等基本内容 - 而且跨表的重复列很费时间。然后，附加表可以引用回基表。由于基表有一个单键，外键必须知道要引用哪个表的问题就会消失。
#6
0
Two distinct types of customer is a classic case of types and subtypes or, if you prefer, classes and subclasses. Here is an answer from another question.
两种不同类型的客户是类型和子类型的经典案例，或者，如果您愿意，还可以是类和子类。这是另一个问题的答案。
Essentially, the class-table-inheritance technique is like Arnand's answer. The use of the shared-primary-key technique is what allows you to get around the problems created by two types of foreign key in one column. The foreign key will be customer-id. That will identify one row in the customer table, and also one row in the appropriate kind of customer type table, as the case may be.
本质上，类表继承技术就像Arnand的答案。使用shared-primary-key技术可以让您在一列中解决由两种类型的外键创建的问题。外键将是customer-id。这将标识customer表中的一行，并在适当类型的客户类型表中标识一行，视情况而定。
#7
-1
- Create a "customer" table include all the columns that have same data for both types of customer.
- 创建“客户”表包括两种类型客户具有相同数据的所有列。
- Than create table "customer_a" and "customer_b"
- 比创建表“customer_a”和“customer_b”
-
Use "customer_id" from "consumer" table as foreign key in "customer_a" and "customer_b"
使用“consumer”表中的“customer_id”作为“customer_a”和“customer_b”中的外键
customer | --------------------------------- | | cusomter_a customer_b