Common one-to-many table for multiple entities

Posted by Ben V on Stack Overflow See other posts from Stack Overflow or by Ben V
Published on 2010-05-19T04:46:06Z Indexed on 2010/05/19 4:50 UTC
Read the original article Hit count: 305

Filed under:

database

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?

Developer IT