How do i properly design a data model for Recipe / Ingredient using MongoDB

Question

Recently i have designed a database model or ERD using Hackalode. So the problem I'm currently facing is that base on my current design, i can't query it correctly as I wanted. I studied ERD with MYSQL and do know that Mongo doesn't work the same

The idea was simple, I want a recipe that has a array list of ingredients, and the ingredients are from separate collection. The recipe also consist of measurement of the ingredient ie. (1 tbps sugar)

Can also query from list of ingredients and find the recipe that contains the ingredients

I wanted this collections to be in Many to Many relationship and the recipe can use the ingredients that are already in the database.

I just don't know how to query the data

I have tried a lot of ways by using $elemMatch and populate and all i get is empty array list as a result.

Im expecting two types of query where i can query by name of ingredients or by the recipe

My expectation result would be like this

[{
   id: ...,
   name: ....,
   description: ...,
   macros: [...],
   ingredients: [
   {
       id,
       amount: ....,
       unit: ....
       ingredient: {
           id: ....,
           name: ....
       }
   }
}, { ... }]

But instead of getting

[]

Answer 1

Imho, your design is utterly wrong. You over normalized your data. I would do something much simpler and use embedding. The reasoning behind that is that you define your use cases first and then you model your data to answer the question arising from your use cases in the most efficient way.

Assumed use cases

1. As a user, I want a list of all recipes.
2. As a user, I want a list of all recipes by ingredient.
3. As a designer, I want to be able to show a list of all ingredients.
4. As a user, I want to be able to link to recipes for compound ingredients, should it be present on the site.

Surely, this is just a small excerpt, but it is sufficient for this example.

How to answer the questions

Ok, the first one is extremely simple:

db.recipes.find()[.limit()[.skip()]]

Now, how could we find by ingredient? Simple answer: do a text index on ingredient names (and probably some other fields, as you can only have one text index per collection. Then, the query is equally simple:

db.recipes.find({$text:{$search:"ingredient name"}})

"Hey, wait a moment! How do I get a list of all ingredients?" Let us assume we want a simple list of ingredients, with a number on how often they are actually used:

db.recipes.aggregate([
  // We want all ingredients as single values
  {$unwind:"$Ingredients"},
  // We want the response to be "Ingredient"
  {$project:{_id:0,"Ingredient":"$Ingredients.Name"}
  // We count the occurrence of each ingredient
  // in the recipes
  {$group:{_id:"$Ingredient",count:{$sum:1}}}
])

This would actually be sufficient, unless you have a database of gazillions of recipes. In that case, you might want to have a deep look into incremental map/reduce instead of an aggregation. Hint: You should add a timestamp to the recipes to be able to use incremental map/reduce.

If you have a couple of hundred K to a couple of million recipes, you can also add an $out stage to preaggregate your data.

On measurements

Imho, it makes no sense to have defined measurements. There are teaspoons, tablespoons, metric and imperial measurements, groupings like "dozen" or specifications like "clove". Which you really do not want to convert to each other or even set to a limited number of measurements. How many ounces is a clove of garlic? ;)

Bottom line: Make it a free text field, maybe with some autocomplete suggestions.

Revised data model

Recipe

{
  _id: new ObjectId(),
  Name: "Surf & Turf Kebap",
  Ingredients: [
    {
      Name: "Flunk Steak",
      Measurement: "200 g"
    },
    { 
      Name: "Prawns",
      Measurement: "300g",
      Note: "Fresh ones!"
    },
    {
      Name: "Garlic Oil",
      Measurement: "1 Tablespoon",
      Link: "/recipes/5c2cc4acd98df737db7c5401"
    }
  ]
}

And the example of the text index:

db.recipes.createIndex({Name:"text","Ingredients.Name":"text"})

The theory behind it

A recipe is you basic data structure, as your application is supposed to store and provide them, potentially based on certain criteria. Ingredients and measurements (to the extend where it makes sense) can easily be derived from the recipes. So why bother to store ingredients and measurements independently. It only makes your data model unnecessarily complicated, while not providing any advantage.

hth