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.