Database schema for food records

[Cong]

Hello, I am creating a calorie counting webpage. And I am not sure about MongoDB database schema for food records. It should work like this:

• day is separated into parts (breakfast, lunch, dinner)
• you can save a food record into one of those parts. e.g. Egg - 3x50 (grams) (3 eggs)
• there is a page for every day. Page is separated into day parts and recorded food is listed here. There is a food information - calorie count/proteins/... per food/day part/day

I have a food collection. example:

{ 
  _id: ObjectID("X"),
  name: "egg",
  calories: 140,
  protein: 12,
  ...
}

(calories and proteins per 100g)

Food records collection. example:

{
  food: ObjectID("X"),
  count: 3,
  weight: 50
}

And I am not sure where to save information about day parts / days. Should I save it in food record?

{
  food: ObjectID("X"),
  count: 3,
  weight: 50,
  day: "2015-11-05",
  part: "lunch"
}

Or should I create a separate collection for day parts?

Or do you have any other solution? Thanks for your help.

[Dwight Merriman]

if you have a page per day, i take it that is the UI of the app -- the schema need not mirror that necessarily. 

i'd do something like this:

1) have a foods collection as you mention.  one aside: you can use objectid's for the _id value, but you might find it makes sense to use simple food names  for the _id value.  just keep in mind that _id's are immutable so if the food name will change don't do that.  but if the name is descriptive you can see it in the meal documents below without looking up the food document.  i'd have a long_name field with a longer name for the food, and also that gives you an opportunity to tweak the long descriptive name , but not the _id, as time passes.

2) have a meals collection.  each meal is something like this format: 

{ _id : ..., 

  person: "joe123",

  date: ... , 

  time : ...,

  type : "lunch",

  items : [ 

    { item : "egg", qty : 2, weight : 100 },

    { item : "bread", qty : 1, comments : "toast" }

    ...

  ],

  total_calories : 999,

  ...

}

then to make the page query meals for all documents on the date in question.

[Cong]

Thanks for your answer.

Users should be able to edit all recorded food (change quantity, weight, move from lunch to dinner, delete). That's why I think is better to use single records for every food. Or is the schema you wrote better for this?

[Adele Palumbo]

Cong I agree with dwight. You need to remember mongo is schemaless. This means you don't need to worry about the size or changes made to one document. You original schema isn't wrong it's just not mongo. Creating multiple collections would mean more querying which would negate the speed enhancements you get with mongo.
I know my response doesn't answer your question directly but you should review https://docs.mongodb.org/v3.0/core/data-model-design/. You should only normalize (something that we all learned is a must in relational databases) if you actually benefit from it. In your case dwight design gives you the same functionality and still retains the speed which mongo provides over sql.

As I said your "schema" isn't wrong. It just negates the performance increases you get with a schemaless db like mongo. It's not so much your logic but howe you're viewing mongo. Personally I had to throw everything I learned out the window to be able to think differently. If you want to see how fast your queries are you can go to the terminal and query with the stats showing. This will help you determine if normalization will actually be of any benefit to you.

[Cong]

Yes, I guess I still think about it in relational db way. I have to look into more design examples. I will use the Dwight's design.

Thanks for your help.

I might ask few more questions about this food database later on.