Sfrgttk

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.

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.

I wouldn't create other tables for the different kind of meats. I would create meat types and cut types and then use FKs to tie them all together. Sample DB below:

USE MEAT

CREATE TABLE [dbo].[MeatCut](

[MeatCutID] [int] NOT NULL,

[Description] [varchar](500) NOT NULL,

 CONSTRAINT [PK_MeatCut] PRIMARY KEY CLUSTERED 

(   [MeatCutID] ASC))



GO

CREATE TABLE [dbo].[MeatType](

[MeatTypeid] [int] NOT NULL,

[Description] [varchar](500) NOT NULL,

[usda_beef_grade_id] [int] NOT NULL,

 CONSTRAINT [PK_MeatType] PRIMARY KEY CLUSTERED 

(   [MeatTypeid] ASC))



CREATE TABLE [dbo].[MeatProduct](

[MeatProductID] [int] NOT NULL,

[MeatTypeID] [int] NULL,

[MeatCutID] [int] NULL,

 CONSTRAINT [PK_MeatProduct] PRIMARY KEY CLUSTERED 

(   [MeatProductID] ASC)) 

GO



CREATE TABLE [dbo].[meat_purchase](

[PurchaseID] [int] NOT NULL,

[purchase_details] [varchar](4000) NULL,

[MeatProduct_id] [int] NULL,

CONSTRAINT [PK_meat_purchase] PRIMARY KEY CLUSTERED 

(   [PurchaseID] ASC)) 

GO

ALTER TABLE [dbo].[MeatProduct]  WITH CHECK ADD  CONSTRAINT [FK_MeatProduct_MeatCut] FOREIGN KEY([MeatCutID]) REFERENCES [dbo].MeatCut] ([MeatCutID])

GO

ALTER TABLE [dbo].[MeatProduct] CHECK CONSTRAINT [FK_MeatProduct_MeatCut]

GO

ALTER TABLE [dbo].[MeatProduct]  WITH CHECK ADD  CONSTRAINT [FK_MeatProduct_MeatType] FOREIGN KEY([MeatTypeID])

REFERENCES [dbo].[MeatType] ([MeatTypeid])

GO

ALTER TABLE [dbo].[MeatProduct] CHECK CONSTRAINT [FK_MeatProduct_MeatType]

GO

ALTER TABLE [dbo].[meat_purchase]  WITH CHECK ADD  CONSTRAINT [FK_meat_purchase_MeatProduct] FOREIGN KEY([MeatProduct_id])

REFERENCES [dbo].[MeatProduct] ([MeatProductID])

GO

ALTER TABLE [dbo].[meat_purchase] CHECK CONSTRAINT [FK_meat_purchase_MeatProduct]

GO

HORSES FOR COURSES

You are trying to join an e-commerce solution with an entirely different operational solution. What you need to do is think of e-commerce as a separate bounded context aka a department and therefore it has a separate database.

Department: SALES

Department: MEAT PRODUCTION

In this case a sales.order has sales.order_lines which has sales.products, and product has a description field that the describes the meat product

If you need meat details from PRODUCTION then they would be sent over to your sales DB as product.specifications