451

Overview of the Proposed Data Stores

Following is a list of the data stores (essentially they will become the tables of the database) and the fields inside each of them. Anything with a star beside it will represent a keyfield, or a part of the keyfield.

Customer

This will be one of the main data stores containing all personal information about the customer. The following fields are in the data store:

Employees must be able to search the following databases given any of the criteria marked with a .

*confirmation ID: Integer assigned when reservation is made
first name: string
last name: string
street: string
city: string
province: string of 2 letters ie: AB for Alberta
postal code: string
credit card type: string ie: Mastercard, Visa, Amex
credit card number: string
credit card expiry: date tends to be a month/year format
credit card name: string could be a business credit card
photo ID: string this tends to be the driver's license number
arrival date: date this is the projected arrival date
duration of stay: integer number of days to book essentially
checkout date: date this is when the customer actually checks out
*room number: integer the room they are assigned to
number occupants: integer this is based on per room as someone could book more than one room
payment method: string cash; credit card; cheque
discount name: string The name of the discount card or the reason.
discount percent: real The percent discount on the room.
employee who booked: (employee ID) integer so we know who booked them in
employee who signed them in: integer who actually signed them in
employee who signed them out: intefer who actually signed them out

Room

This data store will use both room number and day as the keyfield so the rooms will be listed in here more than once possibly but the day will make it unique. To check the availability of the room, this can be derived from the customer information for each entry in here.

*room number: interger
room type: string deluxe (50); single (50); double (100)
*day: date this is the day the room is booked
confirmation ID: interger so we know WHO has the room booked

Employee

This data store will be neccessary in order to determine security access levels in particular, as well what department they are a part of.

Read access to the emplyee database should be restricted to managers.

*employee ID: integer this is the unique identifier
employee first name: string
employee last name: string
employee password: string
employee type: string management; front desk; accounting; room service

Room Services

Included in this table food orders, phone bills, and wake up calls will be maintained. A unique order number will be issued for each order placed.

*order ID: integer
item ID: integer this links the price of item in
time ordered: time
date ordered: date
time to deliver: time
date to deliver: date
room number: integer
employee ID: integer this is to mark who took the order
comments: string incase there are extra instructions etc.

Fees

This is where we actually maintain the item information for all foods, movies, GST, room rates etc.

*item ID: integer the unique identifier for everything
name of item: string ie: movie, phone bill
cost per unit: float

Invoices

This is the pending invoice information. Due to the fact that we intend to use a relational data base only pending invoices are kept in this table. Otherwise, if a fee was changed in the fees datastore it would change the amounts on paid invoices. Each invoice is issued a unique invoice number. If a customer has more than one room booked he will be issued an invoice for each room.

There should be only one invoice number per person making reservation per room per stay. There may also be several items listed on the invoice.

*invoice number: integer unique invoice identifier
room number: integer
employee ID: integer the employee who printed invoice
date when generated: date
item ID: integer
date for item on invoice: date
time for item on invoice: time
intended payment method: integer how the invoice was paid for