Hello,

I'm having some troubles trying to design a database and would appreciate any feedback.

I am trying to store nutrition information for items at various restaurants. So far, I've identified the following tables:

restaurants (id, name, created, modified)
categories (id, name, created, modified)
retaurants_categories (restaurant_id, categories_id) HABTM relationship
items (id, name, calorie_grams, fat_grams, fiber_grams, created, modified)
options (id, item_id, name, calorie_grams, fat_grams, fiber_grams, created, modified)

And that is where I'm stuck. I've tried a lot of different combinations but keep getting stuck. Let me explain the data and hopefully someone will be able to point me in the right direction.

Using a coffee shop as an example:
A restaurant can have many different categories (espresso-hot, espresso-iced, etc), and conversely, a category can belong to different restaurants (starbucks, peet's coffee, etc).

An item (mocha, latte, etc) may have options like type of milk, with or without whipped cream, etc. I guess it would be possible for those options to relate to many different items, but right now I am thinking that it would be a 1 to many (items to options) relationship.

Lastly, an item belongs to a category, but for a specific restaurant. And that seems to be one of my big confusion points. What is the best way to show that relationship in a table. If I put a FK reference to restaurants in items, I wouldn't know what category it belonged to. So, I'd have to put a FK reference to categories...but then do I even need the many-to-many table for retaurants_categories?

I guess on option is to put an id in the retaurants_categories table and put a FK from items to that id...but that doesn't seem right, for some reason.

Thanks for any help =)