Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2008
    Posts
    3

    Database table creation confusion

    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.

  2. #2
    Join Date
    Sep 2008
    Posts
    3
    I noticed that what I had just wasn’t 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •