2

Looking for some advice related to optimal design in the following scenario.

  1. There is a Cases table (represents cases of inventory)
  2. There is a LocationInventory table (represents locations with inventory)
  3. 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

  1. CaseId (FK)
  2. LocationInventoryId (FK)
  3. 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

  1. CaseId (FK)
  2. NeededQuantity

Table: InventoryNeedsLocations

  1. LocationInventoryId (FK)
  2. NeededQuantity

Option C:

One table with no referential integrity.

Table: InventoryNeedsCases

  1. NeedType (values of Case or Location)
  2. NeedId (represents the primary key of either Case or LocationInventory based on NeedType).
  3. 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.

Option A Pictorial - with more explanation and context Option A

Option B - removed context for clarity Option B - Inheritance

2

I would certainly recommend B in most cases. Both A and C leave open lots of room for inconsistency. Enforcing that exactly one of two columns is NOT NULL (for case A) is quite a pain. If you need uniqueness, enforcing that one column is either a unique value, or NULL, and the other one is also either a unique value, or NULL, with exactly one of them being NULL, is a huge pain.

C also misses some consistency checks as you recognized.

I don't exactly understand what the information is that you are trying to represent. But it usually makes sense to me that if you are trying to make one table have references to one of two different other tables, then the information in the first table actually consists of two types of different thing.

It should also be considered that it's much easier and more readable to do a UNION, than to have lots of queries of the form:

SELECT *
FROM InventoryNeed
WHERE CaseId IS NOT NULL
AND LocationInventoryId IS NULL

(This query by itself doesn't look too bad, but you are going to use it everywhere you would use a single table name in JOINs and so on).

  • 1
    Thanks @jwg I was leaning the same way, but I'm a programmer, not a dba so it is good to have this validated. – Chad Richardson Nov 6 '15 at 16:09
2

Yes, there is an option D that would involve a supertype table for the subtype tables Cases and LocationInventories. 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

Cases
    CaseID   PK  
    ... more columns

LocationInventories
    LocationInventoryID   PK  
    ... more columns

InventoryNeeds
    ?        PK
    CaseID   FK -> Cases (CaseID)
    LocationInventoryID   FK -> LocationInventories (LocationInventoryID)
    NeededQuantity
        CK: CHECK ( CaseId IS NULL AND LocationInventoryID IS NOT NULL
                 OR CaseId IS NOT NULL AND LocationInventoryID IS NULL )

Option B

Cases
    CaseID   PK  
    ... more columns

LocationInventories
    LocationInventoryID   PK  
    ... more columns

InventoryNeedsCases
    ?        PK
    CaseID   FK -> Cases (CaseID)
    NeededQuantity

InventoryNeedsLocations
    ?        PK
    LocationInventoryID   FK -> LocationInventories (LocationInventoryID)
    NeededQuantity

Option C

Without foreign keys, you lose referential integrity. I see no reason to follow this option.

Cases
    CaseID   PK  
    ... more columns

LocationInventories
    LocationInventoryID   PK  
    ... more columns

InventoryNeeds
    ?        PK
    NeedType CK: CHECK ( NeedType IN ('C', 'L') )
    NeedID   FK 
    NeededQuantity

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 and LocationInventories) that will involve an additional INSERT into (or DELETE from) the Bases Needs table.

Needs 
    NeedID   PK

Cases
    CaseID   PK  FK -> Needs (NeedID)
    ... more columns

LocationInventories
    LocationInventoryID   PK  FK -> Needs (NeedID)
    ... more columns

InventoryNeeds
    ?        PK
    NeedID   FK -> Needs (NeedID)
    NeededQuantity

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 a Need to be inserted in both Case and LocationInventory.

Needs 
    NeedID   PK  UQ1
    NeedType     UQ1  CK: CHECK ( NeedType IN ('C', 'L') )

Cases
    CaseID   PK  FK1 -> Needs (NeedID, NeedType)
    NeedType PK  FK1 
    ... more columns
        CK: CHECK ( NeedType = 'C' )

LocationInventories
    LocationInventoryID   PK  FK1 -> Needs (NeedID, NeedType)
    NeedType              PK  FK1
    ... more columns
        CK: CHECK ( NeedType = 'L' )

InventoryNeeds
    ?        PK
    NeedID   FK1 -> Needs (NeedID, NeedType)
    NeedType FK1
    NeededQuantity
  • Option D is nice, but it doesn't prevent the a single row of the Needs table being referenced by both a Case and a Location, which might not be desirable. – jwg Nov 6 '15 at 10:02
  • 2
    @jwg of course. That's addressed in option E ;) – ypercubeᵀᴹ Nov 6 '15 at 10:07
  • @ypercube thank you for taking the time to answer my question. Though I understand option E, it seems a little more complex (maybe just to me being a programmer and not a dba). For the purposes of ongoing support for other team members in our company, today and in the future, simple, obvious solutions would be better, even if less optimal from a pure relational design standpoint. – Chad Richardson Nov 6 '15 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.