0

I am creating a recipe app where you can add ingredients to the recipe. I want the ingredient table to have columns for name, quantity, and measurment. And then connect it to the recipe table. Is it possible to have a array with foreign keys in my recipe table?

If not, how else do you solve issues like this?

Mads Kristian Nylund
  • 17
  • 1
  • 2
  • Does this answer your question? [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy Nov 19 '22 at 00:29

2 Answers2

2

Not a good idea to create a foreign key on a column with array type. You should rather create one table for the ingredients, one table for the recipes, and one intermediate table for the many-to-many relationship between ingredients and recipes, with the quantity of the ingredient in the recipe :

CREATE TABLE IF NOT EXISTS ingredient (name varchar primary key) ;
CREATE TABLE IF NOT EXISTS recipe(name varchar primary key) ;
CREATE TABLE IF NOT EXISTS recipe_composition 
       ( recipe_name varchar
       , ingredient_name varchar
       , ingredient_quantity double precision
       , ingredient_unit_of_measure varchar
       , PRIMARY KEY (recipe_name, ingredient_name)
       , FOREIGN KEY recipe_name REFERENCES recipe(name) ON UPDATE CASCADE ON DELETE RESTRICT
       , FOREIGN KEY ingredient_name REFERENCES ingredient(name) ON UPDATE CASCADE ON DELETE RESTRICT
       ) ;
Edouard
  • 6,577
  • 1
  • 9
  • 20
1

It is possible, but that is a very bad idea and will make you unhappy:

  1. any query that joins the tables will become more complicated and is likely to perform worse

  2. you cannot guarantee referential integrity with a foreign key

Create a junction table recipe_ingredient that stores the relationship between the two.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263