SQL multiple foreign keys vs single foreign key on multiple tables

Question

I'm trying to figure out how to define foreign keys in my database.

Let's say I have three tables:

• Site (this is the site where a company resides),
• Warehouse (on this site there can be multiple Warehouses), and
• WarehouseLocation (in that warehouse there are multiple locations, eg shelves)

Now,

• Site-Warehouse is a one-to-many relationship
• Warehouse-WarehouseLocation is a one-to-many relationship

When would I describe WarehouseLocation with multiple foreign keys, one to Warehouse.id and one to Site.id?

Site --[ Warehouse
  |         ---
  |          |
  +----[ WarehouseLocation

When would I just use:

 Site --[ Warehouse --[ WarehouseLocation

In the first option when I lookup a WarehouseLocation I would need the Site.id and the Warehouse.id.

In the second option when I lookup a WarehouseLocation I would need the Warehouse.id, but to lookup the Warehouse I would need the Site.id

I'm confused about which option is suitable in what situation. Can someone give me some hints of the pros and cons of both options?

Answer 1

TL;DR

The second option is what you should be looking at. That is,

A WareHouseLocation table would have only the WareHouseID as the foreign key and the WareHouse table would have the SiteID as the foreign key.

Explanation

You have to look at it from a functional perspective and not querying perspective. A WareHouseLocation specifies the location of a Warehouse. Hence, the relationship makes sense(i.e. the foreign key seems appropriate). However, if you think about it, the WarehouseLocation really has nothing to do with the Site. Hence, the relationship purely from a functional perspective doesn't make a lot of sense.

However, from a querying perspective, it looks great as you would need to retrieve SiteID and WareHouseID in most, if not all situations, when querying over the WareHouseLocation table. Having them readily available in the same table would reduce JOINs and making querying a much easier task. This seems to be the crux of your dilemma in terms of database design.

Database design is quite a complex subject and a lot of considerations go into into it, some of which are very specific to the project itself. A general rule of thumb is to keep databases as normalized as possible (especially if you are reading textbooks). In practice, however, a lot of database designers prefer to denormalize databases to an extant atleast. Normalizing/Denormalizing a database is quite an opinionated subject, so I won't delve on it here. You can read more about it in the following posts :

How far to take normalization in database design?

How does one know when to stop normalizing?

Hope this helps!!!