Difference between revisions of "Guest House/zh"

From SQLZOO
Jump to: navigation, search
(Table room)
(表格 room)
Line 23: Line 23:
  
 
===表格 room===
 
===表格 room===
Rooms 房間,類別有single單人床房,double雙人床房,twin雙床房,和 family家庭.
+
表格 rooms 房間,類別有single單人床房,double雙人床房,twin雙床房,和 family家庭.
 
  +-----+-----------+---------------+
 
  +-----+-----------+---------------+
 
  | id  | room_type | max_occupancy |
 
  | id  | room_type | max_occupancy |

Revision as of 06:31, 4 May 2017

Hotel.png

表格 booking

表格 booking 貯存了旅店的每一個預訂。 每個預訂只記錄一個客人的資料,就算有多於一個客人入住同一晚同一房間,我們都不會記錄其他住客的資料。表格會貯存過去和未來的預訂資料。

+------------+--------------+---------+----------+-----------+---------------------+--------+--------------+
| 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        |
+------------+--------------+---------+----------+-----------+---------------------+--------+--------------+

表格 room

表格 rooms 房間,類別有single單人床房,double雙人床房,twin雙床房,和 family家庭.

+-----+-----------+---------------+
| id  | room_type | max_occupancy |
+-----+-----------+---------------+
| id  | 房間類別   | 最多入住人數   |
+-----+-----------+---------------+
| 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".