Common one-to-many table for multiple entities

Question

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.

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     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.

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

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

Answer 1

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.

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)

Answer 2

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)
  • Address
• 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)
  • entity type (customer or vendor)

Answer 3

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.