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 which 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 forms 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 to know the total amounts (rations) 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 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.
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 tried making a simple ERD which is as follows: A Menu has many Cycles (3). A Cycle has many Days (7). A Menu has many TypesOfDiets (11). A Menu contains many types of Food. An Institution orders from many different Menus (According to TypesOfDiet).
I am just having a hard time trying to visualize how I am going to add multiple requests per type of diet and per 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.
My main worry at the moment is trying to create the tables and the relations for the ordering system since I see it as having two parts:
1. The Menu which includes multiple lines per type of diet for each day in each cycle of a year. The Menu is created by the Central Kitchen.
2. Then there is the actual ordering system which is what the Institutions order for their patients all they will see is the menu that the Central Kitchen created. The institutions should be able to see the rest of the menu for at least 3 days in advanced. They should already have on hand the amounts required for each diet. From the menu they will select the amounts for each item available in the menu per type of diet per day in the cycle.
After a predetermined time the Central Kitchen will not accept anymore orders and the food is then prepared according to the total of each particular menu item. The institutions must be able to go back and change if necessary an amount before the closing time of the diet.
This is basically the system I am trying to create. But am just stuck on those two points. After that I need to create reports of each type of food ordered per day and I guess later on per month which adds the weights of the food per ration ordered. For example, so the butcher knows how much meat to cut. Then an Invoicing system according to the price per food per ration that each Institution ordered.
Any help anybody can offer will be greatly appreciated.
Previous Topic
