PLEASE HELP

Fry Properties, Inc. owns 3 boutique hotels located in Northern Utah. So far, the company has managed to keep guest reservations and accounting "on the books" using a manual spreadsheet system (see sample data below). However, the company would like to upgrade to a computerized database to keep better records and track reservation information for all their properties simultaneously. The new database will eventually be integrated into a new software package called The FARM (Fry's Accommodation and Reservation Management) System. Eventually, the owners would like to host the FARM on the cloud, and offer online reservations. The web site will let guests browse the hotel information (including ratings, amenities, and a single picture of the front of the hotel), view room information (type, descriptions and rates), and make (or edit or cancel) a reservation from the web. Before they can do this, however, the current management needs to move away from their old manual spreadsheet way of doing things.

Here is more information about Fry Properties' business rules (which may or may not impact your data database storage design). Note some rules may need to be enforced with programming).

Thats not your job. You are to focus on things that will apply to data storage only by designing the best (and scalable for the future) relational database diagram as possible. Keep in mind, the database will be physically scripted for MS SQL Server.

1. The current manual system stores guest names (first and last) together in a single field as well as city, state, and country in one field. This makes it difficult to sort and search for guest information and reservations. The owners do not like this system, and would like it fixed so guest information can be found easier. They would also like additional information collected about the guest (like address, phone, e-mail, and even comments about each guest that only the staff can see). Note, not all guests are from the United States so address storage needs to take this into account. Unique guest ids are automatically generated starting with #1500 and incremented by 1.

2. Hotel numbers (ids) are fixed (2100, 2200, & 2300), as well as the actual room numbers that are assigned at each hotel. For example, both hotel # 2100 and # 2200 could each have a room 101. These numbers will not change.

3. Each hotel has various room types (like Single, Double, Suite, etc.) that all have the same rack rate (for a particular room type within that particular hotel). A rack rate is the standard walk-in rate given to everyone for a particular room type, without any discounts. For example: A rack rate for any double room (noting the same type of room throughout the same hotel costs the same) may be $69, depending on the check-in date (rack rates may be different during different times of year or date ranges). Smoking rooms always cost more than (the same type) of non-smoking rooms. Each room type has a general description and can have many specific room numbers assigned to a specific room type category in each hotel (for example, room numbers 101, 102, and 103 all may be double type rooms) at a particular hotel, but could be something different at another.. Each room, regardless of type, will have a specific description (like "this room overlooks the Wasatch Mountains"), a smoking or non-smoking designation (even if the type says smoking in its description), and a bed configuration (like 2 Queen Beds, or 1 King Bed), even if it's assumed from its room type.

4. All guests require a credit card to make a reservation and pay for their expenses (the company does not deal with cash transactions). A guest can keep many credit cards on file. A master reservation is associated with the credit card, not necessarily the guest, as a guest can use a company credit card to book the reservation.

5. A master reservation (beginning with 5000) may be made for one or more than one room (but only within the same hotel). However, rooms cannot be assigned to more than one guest. In other words (see sample data below), a guest can make a single master reservation today that contains one or more reservation details (booking subsequent stays at the same hotel for many later dates, under one single master reservation).

6. When a reservation is made, a room number must be assigned (so it's not reserved by another). In the reservation detail, the quoted room rate assigned will default* to the Room Type's Rack Rate for that particular hotel on the particular check-in date (regardless if the rate changes over the stay). This quoted rate may be overridden by the person taking the reservation, and will be noted in the reservation comments. *Handled programmatically not via direct relationship or table constraint.

7. Each reservation detail will have its own status (R, A, C or X)."R", the default, will mean reserved only, "A" for active - (the reservation and room is active and the guest is currently checked-in), "C" for when the guest checks out OR when a cancellation penalty needs to be applied (#11), and "X" for cancelled without penalty when the cancellation occurs more than 48 hours prior to check-in (#11).

8. The master reservation (which, remember can have multiple details - see #6) will also have its own status (R, A, C or X) and comments. If any of its reservation details are still reserved (not yet active or compete) then the master reservation status will remain R. When there is at least one Active reservation detail, and all the other details are complete or cancelled (C or "X") then the Master Reservation status will change to A for Active as well. If a master reservation is X cancelled, then all its R reservations details are also automatically X cancelled or C Checked out with a penalty applied. If all details are (X or C), then the master reservation status changes to C.

9. An additional discount may be applied to the quoted rate used in the reservation details. There are many discounts to choose from. Some discounts have a percentage (like military 10%) and some have fixed rates (like coupons for $10 off). All discounts have an expiration date and comments (restrictions). The Default Discount - "No Discount Applied", has a rate of 0% and an amount of $0. Only one discount is allowed per reservation detail and must be claimed at the time of the reservation.

10. Check in time is 3PM and Check out is Noon the next day, so a 2 night stay, beginning June 1, would mean a check out on June 3 by noon. There is a one hour check out grace period (1PM). After that, a late checkout fee of 50% of the quoted room rate will apply to checkouts between 1PM and 4PM. After 4PM, another full night's room and tax will be charged.

11. Cancellations must be made 48 hours (from 6PM) in advance of the check-in date, or one night's lodging (plus tax) will be charged and collected. Example: If the reservation is for 16 June 2016, then the reservation must be cancelled by 14 June, 2016, at 6PM.

12. Each reservation detail, can have multiple billing details. This is essentially what is called a Guest Folio. The folio contains the guestid (but do not use a FK constraint to connect tables). At a minimum, each night at midnight and upon check-out, two itemized entries are applied to the guest folio 1) a charge for the previous nights room, and 2) another entry for the County Tax, which is required to be maintained as a separate entry from room revenue. Tax rates vary, depending on the county the hotel is located in. There can be many hotels in a single county. Tax rates include a sales tax (for general purchases / services in the hotel) and/or a lodging tax rate for the actual accommodations. Other billable types (subject to sales tax) to the guest folio may include "Room Service", "In Room Entertainment", "Incidentals", etc.

13. When a reservation detail is Complete (checkout), payment is collected on the credit card used to make the reservation. These payments are also assigned a category (GuestPayments, ExternalVendors, and Services). All payment ledger entries start with 8000 and increment by 1.

Sample Data (Currently held in an Excel Spreadsheet)

Hotel #2100 - Sunridge Bed and Breakfast, Park City

Room 201 - Single - $175 (1/1 4/30 and 11/15-12/31) or $125 (5/1-11/14)

Room 202 - Single - $175 (1/1 4/30 and 11/15-12/31) or $125 (5/1-11/14)

Room 301 - Double - $280 (1/1 4/30 and 11/15-12/31) or $230 (5/1-11/14)

Room 302 - Double Deluxe - $285 (1/1 4/30 and 11/15-12/31) or $235 (5/1-11/14)

Room 303 - Double Deluxe - $285 (1/1 4/30 and 11/15-12/31) or $235 (5/1-11/14)

Room 304 - Family - $390 (1/1 4/30 and 11/15-12/31) or $340 (5/1-11/14)

Room 305 - Family - $390 (1/1 4/30 and 11/15-12/31) or $340 (5/1-11/14)

Summit County Tax Rates: Sales 7.5%, Lodging 12.75%

Hotel #2200 - Pioneer Guesthouse, Salt Lake City, UT

Room 101 - Single - Smoking - $138 year round

Room 102 - Double - Smoking - $142 year round

Room 204 - Double - $139 year round

Room 241 - Double - $139 year round

Room 242 - Double Smoking - $142 year round

Room 351 - Family - $150 year round

Room 352 - Family - $150 year round

SL County Tax Rates: Sales 6.85%, Lodging 12.10%

Hotel #2300 - Weber Suites, Ogden

Room 221 - Single - $124 year round except (7/1-7/31) - $154

Room 222 - Double - $146 year round except (7/1-7/31) - $176

Room 223 - Double - Smoking - $156 year round except (7/1-7/31) - $186

Room 322 - Suite - $250 year round except (7/1-7/31) - $280

Room 321 - Suite Smoking - $260 year round except (7/1-7/31) - $290

Davis/Weber County Tax Rates: Sales 7.15%, Lodging 11.25%

Hotel #2400 - Community Cottages, Salt Lake City (Under Construction)

No Rooms

SL County Tax Rates: Sales 6.85%, Lodging 12.10%

By examining the existing spreadsheets, we can determine the following guests have made reservations for the upcoming weeks. The owners would like this information imported into the new FARM system.

Booked Guests (and History):

Anita Proul, Provo, Utah USA (Note: First two entries for the Pioneer Guesthouse is a SINGLE reservation) Weber Suites

May 2

Room 221

2 Nights

$125

$26

Coupon Applied: $99 Summer Special Revenue: $198 Tax: $22.28 Checked Out

Billed

Pioneer Guesthouse

Oct 15

Room 204

2 Nights

$139

0%

Reserved

Pioneer Guesthouse

Oct 22

Room 241

2 Nights

$139

0%

Reserved

Sunridge B&B

May 25

Room 202

3 Nights

$125

10%

Discount: Internet User's Discount Revenue: $337.50 Tax: $43.03 Checked Out

Billed

Sunridge B&B

Oct 28

Room 305

1 Night

$340

10%

Discount: Internet User's Discount

Reserved

Pioneer Guesthouse

Nov 7

Room 204

2 Nights

$139

0%

Reserved

Sunridge B&B

Nov 15

Room 202

3 Nights

$175

0%

Reserved

Sean O'Brien, Dublin Ireland (First two are a SINGLE reservation)

Pioneer Guesthouse	May 1	Room 241	1 Night	$139	15%	Military Discount. Cancelled within Cancellation Policy	Cancelled 24 April
Pioneer Guesthouse	May16	Room 241	2 Nights	$139	15%	Military Discount Cancelled less than 24 hours Revenue: $118.15 Tax: $14.30	Billed
Pioneer Guesthouse	Nov 1	Room 241	1 Night	$139	15%	Military Discount.	Reserved