多个实体的公共一对多表

问题描述

假设我有两个表，Customer和Vendor。我想为客户和供应商地址提供一个公共地址表。

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.

将AddressID列添加到客户和供应商表。这对我来说似乎不是一个干净的解决方案。

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

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

选项2

将外键移至地址表。对于客户，将填充 Address.CustomerID 。对于供应商，将填充 Address.VendorID 。我不喜欢这个 - 我不应该需要修改地址表每次我想使用它为另一个实体。

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     Vendor         Address
--------     ---------      ---------
CustomerID   VendorID       AddressID
                            CustomerID
                            VendorID

选项3

've也看到了 - 在Address表上只有1个外键列，用另一列来标识地址属于哪个外键表。我不喜欢这个，因为它要求所有的外键表有相同类型的ID。

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.

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

那么，我太挑剔了，还是有没有想到的东西？

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

推荐答案

我会说，拼图的缺失是是一个关系，在数据建模中经常被忽略;这与熟悉的有a关系不同。 is a关系类似于面向对象设计中的继承关系。为了对此进行建模，您需要一个基表，表示供应商和客户的常见属性。例如，我们可以调用基本表组织：

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.

这篇关于多个实体的公共一对多表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！