Skip to main content
Database Administrators

Return to Answer

Edit after comment presented more information.
Source Link
DerekCate
DerekCate
  • 438
  • 1
  • 4
  • 7

Some thoughts on the design of this database:

  • For portions, is there only ever one per food? If so it belongs in the food table.
  • Similarly, with the nutritional facts, only per food? If so, probably just in the food table. If there are multiple portions, it would seem like the nutritional facts are based on the portion.
  • And for types, only one per food, or can a food be listed as both a lunch and dinner food? If you have multiple portions, you might have a lunch portion and a dinner portion, and as such the type would be as part of the portions.

So, if only one portion, one nutritional fact, and one type per table, I'd just have it all in the one Food table, potentially with indexes on fields the user may search on.

(edited after more information)

Based on there being multiple portions per food, and the nutritional facts being dependent on the portion, it would make sense to put the nutritional facts in the portion table. It would seem like your Food and Type tables can remain as you originally posted, so the Portions table looks something like this:

PORTIONS
==========
ID
PORTION
FOOD_ID
KCAL
FAT
SUGAR
PROTEINS

Some thoughts on the design of this database:

  • For portions, is there only ever one per food? If so it belongs in the food table.
  • Similarly, with the nutritional facts, only per food? If so, probably just in the food table. If there are multiple portions, it would seem like the nutritional facts are based on the portion.
  • And for types, only one per food, or can a food be listed as both a lunch and dinner food? If you have multiple portions, you might have a lunch portion and a dinner portion, and as such the type would be as part of the portions.

So, if only one portion, one nutritional fact, and one type per table, I'd just have it all in the one Food table, potentially with indexes on fields the user may search on.

Some thoughts on the design of this database:

  • For portions, is there only ever one per food? If so it belongs in the food table.
  • Similarly, with the nutritional facts, only per food? If so, probably just in the food table. If there are multiple portions, it would seem like the nutritional facts are based on the portion.
  • And for types, only one per food, or can a food be listed as both a lunch and dinner food? If you have multiple portions, you might have a lunch portion and a dinner portion, and as such the type would be as part of the portions.

So, if only one portion, one nutritional fact, and one type per table, I'd just have it all in the one Food table, potentially with indexes on fields the user may search on.

(edited after more information)

Based on there being multiple portions per food, and the nutritional facts being dependent on the portion, it would make sense to put the nutritional facts in the portion table. It would seem like your Food and Type tables can remain as you originally posted, so the Portions table looks something like this:

PORTIONS
==========
ID
PORTION
FOOD_ID
KCAL
FAT
SUGAR
PROTEINS
Source Link
DerekCate
DerekCate
  • 438
  • 1
  • 4
  • 7

Some thoughts on the design of this database:

  • For portions, is there only ever one per food? If so it belongs in the food table.
  • Similarly, with the nutritional facts, only per food? If so, probably just in the food table. If there are multiple portions, it would seem like the nutritional facts are based on the portion.
  • And for types, only one per food, or can a food be listed as both a lunch and dinner food? If you have multiple portions, you might have a lunch portion and a dinner portion, and as such the type would be as part of the portions.

So, if only one portion, one nutritional fact, and one type per table, I'd just have it all in the one Food table, potentially with indexes on fields the user may search on.