防止两个保留之间发生冲突的代码逻辑 [英] Code Logic to prevent clash between two reservations

问题描述

我正在使用PHP 5.1开发会议室预定系统，在这里我需要执行一种机制，即没有两个用户可以在同一天同时预定房间。系统将time_in，time_out，from_date，to_date存储在数据库中。我应该使用哪种算法来防止冲突？

I am working on a meeting room reservation system in PHP 5.1 where I need to enforce a mechanism that no two users can reserve a room at the same time on the same day. The system stores time_in, time_out, from_date, to_date in a database. What algorithm should I use to prevent clash?

我最初想在数据库中保留time_in和time_out字段 UNIQUE ，但是在用户A为 2014年5月5日的9至5 即

I initially thought to keep time_in and time_out fields UNIQUE in the database but in a scenario where user A books for 9 to 5 on 12-05-2014 i.e


**用户A **
time_in => 09:00
time_out => 05:00
Reservation_date => 2014年12月5日

，用户B当天预订了12到3本书，即

and user B comes and books for 12 to 3 the same day, i.e


**用户B **
time_in => 12:00
time_out => 03:00
Reservation_date => 2014年12月5日

由于time_in time_out不同，系统将接受预订。因此，该算法将失败。

The system will accept the reservation since time_in time_out are different. So this algorithm will fail.

此外，人们还可以选择保留几天，例如我想将1号房间保留10天，每天从9到5。有人可以帮我解决这里的算法吗？

Also, people also have an option to reserve for days like I want to reserve room 1 for 10 days, each day from 9 to 5. So can anyone help me with the algorithm here?

推荐答案

根据您的描述，我认为保留如

Based on your description, I assume that a reservation such as

房间：1
time_in：9:00 ，超时：13:00 ，
发件日期：2014年11月1日，至今：2014年11月3日

表示在11月1日，2日和3日的三天，该房间保留了上午9点在每一天的下午1点之前。也就是说，这并不意味着预订从11月1日上午9点开始，一直持续到11月3日下午1点。

means that on the three days Nov 1, 2, and 3, the room is reserved from 9am to 1pm on each of those days. That is, it does not mean the reservation starts at 9am on Nov 1 and runs continuously until 1pm on Nov 3.

基于这些假设，需要解决的基本问题是解决的方法是确定两个保留是否重叠。为了简洁起见，我将在这里以元组形式表示保留：

With these assumptions, the fundamental problem that needs to be solved is to determine if two reservations would overlap or not. For compactness, I will represent a reservation here as a tuple:

[房间，time_in，time_out，from_date，to_date]

如果房间相同，并且两个时间范围和日期范围都重叠，则两个预订重叠。也就是说，给定保留 [r，a，b，c，d] 和 [R，A，B，C，D] ，如果 r = R ， a..b 与 A重叠，则它们重叠..B 和 c..d 重叠 C..D 。当且仅当

Two reservations overlap if the rooms are the same and both the time ranges and the date ranges overlap. That is, given reservations [r,a,b,c,d] and [R,A,B,C,D], they overlap if r = R, a..b overlaps A..B, and c..d overlaps C..D. Date and time overlap occurs if and only if

r = R   and   b >= A and a <= B    and   c >= D and d <= C

使用上面的表达式，您可以编写一个查询数据库中与提议的新保留冲突的所有现有元组，如果计数为零，则插入提议的保留。 （小写字母将是查询中代表建议保留的参数，大写字母将是数据库中的列名称。我将留给您了解SQL进行计数和条件插入。）

Using the expression above you can write a query that counts all existing tuples in the database that would conflict with a proposed new reservation, and insert the proposed reservation if the count is zero. (Lower case letters would be parameters to your query representing the proposed reservation, the capital letters would be the columns names in the database. I'll leave it to you to figure out the SQL to do the count and conditional insert.)

请注意，重叠比较是正确的，可能并不立即显而易见。通过查看两种时间（或日期）范围可能发生的所有方式，可以使自己确信它们是存在的。下面显示的是时间范围之间的可能关系。括号和尖括号旨在帮助可视化每个范围的两个端点。

Note that it may not be immediately obvious that the overlap comparisons are correct. You can convince yourself that they are by looking at all the ways two time (or date) ranges could occur. Shown below are the possible relationships for the time ranges. The parens and angle brackets are intended to help visualize the two endpoints of each range.

(a   b)   <A   B>   condition b >= A fails, no overlap
(a   <A   b)   B>   both conditions met, overlap
(a   <A   B>   b)   both conditions met, overlap
<A   (a   b)   B>   both conditions met, overlap
<A   (a   D>   b)   both conditions met, overlap
<A   B>   (a   b)   condition a <= B fails, no overlap

这篇关于防止两个保留之间发生冲突的代码逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！