Confused during the initial design.
I have a task at hand, and basically it's more or less putting a database together similar to that of www.fitday.com
For those unfamiliar to the site it's used for food logging, planning, calorie counting, activity monitoring and so on. The only aspect I am looking at is the food side of things.
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.
This type of duplication will occur all over, so I gathered I will have let's say something like this:
______________________
ID CategoryName
1 Beverages
2 Dairy
ID Food Member Of
1 Milk 1,2
______________________
I will end up having a web of information, a lot of which will be relating to each other, sometimes it won't be, and I just don't know how to nail it down.
Sorry about the poor diagram, though you should get what I mean. Now this may seem fine, however there is another problem, all products will have nutritional information (protein, carbohydrates, fat) and so on, but along side:
type(beverage) -> manufacture(tesco/asda) -> label(healthy living/for kids) -> foodname(Sugar Free Cola / Mineral Loaded Fruit Juice) -> Nutritional Information Here
Type will always exist, but will sometimes have multiple categories and should have for ease of use/flexibility, manufacture will nearly always exist unless it's something like 'Fresh Meat', the label is a subcategory, IE a brand has a range of foods like 'Tesco -> healthy living series -> Fat Free Salad Dressing' yet a lot of the time it won't exist and it will just be "Tesco -> Whole Milk", and then of course the food name which again will always exist.
I just don't know where to start and it's driving me mad. I am not asking anyone to do this for me, just a little advice and a knock in the right direction to help me work out what's the best way to sort the information out. I do understand Normalisation and so on, but I think that's where I am hitting the wall.
Edited 1 time(s). Last edit at 03/14/2006 09:29AM by Aaron Jackson.