I have two tables
recipe table ----------------------- |recipe_id |name | ----------------------- |1 |Pasta | |2 |Pizza | |3 |Fish | ingredient table ------------------------------- |ingredient_id |name | ------------------------------- |1 |Spaghetti | |2 |Salmon | |3 |Tomato sauce|
I created a junction table called “recipes_ingredients”
------------------------------- |id |fk_recipe|fk_ingredient| ------------------------------- |1 |1 |1 | |2 |2 |3 | |3 |3 |2 |
My first question is: is it correct how I created the “recipes_ingredients” table? Do I need the “id” or I can just have the two foreign keys “fk_recipe” and “fk_ingredient”?
And the second one is if it’s possible to autopopulate the junction table when I insert a record in the other two tables. Or do I have to add manually every single association in the junction table?
Advertisement
Answer
My first question is: is it correct how I created the “recipes_ingredients” table? Do I need the “id” or I can just have the two foreign keys “fk_recipe” and “fk_ingredient”?
Your junction table looks great! Your ID in recipes_ingredients will be auto numbered. I’d rename fk_recipe to recipe_id and fk_ingredient to ingredient_id, but it’s totally fine to keep it the way you have it too. It’s preference based.
And the second one is if it’s possible to autopopulate the junction table when I insert a record in the other two tables. Or do I have to add manually every single association in the junction table?
Typically, you would manually enter ingredients that you don’t already have in ingredients table, enter a new recipe name, and finally add entries in junction table.
However, if you have rules that say: All recipes containing the word pizza will have ingredient 1, 2 and 3, then you can create a stored procedure with logic to add information in the junctions table based on your rules. You can’t cover all your use cases using a stored procedure, so you would still have to go to the junction table and add some entries manually.
Typically, web developers create a web page that allow such interaction of creating a recipe using a web page and then allowing selections of (or drag drop of) ingredients. In the background, the web page updates the junction table.