CODEWITHSUNDEEP
mysqlsqldatabase
Stephan Bijzitter
Stephan Bijzitter

Reputation: 4595

database with array-like support?

So I've got some information to store, let's dumb it down to food to create an example. A recipe contains 1 or more ingredients, each ingredient has also got an amount.

So a recipe (one row in a table called recipe) could contain: id : 0 name : something disgusting ingredientid1 : 0 ingredientamount1 : 20 ingredientid2 : 1 ingredientamount2: 6 ..etc

I've only got experience with mysql so far, but I'm willing to learn. In essence, I could serialize the ingredients to a string and store that string to then use a LIKE operator to find all recipes with a specific ingredient... but I would be disgusted by a solution like that.

The goal is to make the end-user able to give the server a list of ingredients to get a list of recipes returned, which can be made with those ingredients.

How would I do something like this without using a LIKE operator?

Upvotes: 1

Views: 89

Answers (1)

vp_arth
vp_arth

Reputation: 14992

You should implement it as separate table:

Recipe('id', 'name'...)
Ingredients('id', 'recipe_id', 'name', 'amount')

Then, you can simply get all ingredients by WHERE recipe_id = x

Also, if you want to share ingredients with any recipes. you should use tree tables, like:

Recipe('id', 'name')
Ingredients('id', 'name')
RecIngr('recipe_id', 'ingr_id', 'amount')

Then, you can get all recipe ingredients by join:

select i.name, ri.amount From Ingredients i
inner join RecIngr ri on ri.ingr_id=i.id
where ri.recipe_id = x

Upvotes: 4

Related Questions