Too many foreign keys in this Address table?

Other SQL Server Topics
1

In our system, we need addresses for several things: Customers, Vendors, Claimants, Witnesses, Drivers, Loss Locations, Branch Offices, and a few more.

We did this for 2 reasons:

  • To consolidate Address information and avoid having to make a new table every time
  • In our application, we only need to make one form and require only one stored procedure to insert, update Address information

Since the Address information is many to 1, we keep all the foreign keys in the Addresses table. For example a Customer may have more than one location. A Claimant may have a home address and a work address.

So our table looks something like this:

Addresses

AddressID (PK)
AddressType
AddressLine1
AddressLine2
AddressLine3
City
State
ZipCode
Country
CustomerID (FK)
VendorID (FK)
Claimant (FK)
WitnessID (FK)
DriversID (FK)
ClaimID (FK)
BranchID (FK)

For some reason, another department is critizing our design because we have too many Foreign Keys in one table. And saying it would be more intuitive that the FKs should be pointing to this table rather than being in this table.

Any thoughts or idea?

2

I agree with the other department. Put the AddressID column in the other connected tables and remove all those FK ID columns from Addresses.

1 Like
3

Like TaraKizer, I would lean towards having the foreign keys in the other tables as well.

Having foreign keys in the other tables definitely have some advantages. One way to think about it is that, A Customer "HAS" an address, a Vendor "HAS" an address and so on, and not the other way around, so (at least in my mind) the foreign key should be in the Customer table, Vendor table etc.

Your current design also has the disadvantage that if you wanted to add address for a new type of entity - let us say Employees - you would have to modify the Address table to add another column for EmployeeID.

But that then begs the question as to how you would handle multiple addresses for a customer.

One option would be to use multiple columns Address1, Address2 etc. in the Customer table. However, that restricts how many addresses you can have for a customer. Besides, you would have to add these two or three additional columns in each table.

You could use a link table - CustomerAddressLink table for example. That would be great conceptually, except that when you want to find the address of a customer, you have to join 3 tables. Also, you would need to add an additional table for each type of entity - Customers, Vendors etc.

A third option would be to have a common link table which has a third column to indicate which entity the link table is referring to. But then you would lose the ability to use foreign key on the entities. And, the queries would be more convoluted as well.

I am blabbering, so I will shut up :smile:

1 Like
4

Yep, what you have is, frankly, terrible. The Address links should be in other tables.

In our application, we only need to make one form and require only one stored procedure to insert, update Address information

Your current application needs/requirements should never be allowed to corrupt the data design.

5

Well there actually is a concept in Software Engineering known as ... oh nevermind

6

Thanks. That's what we also had on our minds.

7

No, you don't want separate link tables, which again puts into changing table names and code every time a new entity needs an address.

You could have, for example, a single address link table with a tinyint "address type" where 1=Customer, 2=Vendor, 3=Claimant, etc., with the link table clustered on all three columns:
( address_type, owner_id, address_id )

8

Putting the address id in the other tables could be an issue if you have multiple addresses for an entity. Example customer has multiple physical or ship to addresses. Your more flexible option would be the suggestion to have an intermediary table linking the customer to address tables as dennis mentioned earlier.