多个实体的通用一对多表

Suppose I have two tables, Customer and Vendor. I want to have a common address table for customer and vendor addresses. Customers and Vendors can both have one to many addresses.

假设我有两个表，即客户和供应商。我想为客户和供应商地址提供一个公共地址表。客户和供应商都可以拥有一个到多个地址。

Option 1

Add columns for the AddressID to the Customer and Vendor tables. This just doesn't seem like a clean solution to me.

将AddressID的列添加到Customer和Vendor表。这对我来说似乎不是一个干净的解决方案。

Customer     Vendor         Address
--------     ---------      ---------
CustomerID   VendorID       AddressID
AddressID1   AddressID1     Street
AddressID2   AddressID2     City...

Option 2

Move the foreign key to the Address table. For a Customer, Address.CustomerID will be populated. For a Vendor, Address.VendorID will be populated. I don't like this either - I shouldn't need to modify the address table every time I want to use it for another entity.

将外键移动到地址表。对于Customer，将填充Address.CustomerID。对于供应商，将填充Address.VendorID。我也不喜欢这样 - 每次我想将它用于另一个实体时，我都不需要修改地址表。

Customer     Vendor         Address
--------     ---------      ---------
CustomerID   VendorID       AddressID
                            CustomerID
                            VendorID

Option 3

I've also seen this - only 1 foreign key column on the Address table with another column to identify which foreign key table the address belongs to. I don't like this one because it requires all the foreign key tables to have the same type of ID. It also seems messy once you start coding against it.

我也看到了这一点 - 在Address表上只有一个外键列，另一列用于标识该地址所属的外键表。我不喜欢这个，因为它要求所有外键表具有相同类型的ID。一旦你开始对它编码，它似乎也很混乱。

Customer     Vendor         Address     
--------     ---------      ---------
CustomerID   VendorID       AddressID
                            FKTable
                            FKID

So, am I just too picky, or is there something I haven't thought of?

所以，我太挑剔了，还是有什么我没有想到的？

4 个解决方案

#1

I'd say the missing piece of the puzzle is the "is a" relationship that is often overlooked in data modeling; this is distinct from the familiar "has a" relationship. An "is a" relationship is similar to an inheritance relationship in a object oriented design. To model this you'll need a base table that represents the common attributes of vendors and customers. For example, we could call the base table "Organizations":

我要说的是，这个难题的缺失部分是数据建模中经常被忽视的“是一种”关系;这与熟悉的“有”关系截然不同。 “是一种”关系类似于面向对象设计中的继承关系。要对此进行建模，您需要一个表示供应商和客户的公共属性的基表。例如，我们可以调用基表“组织”：

Organizations       Vendors               Customers
--------------      ---------------------  ---------------------
OrganizationID(PK)  OrganizationID(FK/PK)  OrganizationID(FK/PK)
AddressID1(FK)
AddressID2(FK)

In this example Vendor "is a" organization, and Customer "is a" organization, whereas an organization "has a" address. The Organizations, Vendors, and Customers tables share a common key and a common key sequence enforced by referential integrity.

在此示例中，供应商“是”组织，而客户“是”组织，而组织“具有”地址。 “组织”，“供应商”和“客户”表共享公用密钥和由参照完整性强制执行的公用密钥序列。

#2

I think out of the three options you gave, I'd be most inclined to go with option 1. Normally a customer or vendor won't have more than a few different addresses, but if they do, maybe the solution below would work better for you. I wouldn't go for option 2, because it probably doesn't make sense to associate an Address with both a Customer and a Vendor at the same time. I know you'd probably only set one of those IDs at a time, but the model might be confusing, and you may need to add special logic to make sure only the CustomerID or the VendorID is set on any given record. I would definitely not do option 3, because you can't make FKID a true FK. If you want a column to reference more than one table, you will not be able to use a FK constraint in the database to enforce it. Plus, if you plan on using an ORM to interact with the database in code, they tend to have trouble dealing with "fake" foreign keys that reference multiple tables depending on a separate "discriminator" column.

我认为在你给出的三个选项中，我最倾向于使用选项1.通常，客户或供应商不会有多个不同的地址，但如果他们这样做，可能下面的解决方案会更好为你。我不会选择选项2，因为将地址与客户和供应商同时关联可能没有意义。我知道您可能一次只设置其中一个ID，但模型可能会令人困惑，您可能需要添加特殊逻辑以确保在任何给定记录上只设置CustomerID或VendorID。我肯定不会做选项3，因为你不能让FKID成为真正的FK。如果希望列引用多个表，则无法在数据库中使用FK约束来强制执行该表。另外，如果您打算使用ORM在代码中与数据库进行交互，那么根据单独的“鉴别器”列，它们往往难以处理引用多个表的“假”外键。

If you want a truly open-ended solution, you could create many-to-many relationships between Customer and Address and Vendor and Address.

如果您想要一个真正开放式的解决方案，您可以在客户和地址以及供应商和地址之间创建多对多关系。

Customer
--------
CustomerID (PK)

Vendor
------
VendorID (PK)

Address
-------
AddressID (PK)

CustomerAddress
---------------
CustomerID (FK/PK)
AddressID (FK/PK)

VendorAddress
-------------
VendorID (FK/PK)
AddressID (FK/PK)

#3

How about having four tables with one acting as a gateway to the addresses? So you would have

如果有四个表，其中一个作为地址的网关？所以你会的

Customer
    customerId (PK)
    addressBookId (FK to AddressBook)

Vendor
    vendorId (PK)
    addressBokId (FK to AddressBook)

AddressBook
    addressBookId (PK)

Address
    addressId (PK)
    addressBookId (FK to AddressBook)

#4

can you redesign the table to have the following fields:

你可以重新设计表格以包含以下字段：

 Address_Type  (a flag to say that this is a customer, or a vendor)
 ID            (a common ID for both customer and vendor and depending on the flag you know what ID it is)
 Address       (Data of address itself)

or you can have two tables:

或者您可以有两个表：

Address table, where no reference to types of address it is. i.e. to have both
- entity ID (key)
- 实体ID（关键）
- Address
- 地址
地址表，其中没有对地址类型的引用。即具有实体ID（密钥）地址
address type table, where it would point at entry and have the flag outside the table to have:
- entity ID (FK to entity ID in the above table)
- 实体ID（FK到上表中的实体ID）
- entity type (customer or vendor)
- 实体类型（客户或供应商）
地址类型表，它将指向入口并使表外的标志具有：实体ID（FK到上表中的实体ID）实体类型（客户或供应商）

#1

Organizations       Vendors               Customers
--------------      ---------------------  ---------------------
OrganizationID(PK)  OrganizationID(FK/PK)  OrganizationID(FK/PK)
AddressID1(FK)
AddressID2(FK)

#2