nullable foreign keys versus common parent key

Question

In a scenario where we have customers, vendors, & branches which all have common attributes such as an address, which is a better solution and why?

Solution 1:

• Customer table

• Vendor table

• Branch table

• Address table with nullable foreign keys for CustomerID, VendorID, BranchID

Solution 2:

• Entity table

• Customer table with EntityID

• Vendor table with EntityID

• Branch table with EntityID

• Address table with EntityID and a EntityType flag of 'C', 'V', or 'B'

Solution 3 (suggested by AJC):

• Customer table

• Vendor table

• Branch table

• Address table

• CustomerAddress xref table between Customer and Address

• VendorAddress xref table between Vendor and Address

• BranchAddress xref table between Branch and Address

Solution 4 (suggested by 9000)

• Customer table

• Vendor table

• Branch table

• CustomerAddress with FK to Customer

• VendorAddress with FK to Vendor

• BranchAddress with FK to Branch

• vwAddress which UNION ALLs each of the above address tables, and includes a type flag ('B','C','V')

Notes:

Each customer, vendor, or branch could have multiple addresses, but should have at least one.

If an "entity" is both a customer and a vendor, it could have separate addresses for each role.

Would like to know if a customer is also a vendor.

Answer 1

(This is basically what @AJC said, just explained.)

Solution 1 potentially allows to assign the same address to up to three entities of different kinds. Unless the latter is exactly your intention, avoid this solution.

Solution 2 allows to potentially assign a 'B' type address to a Customer, etc. Probably this is also not what you want.

As far as I understand your intention, you need to be able to assign multiple addresses to an entity, but not the other way around.

Create an address table per entity kind: CustomerAddress with FK referring to Customer, VendorAddress with FK to Vendor, etc. Besides strict referential integrity and impossibility to assign an address of incorrect type, this buys you an ability to extend each type of address with extra fields that only make sense for it.

To query all addresses more easily, you can create a view Address, which would include common fields and a type flag ('B', 'C', 'V') to see what kind of address is this.