2 Replies Latest reply on Sep 22, 2014 5:18 PM by davidskim

    Relational Database Design Setup

    davidskim

      I'm not able to see the forest from trees on this design. I'm trying to design a housekeeping room assignment and scoring database for a 63 room hotel. (it's actually my own hotel). Here are my objectives. I need help on the relational database design.

       

      1) Dynamic database of Housekeepers that become active or inactive so that even if they leave, their name and score doesn't dissapear from the score averages.

       

      2) Room assignments are on a per day basis and are manually assigned.

       

      3) Database of rooms is static

       

      4) Room data will be used to create a housekeeper worksheet that lists the following per housekeeper

      • Room #
      • Housekeeper Name
      • Expected time to complete the day
      • Checkout Date (to be manually entered by night person)
      • Do Not Disturb sign shown checkbox
      • Pet in Room or not checkbox
      • Rollaway bed in room or not checkbox
      • Score #1 from Room Auditor and initials (Room auditor may be different people - could be housekeeper lead or a front desk staff or me)
      • Score #2 from Room Auditor and initials (Room auditor may be different people - could be housekeeper lead or a front desk staff or me)
      • Notes section per room

      5) Scoring module to go back and manually enter scores received for the day

      • Time per room (Different rooms have different times and based on if they are a checkout or stay another night)
      • Summary of scores per housekeeper based on Total score/rooms done
      • Time adjustment will change Score average based on actual time vs expected time
      • Housekeeper total time for the day
      • Housekeeper chore time (Calculation that subtracts total time for day minus room time)

       

      5) Be able to click on button to create a new form for a day where a person selects all occupied rooms from master room list that creates a new record for a date for assigned rooms.

       

      My expected database structure was as follows, but I wasn't sure.

      Database #1 (rooms)

      • Room # (primary key)
      • Room Type
      • Time to complete room as a checkout
      • Time to complete room as a stayover

       

      Database #2 (Houskeepers)

      • Housekeeper Name
      • Housekeeper ID (primary key)
      • Housekeeper Cell
      • Housekeeper Title

       

      Database #3 (Auditors)

      • Auditor Name
      • Auditor ID (pimary key)

       

      Database #4 (HK assignments) join table

      • Room# (foreign key from DB #1)
      • Housekeeper ID (foreign key from DB#2)
      • Housekeeper name (from relationship from DB#2)
      • Date
      • Score #1
      • Score #2
      • Auditor ID #1 (lookup list fromDB#3)
      • Auditor ID #2 (Lookup list from DB#3)
      • Do Not Disturb
      • Pet in Room
      • Rollaway Bed in room
      • Time to complete room as a checkout (from relationship from DB#1)
      • Time to complete room as a stayover (from relationship from Db #1)
      • Summary score #1
      • Summary score #2