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.

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

with constraints wherever applicable.

Thanks. After sitting an planning the project all day I was just totally blocked. Makes absolute sense.

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.

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."

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.

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.

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?

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