Common one-to-many table for multiple entities
- by Ben V
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?
