1 引言

完整代码https://download.csdn.net/download/weixin_55771290/87398756

在本文中，对酒店客房预订管理系统进行了系统调查，分析与设计，进行了详尽的需求分析，并基于用户需求，设计了一个高效且规范的数据库模式。在此基础上，我们创建了 Mysql 数据库，并使用 Html 和 Javascript 编写了在线酒店客房预定管理系统。本系统采用前后端分离的方式进行开发，前端使用了基于 nodejs 及多种组件开发而成，并通过 Ajax 与后端相连接，两者通过一套详细、准确且完备的 API 作解耦。后端则完成两部分的功能，一部分是处理复杂的业务逻辑，对各种诸如注册，增添新用户，新房间，或增加新订单等接口进行处理，保证对数据库操作的一致性，另一部分是对传入 API 的数据进行格式检查，防止出现 SQL 注入或者恶意攻击的情况。

在后端，除了处理复杂业务逻辑，还负责与数据库进行交互，我们编写了很多 SQL 查询与更新语句，在后端中根据接口传入的数据生成 SQL 语句，并交由数据库查询与更新。

在数据库层面，我们发现一些复杂的业务逻辑在后端编写时一定要确保一致性，否则会产生严重的逻辑错误。因此我们在数据库中编写了一些触发器与存储过程，以保证修改数据库信息时信息的一致性，对于在处理数据库数据时产生的错误，也在后端进行了适当的处理并反馈到前端中以让用户知晓。

总之，我们遵循数据库设计与网站设计与编写的方法，实现了酒店预定系统，该系统包括客房预订、会员注册、用户管理、客房管理、客户与客房的信息增删改、以及订单管理等功能，已经能够满足大多数酒店的需求。

2 系统分析与设计

随着信息化时代的到来，使用数据库来对酒店客房预订信息进行管理能够大 大提高信息管理的效率。基于网络对酒店管理系统的了解，我们确定了该系统所 需要满足的需求，以及我们系统应具有的功能模块。

2.1 系统需求分析

我们的系统可供酒店经理，酒店前台人员以及顾客使用。其中，不同的用户对该系统有着不同的需求，以及相应的操作权限。

对于顾客而言，他们需要能够在系统中：

• 查询可用的房间
• 预定指定的房间，提交订单
• 取消自己已经订好的订单
• 查看自己存在酒店中的个人信息，并且修改某些易变的项（如手机号码等）

而对于酒店前台人员而言，他们不仅需要顾客本身可实现的需求，而且还应有更多信息的查询权限，更全面地说，应该如下所示：

• 查询酒店所有房间的信息
• 查询酒店所有订单的信息
• 可能需要根据需要，取消其他用户的订单
• 增加新用户

酒店经理，则作为整个酒店的管理者，应当具有更多信息的修改权限，更具体的说，应该如下所示：

• 增加酒店的房间类型
• 酒店若新装修了房间，应当能够增加新的房间，或者修改已有的房间类型
• 还需要能够对所有订单的情况做一个统计，计算酒店的营业额等信息

2.2 系统结构设计

本系统可以分为这 5 个子系统，可见图 2.1，每一个子功能都会在后端编写一个或多个对应的 API 供前端调用。

3 数据库设计

在本节中，我们会按照概念设计、逻辑设计到物理设计的顺序讲解本项目的具体设计方式。

3.1 概念设计

根据需求分析的结果，我们进行了数据库的概念设计，分析了数据库中应该存在的实体集、联系集，并绘制了 ER 图。

3.1.1 实体集

描述酒店的房间需要房间实体集 (表 3.1) 和房型实体集 (表 3.2)。描述酒店的用户并记录用户个人和登录信息需要用户实体集(表3.3)、账户实体集(表3.4)、区域实体集(表3.5)。最后，酒店还需要记录所有订单(表3.6)，以及订单的操作明细 (表 3.7)。

下面对这些实体集作简要的描述和区分。

3.1.1.1 房间和房型实体集

房型实体代表房内的基础设施状况，例如房内是否有wifi、是否包含早餐、容纳的人数等，可见表 3.2。

而房间实体则对应着真实的每个房间。其具有所在层数、房号、价格等属性，可见表 3.1。

3.1.1.2 用户，地区和账户实体集

用户实体代表一个真实的个人。它有自己的身份信息，例如证件号、姓名、性别等。其中用户实体集中的 name 属性代表这个人的真实姓名，可见表 3.3。

账户实体集代表本应用上的一个账号。其中的账户名 (username) 是登陆名，password 用于登陆。role 为该账户在应用里的角色，它决定了该账户拥有的权限，可见表 3.4。

同时，为了保证每一个用户所在的地区有意义，我另外使用了一个地区实体用于确定用户所在的地区，以防止用户输入无效的地区，该实体可见表 3.5。

3.1.1.3 订单和操作实体集

订单实体的信息包含了哪个用户、在何时、花费多少钱、在哪个时间段订购了哪个房间，可见表 3.6。

操作实体集包含了用户对订单的两种操作，分别是发起订单和取消订单，可见表 3.7。

3.1.2 联系集

房型与房间实体、房间和订单实体、用户与订单实体、区域与用户实体之间有一对多的联系。对于一个订单只有完成下单和完成下单后又取消订单两种情况，因此订单实体与订单操作实体是一对一或一对二的联系。用户与账户之间是一对一联系。

3.1.3 E-R图

3.2 逻辑设计

在完成数据库的概念设计后，接下来要做的是将所有实体集合联系集分别转换为关系模式 R。

有关实体集的转换规则如下：

• 具有 n 个简单描述性属性的强实体集转换为具有 n 个属性的关系模式
• 为复合属性的每个子属性在 R 中创建一个单独的属性
• 为多值属性 M 创建一个单独的关系模式，该模式中包含了 M 所在的实体集或联系集的主码
• 将派生属性实现为方法
• 将弱实体集表示为包含所有弱实体集属性以及所依赖的强实体集的主码的关系模式。R 的主码包括弱实体集的部分码和所以来的强实体集的主码

有关联系集的转换规则如下：

• 联系集转换为由联系集的描述性属性以及相关实体集的主码组成的关系模式
  
• 对于多对多的联系集，R 的主码包含所有相关实体集的主码
  
• 对于一对一的联系集，任意一个相关实体集的主码都可以作为 R 的主码
  
• 对于一对多的联系集，用关联的映射基数为”多”的那个实体集作为 R 的主码
  

转换完成后，还要消除关系模式的冗余，并合并部分关系模式。具体而言包括以下两种情况：

• 连接弱实体集与其依赖的强实体集的联系集的模式是多对一且没有描述性属性的，另外弱实体集的主码包含强实体集的主码，因此这样的联系集对应的关系模式是冗余的
  

• 考虑多对一的联系集 AB 和相关的实体集 A, B, ，若 A 在联系中的参与是全部的，那么可以将 A 与 AB 模式属性合并得到 A∗，主码是 A 的主码，外码加入 A∗ 中
  

依照上述规则，由于Type、Room、Operation、Order、User、Region、Account均为简单强实体集于是可以首先生成包含对应属性的关系模式。再考虑联系集，由于 OrderUser、RoomType、OrderRoom、OrderOp、UserRegion 均为一对多的联系 集，因此均可以按照上述规则消除。

最后得到以下关系模式：

• RoomType(id,name,capacity,breakfast,wifi)
• Room(id,floor,room_num,price,type_id) Operation(id,time,detail. order_id)
• Order(id,status,check_in,check_out,room_id,user_id) Region(id,name)
• User(id,credential,name,gender,birthdate,phone,bonus,balance,region_id)
• Account(id,username,password,role)

3.3 物理设计

在这一部分中，我们主要对数据库中索引的设计做了一个比较详细的解释，说明了我们对数据库性能问题的考虑与我们的解决方案。

3.3.1 索引简介

在 MariaDB 中，有 4 种类型的索引（Mariadb 不严格地区分 key 和 index 的概念，并时常根据语境对他们混用）。

表 3.8列出了这 4 种索引。其中 unique 表示索引建立的数据项是否必须唯一，notnull 表示数据项是否必须不为 null。

mariaDB会自动为primarykey和foreignkey建立索引（表第一行）。其中如果对unique的数据项建立索引，可以建立一个unique索引（表第二行）。如果该数据项不是唯一的，则需要建立 plainindexes 或 full-textindexes。后两者最大的不同在于，如果使用 like‘%word%’ 这种形式的查询，必须建立 full-text 索引，否则无效。

3.3.1.1 索引建立

搜索订单几乎是最常见的一种操作。从用户的行为来判断，大部分对订单的搜索都是基于订单日期的。故在 Order 表上对 check_in 和 check_out 添加索引十分有必要。

CREATE INDEX Order__index_check_in ON `Order` (check_in DESC);

在上面的索引建立中，显式地指明了索引使用降序排列。这是因为根据时间局部性，越 “晚 (刚刚发生）” 的记录越可能被搜索，越 “早（年代久远)” 的记录越不可能被搜索。降序排列索引可以尽可能早地让索引被搜索到。

3.3.2 效果评价

1. explain 
2. select * from `Order` 
3. where check_in >= '2019-01-22';

比较有索引和无索引时的相关输出。没有建立索引时，仅能根据主键索引进行查询。查询了 300 多行。但建立了对 check_in 的索引后，以上的语句仅需要搜索 75 行，大大加快了搜索时间。

进一步测试以下的语句：

explain select * from `Order`
where check_in >= '2019-01-22' AND check_out <= '2020-01-01';

发现mariaDB的做法是，先根据索引check_in作rangescan，估计会检索到 75 个左右的行。然后对这些行作 where 筛选，得到最终的结果。

在上面的语句执行中，没有用到 check_out 索引，但这个索引依旧有它的必要指之处。mariaDB 会选择 check_in 或 check_out 中的一个索引作 range scan，很可能在下一次更合适的情况下会使用 check_out。维护 check_in 和 check_out 两个索引，可以让 mariaDB 有更多提升性能的可能性。

更进一步的分析，如果用户想要筛选订单，必行会出现user_id=的情况。此时user_id一定是更适合的索引。但不能忽略的是，酒店的前台也需要筛选订单。后者往往是根据日期作筛选的，而且这个操作的发生极其频繁。故这个索引十分有必要。

3.3.3 其他索引的需求分析

大部分对User表的查询都是使用id进行查询的。username是用户的真名，对用户真名的查询操作其实很少。故现有的主键索引已经足够。

对 Account 的查询中，由于 username 被标记为 unique（IDE 自动为我们建立了索引），故对 Account 的大部分查询效率都很高。

一个比较微妙的操作是查询空闲房间。因为查询空闲房间涉及到查询房间 (Room)、查询房间类型 (RoomType) 和查询订单 (Order) 三个表的数据。实际上这个操作的效率也是很高的。对此详细的介绍见后文。

我们的应用经常需要返回 “空闲的房间数”。可是怎么定义空闲呢？如果对于一段查询时间 [check_in,check_out], 不存在与之重叠的订单（order），那么我们就说这个房间是空闲的。但实际的查询并不是通过使用check_in和check_out的索引进行的。

explain
select R.id, R.floor, R.room_num, R.price, T.breakfast, T.wifi ,T.name, T.capacity
from Room as R, RoomType as T
where R.type_id = T.id
  and T.capacity >= 2
  and T.wifi like 1
  and T.breakfast like 1
  and not exists
    ( select room_id, O.id
    from `Order` as O
    where status = 1
      and ((O.check_in <= '2018-11-20' and O.check_out >= '2018-12-30')
      or (O.check_in <= '2018-10-30' and O.check_out >= '2018-11-10'))
      and O.room_id = R.id );

使用 explain 查看查询计划后，我们发现，mariaDB

• 首先使用 where 筛选掉不符合要求的 RoomType。由于 RoomType 是很小的一个表，这一步的效率非常高
• 利用 Room 对 RoomType 的外键索引筛选合适的 Room。由于利用了索引，这一步速度也非常高
• 利用 Order 对 Room 的外键索引筛选合适的 Order。由于利用了索引，这一步的速度也非常高

这说明我们没有额外建立索引的必要了，现有的主键和外键索引已经能让数据库的执行速度足够高了。

只有订单的check_in和check_out需要额外的索引。其他操作都可以通过主键外键索引、unique 索引得到加速。