Looking for some advice related to optimal design in the following scenario.
- There is a Cases table (represents cases of inventory)
- There is a LocationInventory table (represents locations with inventory)
- Then I have an InventoryNeed table or tables (this is the crux of the question), that needs to account for cases and locations.
Option A:
One table with 2 foreign key columns in which one and only one of the foreign keys would be populated.
Table: Inventory Need
- CaseId (FK)
- LocationInventoryId (FK)
- NeededQuantity
In this case either CaseId or LocationInventoryId would null and the other populated.
Option B:
Two tables for each need type that will frequently be UNION'ed to get summary data.
Table: InventoryNeedsCases
- CaseId (FK)
- NeededQuantity
Table: InventoryNeedsLocations
- LocationInventoryId (FK)
- NeededQuantity
Option C:
One table with no referential integrity.
Table: InventoryNeedsCases
- NeedType (values of Case or Location)
- NeedId (represents the primary key of either Case or LocationInventory based on NeedType).
- NeededQuantity
And the winner is? I'd probably narrow it down to say A or B to ensure data integrity…but not sure which is best from there. Or maybe there is an option D (like creating a base table with common columns…)
UPDATED SCENARIO
When I posted this last night, I was only thinking downstream, but there are also upstream dependencies to these same tables. I made some drawings that may hopefully explain it better. With this advent, option B starts to explode the nbr of tables involved, and after reading this SE answer…I am now leaning more towards A.
Pics below. Then red represent Inventory Needs, Green represents Inventory sources to meet those needs. And yellow is the question at hand…how to link the reds and greens efficiently.
Best Answer
Yes, there is an option D that would involve a supertype table for the subtype tables
Cases
andLocationInventories
. You can search for the "supertype/subtype" pattern and "shared primary key" for additional info.Option D is moved below and I'll add the other options for comparison:
Option A
Option B
Option C
Without foreign keys, you lose referential integrity. I see no reason to follow this option.
Option D
With the addition of this one table, the constraints are quite simplified. The only thing that gets complicated is the insertion (and deletion) of rows to the two tables (
Cases
andLocationInventories
) that will involve an additionalINSERT
into (orDELETE
from) theBases
Needs
table.Option E
This is a modified D option that combines the
NeedType
of C option, without losing any referential integrity. It also avoids the (accidental or not) pitfall of option D, that allows aNeed
to be inserted in bothCase
andLocationInventory
.