Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Recipe database problem

    I am trying to get a simple cooking recipe database together:

    I got a ingredients table with all the nutrition info.

    Each recipe has up to 20 ingrediences, amounts, descriptions on what to do, and comment fields.
    Each recipe also has fields for what cooking tools to use, name, difficulty, time to cook and other fields.

    How can I put together the 20 rows of each recipe and connect it to the ingedients table?

    Maybe I am just tired but I am pulling a blank on that one as I am not very experienced with dbdesign.

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Posts
    108
    Ingred_All Will have : (All of your ingr. )
    Ingr_Id
    Ingr_Name
    Ingr_Nutr

    Recipe_Main Will have : (all info for recipe )
    Rcp_Id
    Rcp_Name
    Rcp_CookedBy
    .

    RcpIngr will have : (Just store which receipe has what all ingriedents)
    Rcp_Id
    Ingr_Id
    Ingr_Qty

  3. #3
    Join Date
    Feb 2004
    Posts
    108
    with constraints wherever applicable.

  4. #4
    Join Date
    Mar 2004
    Posts
    15
    Thanks. After sitting an planning the project all day I was just totally blocked. Makes absolute sense.

  5. #5
    Join Date
    Mar 2004
    Posts
    15
    Actually when thinking it thru there is a problem with the Pimary key of RcpIngr

    As it is the combined Primary of ngred_All and Recipe_Main there is a problem in the setup of my recipes.

    It might be that in an early stage the food is spiced with e.g. pepper and later on the whole dish might be spiced with pepper again so the primary will not be unique.

    Guess I have to put in a step number to combine with the primary.

  6. #6
    Join Date
    Mar 2004
    Posts
    3
    But that artificial key may not be needed. It is common for a recipe to state something like "fold in two cups of x, reserving the remainder for later."

  7. #7
    Join Date
    Feb 2004
    Posts
    108
    Originally posted by AGISB
    Actually when thinking it thru there is a problem with the Pimary key of RcpIngr

    As it is the combined Primary of ngred_All and Recipe_Main there is a problem in the setup of my recipes.

    It might be that in an early stage the food is spiced with e.g. pepper and later on the whole dish might be spiced with pepper again so the primary will not be unique.

    Guess I have to put in a step number to combine with the primary.
    Yea agreed with bobweeks.
    If for Rcp1, you are adding Ingr1 twice - first time during preperation and second when the dish is ready. So you just add up the "amount" of Ingr1. If required, keep this info in "comments". No need to insert second record.

  8. #8
    Join Date
    Mar 2004
    Posts
    3
    Originally posted by AGISB
    Guess I have to put in a step number to combine with the primary.
    The step number may have a good purpose, though. Aren't the ingredients usually listed in the order in which they're used? If so, this is a good attribute to have. But, it doesn't necessarily have to be part of the primary key.

  9. #9
    Join Date
    Mar 2004
    Posts
    15
    Originally posted by bobweeks
    The step number may have a good purpose, though. Aren't the ingredients usually listed in the order in which they're used? If so, this is a good attribute to have. But, it doesn't necessarily have to be part of the primary key.

    Yes thats the case. The problem I have is that the recipes are already created and I will have to deal with how they were created now as it is a nightmare to do it later on or even rewriting the recipes.

    I will probably add another table for the work steps and connect them to RcpIngr as PF key as well as it will be planned to have different translations later on as well.

  10. #10
    Join Date
    Mar 2004
    Posts
    15
    Here is what it looks like after 2NF

    Does it look good to you?

    One thing I am unsure about is Recipe_Creator. The creator can be either a Customer, an Employee or both when the Customer submits a recipe and an employee reworks it a little. Is the conection to the employee and customer table correct? (left out those tables but the FK are in Recipe_Creator)

    I assume that if a customer creates the recipe employee_ID is just left NULL in Recipe_Creator or does the FK require to be NOT NULL?
    Attached Thumbnails Attached Thumbnails recipedata.gif  

  11. #11
    Join Date
    Apr 2003
    Location
    London, England
    Posts
    42

    Recipe Database design

    I assume that if a customer creates the recipe employee_ID is just left NULL in Recipe_Creator or does the FK require to be NOT NULL? [/SIZE][/QUOTE]

    Here is a Recipe Data Model that you might find interesting on my Web Site :-
    http://www.databaseanswers.com/data_...ipes/index.htm

    Barry Williams
    Principal Consultant
    Database Answers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •