Relational Design - Multiple tables into one foreign key column?












5















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
















5















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














5












5








5


3






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














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.







database-design relational-theory






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked May 17 '11 at 2:16









tgxiiitgxiii

170225




170225








  • 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














  • 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








1




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





"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





@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







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





@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










3 Answers
3






active

oldest

votes


















4














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



















3














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





















0














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






share|improve this answer








New contributor




jim smith is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.




















    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f2754%2frelational-design-multiple-tables-into-one-foreign-key-column%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    4














    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
















    4














    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














    4












    4








    4







    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













    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












    share|improve this answer



    share|improve this answer










    answered May 17 '11 at 6:47









    dba4lifedba4life

    33613




    33613













    • 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



















    • 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

















    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





    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













    3














    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


















    3














    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
















    3












    3








    3







    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













    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












    share|improve this answer



    share|improve this answer










    answered May 18 '11 at 22:30









    SqlSandwichesSqlSandwiches

    1,24511320




    1,24511320













    • 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





















    • 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



















    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







    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







    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













    0














    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






    share|improve this answer








    New contributor




    jim smith is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.

























      0














      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






      share|improve this answer








      New contributor




      jim smith is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.























        0












        0








        0







        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






        share|improve this answer








        New contributor




        jim smith is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.










        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







        share|improve this answer








        New contributor




        jim smith is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.









        share|improve this answer



        share|improve this answer






        New contributor




        jim smith is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.









        answered 21 mins ago









        jim smithjim smith

        1011




        1011




        New contributor




        jim smith is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.





        New contributor





        jim smith is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.






        jim smith is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f2754%2frelational-design-multiple-tables-into-one-foreign-key-column%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh