Am trying to teach myself how to do this - any feedback would be great. Here are my tables:

Ingredients(ingredient_id, ingredient_name, recipe_id_01, recipe_id_02, recipe_id_03 etc.)

Recipes(recipe_id, recipe_name, recipe_filename, recipe_link)

Where the Ingredients(recipe_id_01) is related to the Recipes(recipe_id).

What I want to do is very simple. I want a web page to display:
- ingredient_name, plus any recipe names/links that include this ingredient. Obviously each ingredient will appear in many recipes.

I realize this is such a basic question. But I get utterly confused by the one-to-many, many-to-many relationships among tables, and wondering whether I must establish this relationship before trying to build a query...?

Oh- I should clarify - I am playing around with it in Access but will eventually migrate it to SQL Server 2000.

Thanks in advance...

I'd do this a wee bit differently.

My ingredient table would include the ingredientId and its name.

My reciepe table would match yours.

My "uses" table would describe the relationship between the receipe and the ingredient. It would include the receipeID, the ingredientId, and the measurement (how much of this ingredient does this receipe need).

See if looking at the problem that way helps!

-PatP

(And thanks btw) The problem I have is how the uses table describes the relationship.

Basically the uses table, to work the way I need it to, would look something like this:

Uses(recipe_id, ingredient_id_01, ingredient_id_02) etc. -- because each recipe may use > one ingredient from the ingredient table. So the output would eventually need to look like this:

You searched for all recipes with tuna (ingredient_id=tuna):

Recipe: Tuna Casserole (recipe_id="tuna casserole")
Recipe: Your mother's gross tuna recipe (and so on)
Recipe: The stuff you feed your cat

etc.

So, the logic is, click on an ingredient from the ingredient table. Query will locate each recipe_id that includes the chosen ingredient_id.

Hmmm. Did I just answer my question? If it's set up the way I described, based on your suggestion - would there be any need to establish relationships? Or would it work like that?

In particular I'm wondering about what to name each field in the uses table - I assume I need distinct names for each field in the table - like ingredient_id_01, ingredient_id_02 etc. -- or not? Sorry for the newbiness. Haven't got the zen of it yet.

There would be a separate "uses" row for each ingredient that was used by a receipe. So if you had 20 ingredients, and ten reciepes that each used six different ingredients, then you'd have sixty (ten times six) uses rows.

-PatP

OH, I get that -- thank you!

The process that you're going through is called "normalization" in database geek-speak. The normalization process basically boils down to getting each table to represent exactly one thing, be that a receipe, an ingredient, or the abstract relationship between a receipe and an ingredient.

Once you get a database normallized (to at least the third normal form), queries become lots easier. While a given query might take a whole flock of tables, the tables needed and the ways to link them together are intuitive for most programmers and many users.

-PatP

So what would the primary key be for the "uses" table? Do you require a primary key to describe a relationshinp? Or do you use composite key's to describe a relationship? Thans they only way I see you can get a unique value without adding another field.

As long as you only use an ingredient once in a reciepe, you can use the combined FK columns to create a PK.

Some reciepes call for the same ingredient in the main part, and again in a topping. If there is a chance that you might use the same ingredient twice in a reciepe, then I'd suggest a surrogate key.

-PatP