Newbie database: Same foreign key to one table?

Question

I am attempting to build a Java program that stores food recipes for myself. The program should be able to store, search and change recipes stored in a database. Currently I am using Access 2003 since I am accustomed to it but I will most likely use MySQL later on.

The issue here is most likely very basic but what I am having a problem with is implementing my logical model and I'd like to know how solve this or if there are any better ways to do this.

So here's the thing, here's a concrete example: I want to store ingredients in the database, like "carrot". However many items have alternate names, especially since I am not even english so: Let's say Carrot is Carrot in english. Now Carrot is "Morot" in my language. Maybe later on I watch Jamie Oliver's shows and he calls the carrot something like "shapdoodles" or something.

The reason I want to store these ingredients is because if I add these three recipes and then later on search on "carrot", I want the program to list not only recipes involving carrots but also morot and shapdoodles because, well, they are also carrots, just different names!

It sounds simple enough and at first I thought it was merely as easy as to have a table "Ingredient" with one textfield. The text would be a primary key and it would store the ingredient name such as "carrot" or "shapdoodle". This table would be connected with the table "Ingredientlist" and Ingredientlist would lead to "RecipeDescription" and so on.

I assumed I could simply add another table with two textfields, name and alternatename and just make the Ingredient's textfield a foreign key to both of these two textfields and these two textfields together would serve as the primary key for the table. I was kinda thinking of this kind of result:

AlernateIngredientTable:
carrot  shapdoodle
carrot morot

And so on... So when later on I could simply send queries to find everything in this table that is related to "carrot" and from there on find the recipes involved with carrot or shapdoodles or morot.

Well, since I could not add the same foreign key to two fields in a table I am kinda lost!

Answer 1

You should change your model and create a table associating an ingredient with all its names. Every ingredient that is equivalent to carrot will shere the same id. Here is an example:

table Ingredient(id, name)

id    Name
---------------
1    carrot
1    shapdoodle
1    morot
2    potato
2    patata
3    sugar
3    brownsugar
3    anothernameforSugar

And a table for recipes:

table recipe(id, name, preparation_time)
1    carrot cake    45

A 3rd table to associate a recipe with its ingredients

table recipeToIngredient(recipeId, ingredientId)
1    1
1    3

In this example, I defined a "carrot cake recipe" which has ID 1 This recipe has 2 ingredients : carrot (id 1 in table 'ingredients'), and sugar (id 3)

To retrieve all the recipes involving carrots/morots/etc :

SELECT recipe.name FROM recipeToIngredient, recipe
WHERE recipeToIngredient.recipeId=recipe.id
AND ingredient.id = recipeToIngredient.ingredientId 
AND recipeToIngredient.ingredientId = (SELECT id FROM ingredients WHERE name'carrot')

Basically, what we've done here is to put an 'id' field for the ingredients, instead of using your model where the ingredient name is the primary key. This way, while the name will change according to the language, the id always the same for a given ingredient, and you can run queries according to the id, not the ingredient name.

Let me know if I can help/explain more.