Skip navigation links

MySQL Forums :: Database Design & Data Modelling :: Confused during the initial design.


Advanced Search

Re: Confused during the initial design.
Posted by: Roland Bouman ()
Date: March 14, 2006 05:07PM

Hi!

>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.
You see,

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:

food_material_category:
id
, name

(this would contain 'beverages' and 'food' - I can't think of any more categories here)

food_source_category:
id
, name

(this would contain 'dairy and eggs', 'fish and flesh', 'cereals', 'fruit', 'vegetables')

Now, the food table would look like this:

food:
id
, name
, food_material_category_id
, food_source_category_id

The nutritional information problem is -in part- similarly solved. You would have a separate table with the type of food-'material'

nutritional_component
id
, name

(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:

food_nutritional_component
id
, food_id
, nutritional_component_id
, quantity
, quantity_units

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:

brand
id
, name

You also need to keep track of what manufacturer makes which food products:

brand_food
id
, brand_id
, food_id

than, you can start recording the composition:

brand_food_nutritional_component
id
, brand_food_id
, nutritional_component_id
, quantity
, quantity_units


I hope it helps some

(PS - i think that this is really a bit of a difficult problem)

Options: ReplyQuote


Subject Written By Posted
Confused during the initial design. Aaron Jackson 03/14/2006 09:28AM
Re: Confused during the initial design. Roland Bouman 03/14/2006 05:07PM
Re: Confused during the initial design. Aaron Jackson 03/15/2006 12:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.