Junction table in PostgreSQL-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

It would be better if the junction table had no id column. The primary key should be on (fk_recipe, fk_ingredient) – this should be a unique and not NULL anyway.

You'll have to add elements to the junction table explicitly - just because you add a new recipe, it is not yet clear which ingredients are required. The junction table should only have an entry if a certain ingredient is needed for a certain recipe.

The junction table can and should include additional data, like the amount of the ingredient that is required.