Relational Design – Multiple tables into one foreign key column

database-designrelational-theory

Take a table that tracks meat purchases. It has a "meat_id" foreign key column to indicate what type of meat the purchase was.

But, the different types of meat are unique in some way (such as USDA grading), so I'm thinking they should be stored in different tables.

I currently don't have enough rep to post the ERD I drew out, but I hope these DDLs will be enough (I've simplified them for brevity):

CREATE TABLE meat_purchase
(
    id                  INTEGER
  , purchase_details    VARCHAR(4000) -- actually multiple columns, but details are irrelevant
  , meat_id             INTEGER
);

CREATE TABLE beef_meats
(
    id                  INTEGER
  , usda_beef_grade_id  INTEGER
        FOREIGN KEY REFERENCES usda_beef_grades
  , desc    VARCHAR(4000)
);

CREATE TABLE pork_meats
(
    id      INTEGER
  , desc    VARCHAR(4000)
);

CREATE TABLE poultry_meats
(
    id      INTEGER
  , bird_id
        FOREIGN KEY REFERENCES birds
  , desc    VARCHAR(4000)
);

-- and so on for the different types of meat...

I'm wondering how to relate the "meat_purchases" table to the meats tables.

Standard SQL and RDBMS agnostic answers only, please.

Best Answer

I think you are looking for a subtype/supertype construct. Meat would be your migrating key and would contain a Type field that indicates which sub-type of meat it relates to. So:

PurchaseMeat = Meat = {MeatBeef, MeatPork, MeatPoultry}

Where Meat is the type and the key of the subtype.

In Crow's feet notation this is a circle with a line under it.

Related Question