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.