Postgres database table with unknown number of columns

Question

So I'm trying to figure out how to best approach creating a data storage that contains an unknown number of columns.

So say I have a table with recipes. Recipes usually have ingredients. Some have 5, some 8 some 10. You get the point. What would be the best way to stores recipes when I don't know how many ingredients it will hold?

I thought about using an array inside the table for the ingredients. However, doing a query like "find all recipes that contain the ingredient x" seem to be rather difficult to construct. This is because sometimes ingredients can be "smoked x" or "salted x" for example.

So I wonder if there is a better alternative?

puelo · Accepted Answer · 2020-05-16 18:42:27Z

3

That is what relationships with foreign keys are for. You could have three tables: Recipe, Ingredient and RecipeIngredient. RecipeIngredient will represent the ManyToMany relationship you have between recipes and ingredients (in other words: A recipe can have x ingredients, and each ingredient can be used in y recipes, where x and y > 1). This would allow you to query to all recipes with a specific ingredient, with something like this:

SELECT r.* FROM recipe r 
    LEFT JOIN recipe_ingredient ri ON ri.recipe_id = r.id 
    LEFT JOIN ingredient i ON i.id = ri.ingredient_id 
WHERE i.name = 'Onion'

answered May 16, 2020 at 18:42

puelo

6,0752 gold badges39 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres database table with unknown number of columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related