Re: Confused during the initial design.
Posted by: Roland Bouman
Date: March 14, 2006 05:07PM
>I can category the food/drink exactly the same, like this:
>Beverages -> Soft Drinks, Alcohol, _Milk_, Tea and Coffee etc.
>Dairy and Eggs ->_Milk_, Cheese, Yogurt, and Cream etc.
mmm, I'd say this is a little more difficult than is looks superficially.
1) Soft Drinks, Alcohol, Milk etc. could all be called beverages, but Soft Drinks and Alcohol are not literally beverages themselves. Rather, they are also categories. Milk is not - milk is literally a particular type of beverage.
2) Both "Beverages" and "Dairy and Eggs" are categories in the sense that we can use them as bucket terms for a bunch of foods. However, these terms are not 'equal' of you catch my drift: In the first case, the common factor between all foods captured by the term is that they are 'drinkable'; in the second case, the common factor is that they are 'derived from milk, or from eggs'. Of course, these attributes might overlap, which is something you mentioned youreself already.
I think that this second point should be adressed as follows:
-each food is either a beverage or not.
-each food is either one of 'dairy and eggs', 'meat', 'cereal',....
So essentially, you have (at least) two different (orthogonal) category systems that you can use to categorize foods. If you can model these different category systems so that they contain only non-overlapping categories, then you should do so. At least I certainly would.
This would give use:
(this would contain 'beverages' and 'food' - I can't think of any more categories here)
(this would contain 'dairy and eggs', 'fish and flesh', 'cereals', 'fruit', 'vegetables')
Now, the food table would look like this:
The nutritional information problem is -in part- similarly solved. You would have a separate table with the type of food-'material'
(this would contain: protein, carbohydrates, fat etc)
Now, because a particular food can contain a combination of these components, you would need yet another table:
And this would contain the fact that a particular food contains a particular quantity of a particular food component.
However, we have to be careful here - You are interested in the manufacturer too. Because the cola made by manufacturer X could have a different composition than that made by manufacturer Y, you can't solve it like this directly. You need to create a manufacturer or brand table first:
You also need to keep track of what manufacturer makes which food products:
than, you can start recording the composition:
I hope it helps some
(PS - i think that this is really a bit of a difficult problem)