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

问题描述

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

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：

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如下：

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如下：

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如下：

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相应做如下修改：

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进行插入。