I'm creating a practice app which is essentially a Recipes database.
I'm trying to include a feature that allows users to search by ingredients, as many as they want.
Currently, my Recipe model has an ingredients column. And the ingredients is one long string. I am using Sequelize and by having all the ingredients in one long string, it allows me to query Recipes by multiple ingredients.
Consider the below. Please bear in mind, I only have 1 table (Recipes) currently.
**RecipesTable**
Recipe Name | Ingredient (1 long string)
-----------------------------------------
RecipeOne | "Tomato, Mushroom, Potato"
RecipeTwo | "Brocolli, Lettuce, Potato"
RecipeThree | "Tomato, Olives, Lettuce"
With my current setup, I can look query (easily with Sequelize) for Recipes that has Tomato, Potato - which will return RecipeOne. If I search for Tomato, Potato, Lemon, I will get nothing, because no strings contain those 3 words/ingredients.
I'm still new to databases and software engineering in general; but from what I've been reading... this breaks normalization (which I still need to understand its meaning).
Other threads on Stack Overflow advises to break Recipes and Ingredients into 2 separate tables, and create a relationship between them.
My question is this...
Should I break Recipes and Ingredients into 2 separate tables, but keep the ingredients as 1 long string.
**RecipesTable**
Recipe ID | Recipe Name
-----------------------------------------
1 | RecipeOne
2 | RecipeTwo
3 | RecipeThree
**Ingredients**
Ingredients ID | Ingredients
-----------------------------------------
1 | "Tomato, Mushroom, Potato"
2 | "Brocolli, Lettuce, Potato"
3 | "Tomato, Olives, Lettuce"
**RecipesIngredients**
Recipe ID | Ingredient ID
-----------------------------------------
1 | 1
2 | 2
3 | 3
or should I break the ingredients into each row; which will make the Sequelize query much more difficult. I have attempted this and I'm not getting the results I'm looking for
**RecipesTable**
Recipe ID | Recipe Name
-----------------------------------------
1 | RecipeOne
2 | RecipeTwo
3 | RecipeThree
**Ingredients**
Ingredients ID | Ingredients
-----------------------------------------
1 | "Tomato"
2 | "Mushroom"
3 | "Potato"
4 | "Broccolli"
5 | "Lettuce"
6 | "Potato"
7 | "Tomato"
8 | "Olives"
9 | "Lettuce"
**RecipesIngredients**
Recipe ID | Ingredient ID
-----------------------------------------
1 | 1
1 | 2
1 | 3
2 | 1
2 | 2
1 | 3
2 | 3
3 | 3
3 | 3
If I break each ingredient into a record, and search for Tomato ingredient, I will get RecipeOne and RecipeThree. But if I add more ingredients to the query, I will get nothing because none of the strings contain multiple ingredients; as they're all single ingredient.
I can probably do something hacky, where I query one ingredient first, then .filter the rest. But ideally, I query via Sequelize only, rather than having to resort to a javascript array method...
With that said, apologies for the long post; but I'd appreciate any input on which option I should go with. I looked into Spoonacular API, and they seem to have their ingredients in one long string.
EDIT:
Ingredient.findAll({
where: {
[Op.or]: {
name: {
[Op.iLike]: array of ingredients
}
include: [Recipe]
}
}
});
.then(results => {
use .filter(results) or ES6 Set to remove the duplicates from the results
})
