Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
"I'm wondering how to relate the "meat_purchases" table to the meats tables." Aren't you doing this already? You can go from meat_purchase to any other of the *_meat tables via the meat_id column. Just use this as a foreign key. –  DrColossos May 17 '11 at 6:52
    
@DrColossos I thought a foreign key can only reference one table, though. –  tgxiii May 17 '11 at 15:32
    
Correct, but if you leave constraints aside (this is what a FK does, it constraints an table<->column to another table<->column), you could have in in as many tables as you want. If you don't tell the script to have an explicit foreign key (as e.g. bird_id), but a self definied constraint, that would ensure integrity along the whole insertion process. Another option would be to "simulate" FK behavior. This could be a trigger that checks the inserted values for their correctness. –  DrColossos May 17 '11 at 15:37
    
@DrColossos That was actually the direction I was going with initially. This question was just out of my curiosity. I started to wonder how this would be achieved with just standard SQL (no triggers/stored procedures that are dependent on the specific RDBMS vendor). –  tgxiii May 17 '11 at 15:56

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
Thanks for the answer. This seems to be what I'm looking for, but I'd like to research it further before I accept. –  tgxiii May 17 '11 at 15:57

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
share|improve this answer
    
Although cows and pigs have similar bodies, the names of their cuts differ (beef chuck vs. pork butt). And both are completely different from poultry. Also, AFAIK, all poultry cuts are named the same, no matter the type of bird (chicken breast, turkey breast, etc.), which is why there's a "bird_id" foreign key column on the "poultry_meats" table. In practice, I would go in a similar route as you suggested, but this question is just for theory. –  tgxiii May 19 '11 at 3:02
    
There is probably some over lap. Lamp chops-pork chops, Chicken leg - lamb leg, etc. You could use a many-many table to map meats to cuts. –  SqlSandwiches May 19 '11 at 23:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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