在预订系统中查找空闲插槽 [英] Finding free slots in a booking system

问题描述

在我的问题关于搜索日期范围我尝试简化问题，无意中提出了一个不同的和更简单的问题。

而不是使编辑复杂化这个问题，我将要问我实际想要的问题。

我有两张表格Property and Booking。预订对物业有一个外键，也是开始和结束日期。

用户正在搜索空闲时间段，并在几天内提供所需的持续时间。他们还提供一系列的开始日期，他们感兴趣，所以搜索将是这样的：
找到我所有的物业，我想要一个3天的槽，从5月开始任何时间。

现在我可以这样做：
1.为每个潜在的开始日运行31个查询
2.在5月份查找所有预订，将它们集中到一个31个布尔代表日子，并循环寻找插槽。

我假设（2）在大多数情况下效率更高。有更好的算法吗？有没有一个纯SQL解决方案。

我将使用Django，我的数据集很小，所以我可能会做一个笨蛋的准备，但我很好奇知道什么最好的算法看起来像。

解决方案

可能对您的申请过度杀人 - 但是：

改进您的搜索的方式是以写入过程更为复杂的简单方法，就是更改预订表，使其成为可用性表。

添加一个布尔列，以指示插槽是免费的还是预订的（或者更好地放在已经预订的客户的ID中，如果插槽是空闲的，则使用0）。

从2009年1月1日开始 - 1月20日 - 31日，您可以从一个空闲时段开始。插入和一个更新），预订的插槽和两个可用的插槽。

继续这样做，随着时间框架更加分散，预订过程将包括以下：

• 将一个完整的可用插槽分配给某人（一个更新）


• 拆分'可用插槽'int o两个（一个更新和一个插入）


• 如果有人在可用插槽中书写中间部分，则将插槽分成3（如上所述）。

这不是令人难以置信的复杂的管理，搜索过程成为一个简单的查询：找到所需时间范围内可用的任何插槽（booked = false或customerid = 0，无论哪个方式你一起去）其中enddate - startdate> =你想要的天数。

它使预订/可用性表的大小翻倍，使预订不那么简单，但是权衡之处在于搜索过程与所得到的一样简单。

In my question about searching for date ranges I tried simplifying the problem and inadvertently posed a different and simpler problem.

Rather than complicate that question with an edit I am going to ask the problem I actually intended.

I have two tables Property and Booking. Bookings have a foreign key to Properties and also start and end date.

The user is searching for free slots and supplies a desired duration in days. They also supply a range of start dates that they are interested in. Therefore a search will be along the lines of: "Find me all properties I want a 3 day slot which starts anytime in May."

Now I can do this by: 1. Running 31 queries for each potential start day 2. Finding all bookings in May, condense them into one array of 31 booleans representing days and loop through looking for slots.

I assume (2) is more efficient in most cases. Is there any better algorithms? Is there a pure SQL solution.

I will be using Django and my dataset is small so I will probably be OK with a 'dumb' approuch but I am curious to know what the best algorithm looks like.

解决方案

Probably overkill for your application - but:

A relatively simple way of improving your searches at the expense of making the 'write' process more complicated, would be to change the Booking table to make it an 'Availability' table.

Add in a boolean column to indicate if the slot is free or booked (or better still put in the id of the customer who's booked it, and use 0 if the slot is free).

Start off with a single free slot, 1st Jan 2009 -> 31st Dec 20??

When you get a booking split the free slot into 3 (two inserts and one update), the booked slot and the two available slots.

Keep doing that and as the time frame gets more fragmented the booking process will consist of one of the following:

• Assigning an entire 'available slot' to someone (one update)
• Splitting an 'available slot' into two (one update and one insert)
• Splitting a slot into 3 (as above) if someone books the middle section out of an available slot.

That's not incredibly complicated to manage and the search process becomes a simple query: finding any slots in the required time frame that are available (booked=false or customerid=0, whichever way you go with it) where enddate - startdate >= the number of days you want.

It doubles the size of the booking/availability table, and makes bookings less simple, but the trade off is that the search process is about as easy as it gets.

这篇关于在预订系统中查找空闲插槽的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！