酒店预订系统的设计 - 保留它采用多个房间 [英] Hotel booking system design - Reservation which uses multiple rooms

问题描述

我在找一个酒店房间一样的预订系统一些设计建议。 我读了很多东西，在这个至今，我已经得到了它返回客房，可在一定日期范围内的清单查询。

I'm looking for some design advice on a hotel room like booking system. I've read a lot of stuff on this so far and I've got a query which returns a list of rooms which are available for a certain date range.

我的搜索目前只能返回客房，可用于整个预订期的用户搜索。我想扩大这个，所以我可以履行多个房间的要求。即用户搜索日期范围和房间1是可用的第2天，然后，他们将需要被转移到室2的最后2天

My search currently only returns rooms which are available for the entire booking period the user is searching for. I'd like to expand this so I can fulfill the request with multiple rooms. I.e. User searches for a date range and Room 1 is available for the first 2 days, then they would need to be transferred to Room 2 for the last 2 days.

我目前做使用下面的查询我的可用性查找;

I currently do my availability lookup using the following query;

Dim available = From k In rooms _
            Group Join r In db.Reservations.Where(Function(fx) (fx.DateIn >= Date_From And fx.DateIn < Date_To) Or (fx.DateOut > Date_From And fx.DateOut <= Date_To)) On k Equals r.Room Into Group _
            From r In Group.DefaultIfEmpty
            Where r Is Nothing
            Select k

我怎样才能改变我的设计或扩大目前的设计，让每预订多间客房？我有点难倒！

How can I alter my design or expand on the current design to allow multiple rooms per booking? I'm a bit stumped!

在此先感谢！

推荐答案

所以，我的第二个解决方案，它相当复杂，但我认为（在评论第一个说的）的非常有效的为好。

So my second solution, its (as said in the comments to the first one) quite complicated but I think its quite efficient as well.

我不会发表code，而是整体的概念，如果你有兴趣，具体细节我会看看我能做些什么。

I will not post code but rather the overall concept, if you are interested in specific details I'll see what I can do.

问题：我们创建了一个抽象，其中每一个房间的每一天都是可用或阻塞。我们感兴趣的是在来宾想要访问的时候可用客房。 我们要选择的房间需要客人的最低搬迁相结合。

Problem: We create a abstraction where every room on every day is either available or blocked. We are interested in the available rooms in the time the guest wants to visit. We want to select the combination of rooms that need the least relocations of the guest.

摘要：我们创建房间/天的组合，这里每天将重新由两个（该DAYVALUE）的功率psented $ P $的二维矩阵。一个房间多数民众赞成提供有相应的DAYVALUE。一个房间多数民众赞成封锁作为SERO的值。因此，如果我们总结的所有天为一个房间，我们可以定什么样的组合房间满足全职工作。

Abstract: We create a two dimensional matrix of room/day combinations where every day is represented by a power of two (the DAYVALUE). A room thats available has the corresponding DAYVALUE. A room thats blocked as a value of SERO. So if we sum all DAYS for a ROOM we can determinate what COMBINATION of ROOMS fulfills the full time.

更具体：当我们得到的所有值的总和一个房间，我们可以使用二进制或功能来决定是否房间给定的组合通过的或运算结果与值的总和比较fullfills完整的住宿所有的日子。

More specific: after we get the SUM of all VALUES for a ROOM we can use the binary OR function to decide if a given combination of rooms fullfills the complete stay by comparing the result of the OR operation with the SUM of values for all DAYS.

请注意，该DAYVALUE没有存储在dayTable。据计算为每个请求（2 ^（朝九特派-DAY））

Note that the DAYVALUE is not stored in the dayTable. It is calculated for every request (2^(STARTDAY-DAY))

混凝土：

1），你需要创建一个包含所有日期的表。它有一列包含日期（没有时间，没有时区只有一天）。我会推荐它包含从现在开始每一天，直到一百年。 （所以你必须来填充它只有一次）。 （注：这解决了，你不能在一个SQL查询选择每天一次的问题）

1) You need to create a table that contains all DAYS. It has exactly ONE column that contains a date (no time, no timezone only the day). I would recommend it to contain ALL days from now until in a hundred years. (so you will have to populate it only once). (Note: This solves the problem that you cannot select every day once in a SQL Query)

2）您创建产生输出这样一个观点：

2) You create a VIEW that produces an output like this:

DAY      ; DAYVALUE ; ROOM1 ; ROOM2; ROOM3 ; ROOM4 ; ... ; ROOMn
20120313 ;        1 ;     0 ;     0;     1 ;     1 ; ... ;
20120314 ;        2 ;     2 ;     0;     2 ;     2 ; ... ;
20120315 ;        4 ;     4 ;     0;     0 ;     4 ; ... ;
20120316 ;        8 ;     8 ;     0;     0 ;     0 ; ... ;
20120317 ;       16 ;    16 ;    16;    16 ;     0 ; ... ;

3）产生这样的总和。

3) Produce a SUM of this.

DAYVALUE ; ROOM1 ; ROOM2; ROOM3 ; ROOM4 ; ... ; ROOMn
      31 ;    30 ;    16;    19 ;     7 ; ... ;

4）看是否室值的任何单一所有的时间相匹配的DAYVALUE的总和（因此是可用的）

4) see if any single of the room values matches the sum of the DAYVALUE (and therefore is available all the time)

5）如果4个未成功创建每两个结果的OR值，并把它比作DAYVALUE

5) if 4 did not succeed create an OR value of every two of the results and compare it to the SUM of the DAYVALUE

ROOM1 OR ROOM2 = 30 <> 31
ROOM1 OR ROOM3 = 31 == 31. This is a valid match of rooms.

6）如果没有两个房间匹配的，你必须继续这个3房等。

6) If none matched for two rooms you have to proceed this for 3 ROOMS and so on.

使用此过程总是产生正确的一组室，可在参观的时候，如果你建立你的数据库以正确的方式（或对房间足够低量）查询应完成一个可接受的时间。

Using this procedure always produces the correct set of rooms that are available in the time of the visit and if you construct your database in the correct way (or have sufficiently low amount on rooms) the query should complete in a acceptable time.

玩得开心（我有！）

这篇关于酒店预订系统的设计 - 保留它采用多个房间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！