Guest House/zh

From SQLZOO
Revision as of 06:13, 4 May 2017 by Kenlyl (talk | contribs)
Jump to navigation Jump to search

Hotel.png

Table booking

The table booking contains an entry for every booking made at the hotel. A booking is made by one guest - even though more than one person may be staying we do not record the details of other guests in the same room. In normal operation the table includes both past and future bookings.

+------------+--------------+---------+----------+-----------+---------------------+--------+--------------+
| booking_id | booking_date | room_no | guest_id | occupants | room_type_requested | nights | arrival_time |
+------------+--------------+---------+----------+-----------+---------------------+--------+--------------+
|       5001 | 2016-11-03   |     101 |     1027 |         1 | single              |      7 | 13:00        |
|       5002 | 2016-11-03   |     102 |     1179 |         1 | double              |      2 | 18:00        |
|       5003 | 2016-11-03   |     103 |     1106 |         2 | double              |      2 | 21:00        |
|       5004 | 2016-11-03   |     104 |     1238 |         1 | double              |      3 | 22:00        |
+------------+--------------+---------+----------+-----------+---------------------+--------+--------------+

Table room

Rooms are either single, double, twin or family.

+-----+-----------+---------------+
| id  | room_type | max_occupancy |
+-----+-----------+---------------+
| 101 | single    |             1 |
| 102 | double    |             2 |
| 103 | double    |             2 |
| 104 | double    |             2 |
| 105 | family    |             3 |
+-----+-----------+---------------+

Table rate

Rooms are charged per night, the amount charged depends on the "room type requested" value of the booking and the number of people staying:

+-----------+-----------+--------+
| room_type | occupancy | amount |
+-----------+-----------+--------+
| double    |         1 |  56.00 |
| double    |         2 |  72.00 |
| family    |         1 |  56.00 |
| family    |         2 |  72.00 |
| family    |         3 |  84.00 |
| single    |         1 |  48.00 |
| twin      |         1 |  50.00 |
| twin      |         2 |  72.00 |
+-----------+-----------+--------+

You can see that a double room with one person staying costs £56 while a double room with 2 people staying costs £72 per night

Note that the actual room assigned to the booking might not match the room required (a customer may ask for a single room but we actually assign her a double). In this case we charge at the "requirement rate".