Database table creation confusion (1 Viewer)

[Pap114]

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.

 

[statsman]

Just goes to show there are many ways to skin a cat, especially in Microsoft.

Personally, I would attack the problem from a different direction. As food can only be ordered based on the menu for each cycle, my primary data would be the cycle and the menu for that cycle.

Based on this table, everything else would simply be an expansion of this basic set up.

Table: Cycle_1
Day - an entry for each day in the cycle
Breakfast - what's available for breakfast on the day listed in Day
Lunch - whats available for lunch on the day listed in Day
Supper - what's available for supper on the day listed in Day.

You may wish to further divide the table Cycle_1. For example - Supper Appetizer_1, App_2 etc.
Supper Entre_1 Entre_2
Supper Dessert_1 Dessert_2 etc.

The patient's dietary restrictions would be considered in the patient table. If they have condition "A" they would be restricted in the selections that could be made.

 

[Pap114]

I noticed that what I had just wasn’t working so I basically restructured a bit and started creating the following tables Menu tables:

“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.

 

[Pap114]

I just have 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 prepaired everyday, like eggs, cofee, milk, and a substitute.

 

[The_Doc_Man]

I'm going to give you some painful advice. You aren't ready to port this to Access yet.

Why? Because you are having trouble explaining the problem. Old programmer's rule #1 is "Access won't do in a computer anything you couldn't have done yourself on paper."

I.e. if you don't have a gut-solid feel for a problem, starting to use Access right now is like throwing darts at a dartboard while blindfolded. The odds of even hitting the board aren't that good.

There is NO substitute for problem pre-analysis. Here is where you start, and this is one person's opinion not guaranteed to be any better than yours... but perhaps different enough to get you thinking in a different direction.

Identify the entities. ("What?" you say...) That is, you are modeling something in order to track it. But what are you really tracking?

You seem to be tracking food in order to requisition it. So you are modeling food consumption. I.e. this is a food inventory system.

Next entity: Consumers. You need to know something about who consumes each type of food.

Next entity: Diets. You listed cold-food, several modified diets, etc.

The problem is then to somehow MAP consumers to diets and diets to foods so that you can predict/track consumption of various foods.

I'll demonstrate something called a junction table, which is used to join many-to-many relationships through an intermediate table. (Because Access won't directly do many-to-many joins in a single operation.)

tblFoodDiet
FoodID - the code number for a particular food item.
DietID - the code number for a particular diet.

If an entry is made in this table, then the food with the given ID is on the diet with the given (different) ID.

Now a question: Can a person be on more than one diet? If not, then the diet ID number can be in a person table. If they can, you need a junction table to link each person to the diet they are on.

Eventually, though, you must face the fact that not every food will be on every daily menu even on restricted diets. I.e. I might be allowed bacon and eggs on my diet but I won't eat bacon and eggs every day. So you have to take into account the frequency of appearance. This is where you make life complicated.

I might take a different approach, but this requires some predictive capability. The above discussion isn't wrong, but it might be only half the problem. See, what you REALLY need to do a food inventory and requisition is to know ahead of time what is actually on the menu for each day and THEN decide how many people would get that by knowing the diet they are on. In other words, it is not enough to know that bacon is on my diet and George's diet. (Do you detect a "bacon" trend here?) You have to know how often it will appear. Which means you should have a dietary pattern figured out farther ahead of time than you need to place and receive your orders. Once you have the diet patterns, you can then look up the numbers of people getting samples from each pattern, and from THERE, you can accurately predict what you need to order.

Hope I didn't confuse you more than I helped you. But sometimes that is actually a good thing, believe it or not. Because it suddenly stops you in your tracks with realization of what ELSE you needed to know. Good luck.

 

[Pap114]

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.

 

[Pap114]

Right now I am focusing on the Menu system, then the Order system, so I made reconfigured the following tables:

“MenuByCycle” Table
MenuByCycleID (PK)
Year
Cycle
Day
Date

“MenuByCycleDetail” (Junction Table)
MenuByCycleID (PK)
TypeOfDietID (PK)
FoodID
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)

“TypeOfDiet” Table
TypeOfDietID (PK)
TypeOfDiet


Since, I have MenuByCycle which has many types of diets and each has many orders and inside those orders are many types of food and methods of preparation.

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).

 

[Pap114]

I have been normalizing tables, because of my problem trying to relate the tables by menu cycle, by day, and by diet. In the process I ended up created a menu subsystem. These were the related tables I restructured:

MenuByCycle
MenuByCycle (PK)
Year
CycleID
StartDate
EndDate

MenuByDay
MenuByDateID (PK)
Day
Date
MenuByCicleID (FK to MenuByCycle Table)

MenuByDiet (Junction table between MenuByDay and TypeOfDiet tables, since many foods per day and type of diets)
MenuByDietID
TypeOfDietID (PK)
MenuByDay (PK)

FoodByMenu
FoodByMenuID (PK)
MenuByDietID (FK to MenuByDiet Table)
FoodID
ModifiedDietPreperationID
MethodOfPreperationID


I was thinking of linking the Order Tables with the Menu tables.

If I create the order tables I will have to normalize the same way I did with the Menu tables?
Example: OrderByCycle, OrderByDay, OrderByDiet, etc..

 

[gemma-the-husky]

I know this wont help

What the docman is saying is that this particular problem is one where it will be very difficult to just dive in and start developing.

you need to think of the system in global terms first, not in terms of the details - ie design the house before you add the furniture

so think of the inputs and outputs - what information are you trying to get out of the system - what information is going in - what drives the system

how do you decide what meals to do each day - do you just make stuff, distribute it, throw left overs away - or do you get requests in from institutions, and then decide how much of everything to use - do you supply them with extra stuff for their new patients etc who werent on the original order form

assuming the data driver is the institutions requests then what are the time constraints you have - is it same day, next day etc etc

you need to really understand this whole business process before you start thinking about how to develop this - for example if you have some tried and trusted spreadsheet mechanisms it may be that the first step is to build the conceptual
framework to capture the orders in terms of finished meals, and use access to generate a summary that the spreadsheets can turn into detailed kitchen instructions. When that works, then look at converting the spreadsheets.

how big is your organisation? do you have a budget for this? it sounds like a big job to me, its the sort of thing where you could (and should) easily spend days or weeks on a design phase, before you even thought about implementation. You need to be completely familiar with your system, as well as familiar with what Access can offer. If you are new to Access, then you will almost certainly need some professional help. Its quite similar really to the considerations that would be appropriate if you were going to find an external solution. You would still need to document exactly what you wanted before you would be able to approach prospective suppliers, unless there are external solutions already available.

if you do just start developing on the fly, and see how things go, you would have to be prepared to keep going back, and redesigning a lot of things that probably dont work properly the first time?