SQL puzzles and answers读书笔记——酒店订房问题

问题描述

Hotel表记录了酒店订房的信息，创建Hotel表的脚本如下：

?

1 2 3 4 5 6 7 8 9

create table Hotel (   room_nbr int not null,   arrival_date date not null,   departure_date date not null,   guest_name char(30) not null,   primary key(room_nbr, arrival_date),   check(departure_date >= arrival_date) );

其中room_nbr为房间号，arrival_date为入住日期，departure_date为退房日期，guest_name为住客名。

对于这张表已经加了主键约束primary key(room_nbr, arrival_date)和check约束departure_date >= arrival_date

但这些还不够，我们的问题是：如何防止同一个房间预订的时间产生重叠。

解决方案

方案1

首先我们可以试图使用check约束来解决这个问题，尝试写如下script：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

create table Hotel (   room_nbr int not null,   arrival_date date not null,   departure_date date not null,   guest_name char(30) not null,   primary key(room_nbr, arrival_date),   check(departure_date >= arrival_date),   check(not exists           (select *           from Hotel H1 inner join Hotel H2           on H1.room_nbr = H2.room_nbr             and H1.arrival_date between H2.arrival_date and H2.departure_date           )        ) );

很可惜在Oracle 11g和SQL Server 2008中这种写法都无法通过语法，出错提示大致为：在check中不能写子查询。

同时这里check中的逻辑本身也存在问题，就算语法支持，也可能无法插入任何数据（方案3中会进一步涉及此问题）。

方案2

重新设计表结构，为每一个房间的每一天预订对应一条记录，script如下：

?

1 2 3 4 5 6 7

create table Hotel (   room_nbr int not null,   occupy_date date not null,   guest_name char(30) not null,   primary key (room_nbr, occupy_date) );

这样设计解决了重叠预订的问题，但会带来数据的冗余，而且插入预订记录也变得麻烦了。

方案3

创建带with check option的View，之后通过View来操作表，script如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

CREATE OR REPLACE VIEW HOTELSTAYS AS   select   H1.room_nbr,   H1.ARRIVAL_DATE,   H1.DEPARTURE_DATE,   H1.GUEST_NAME from   Hotel H1 where   not exists   (select     *   from     Hotel H2   where     H1.room_nbr = H2.room_nbr     and     H1.ARRIVAL_DATE between H2.arrival_date and H2.departure_date    ) with check option;

在创建此View之后，在Oracle 11g中向此View插入一条预订记录能成功，但再查看此View却无法查到刚插入的数据。如果在SQL Server 2008中，此View无法插入任何数据。

造成这种情况的原因是此View子查询中的H1.room_nbr = H2.room_nbr and H1.ARRIVAL_DATE between H2.arrival_date and H2.departure_date一定会连接到自身的记录。

我们可以在Hotel表中加入Id列，并给予unique约束，script如下：

?

1 2 3 4 5 6 7 8 9 10

create table Hotel (   reserve_id int not null unique,   room_nbr int not null,   arrival_date date not null,   departure_date date not null,   guest_name char(30) not null,   primary key(room_nbr, arrival_date),   check(departure_date >= arrival_date) );

创建View的script相应做如下修改：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

CREATE OR REPLACE VIEW HOTELSTAYS AS   select   H1.reserve_id,   H1.room_nbr,   H1.ARRIVAL_DATE,   H1.DEPARTURE_DATE,   H1.GUEST_NAME from   Hotel H1 where   not exists   (select     *   from     Hotel H2   where     H1.room_nbr = H2.room_nbr     and     H1.ARRIVAL_DATE between H2.arrival_date and H2.departure_date     and     H1.reserve_id <> H2.reserve_id   ) with check option;

这样问题就解决了。

对于reserve_id在SQL Server中我们可以定义identity列，在Oracle中可以使用sequence进行插入。