Database table creation help

I am having a hard time trying to create tables for a database I am trying to create. I am starting in Access but eventually it will be ported to SQL, but the forms will be made in Access. I work for a health care organization and I have been given the task to create a database for the Central Kitchen. They prepare food for other institutions. The only information the other institutions give them is the amount requested from the menu and the Central kitchen prepares the food according to the amount they request and then each institution is in charge of preparing the trays.
Sounds simple enough. Problem is they have a 40 year old system that basically everyday they just cut from a form and post the amounts required.

The menu changes everyday according to a weekly cycle. Each year they create 3 cycles wich last a week (From Wednesday to Tuesday). In each cycle there is a different menu for each day of the week.

They have 11 different forms each one is for modified diets (breakfast, lunch, dinner, and cold food which include juices and deserts) with many specifications and one regular diet.
The food requisition form are dived in the following forms:
"      Regular diet which includes menu selection for breakfast, lunch and dinner.
"      Modified diet: Breakfast (Modifications include nonfat milk with salt, non fat milk, without salt, with water, no salt, etc&)
"      Cold diet: Breakfast (Includes: dairy products, juices, and fruits)
"      Modified diet: Lunch Farinaceous (containing flour; starchy)
"      Modified diet: Lunch Vegetables
"      Modified diet: Lunch Meats
"      Cold diet: Lunch (Includes: dairy products, juices, and fruits)
"      Modified diet: Dinner Farinaceous (containing flour; starchy)
"      Modified diet: Dinner Vegetables
"      Modified diet: Dinner Meats
"      Cold diet: Dinner (Includes: dairy products, juices, and fruits)

They need the total amounts for each item from each Hospital (Institution) they serve.

I created a table titled Food and there I include all the different types of foods with a field titled TypeOfFood and ModifiedDietPreparationMethod for the modified diets.

Food Table
      FoodID
      TypeOfFood
      MD (Boolean; if it is a modified diet or not)
      Rate
      Weight (weight of each ration)




I am having a hard time creating the order request form, so far I have the following:

OrderRequestHeader
      OrderRequestHeaderID
      OrderRequestDetailID (Foreign key)
      Date
      Cycle
      Institution
      TypeOfDiet (From one of the 11 forms listed earlier)
Diet (Wheter breakfast, lunch or dinner)

OrderRequestDetail
      OrderRequestDetailID
      OrderRequestHeaderID (Foreign key)
      Food
      Quantity
      MD (Boolean; if it is a modified diet or not)
      ModifiedDietPreperationMethod
      PreperationMethod (Mostly for the preparation method of meats and farinaceous)
      TypeOfFood
      Commentary (For any changes)

Menu Table
      MenuID
      Cycle
      Day
      Date
      Food
      Preperation
Diet (Wheter breakfast, lunch or dinner)
MD (Boolean; if it is a modified diet or not)
      ModifiedDietPreperationMethod
      TypeOfDiet (From one of the 11 forms listed earlier)


I am having a lot of trouble trying to create the menu table. It is just so the Central Kitchen can enter the daily menu for each cycle and each institution can view the menu and select the quantity, hence why I want to transfer the database to SQL, but leave Access for the forms and reports.

Any help anybody can offer will be greatly appreciated.







I noticed that what I had just wasnt working so I basically restructured a bit and started creating the following tables Menu tables, deleting the original Menu Table:

 MenuByCycle Table
      MenuByCycleID
      Year
      Cycle
      Day
      Date
      

MenuByCycleDetail
      MenuByCycleDetailID
      TypeOfDiet
      Food
      ModifiedDietPreperationMethod
      PreperationMethod (Mostly for the preparation method of meats and farinaceous)
Diet (Wheter breakfast, lunch or dinner)
MD (Boolean; if it is a modified diet or not)

      


Trying to normalize even more since each menu is different for the type of diet I tried creating another table MenuByType of Diet

MenuByTypeOfDiet
      MenuByType of DietID
      TypeOfDiet
      MenuByCycleDetailID


And  changing the other tables


MenuByCycle Table
      MenuByCycleID
      Year
      Cycle
      Day
      Date
      MenuByTypeOfDietID

      

MenuByCycleDetail
      MenuByCycleDetailID
      Food
      ModifiedDietPreperationMethod
      PreperationMethod (Mostly for the preparation method of meats and farinaceous)
Diet (Wheter breakfast, lunch or dinner)
MD (Boolean; if it is a modified diet or not)
MenuTypeofDietID

I think I am just making even a bigger mess&

In the end, I need the Central Kitchen to be able to post the menu for at least a week in advanced (1 cycle) and the other institutions be able to view the menu for each diet and daily just enter the quantity for each item.


I am just having a hard time trying to visualize how am I going to add multiple requests per type of diet and menu day in a table.
There are a lot of selections of food per day, some are default meaning that are prepared every day, like eggs, coffee, milk, and a substitute.
Start Free Trial