如何根据天数或月数来管理房间的可用性 [英] How to manage rooms availability based on days or months occupation
问题描述
我正在开发一个Rails 3应用程序（以MySQL作为RDMS）来管理某些房间的可用性/占用率（这是一个类似酒店的应用程序）
就我而言，问题是我必须跟上在系统中有2个不同的要求，因为一个房间可以租用：
I'm developing a Rails 3 application (with MySQL as RDMS) to manage availability/occupancy for some Rooms (it's an hotel-like app) In my case, the problem is I've to keep up in the system 2 different requirements because one room can be rented for:
- 有些月份（例如6个月）
- 某些天/周/周末
- Some months (for example 6 monts)
- Some days/weeks/weekend
显然应用必须在给定的日期显示每个房间的是否可用以及是否正在出租。另一方面，一个用户可以在一个房间中搜索给定的日期范围。
日期范围可以是某个固定日期或几个月范围（例如从9月到3月），我知道这与数据库的设计无关，但我要记住。
Clearly the app has to show for each room in a given date, if it is available or not, and which user is renting; and on the other side, one user can search one room for a given date range. The date range can be some fixed date or some months range (from September to March for instance) and I know this is something not related with the DataBase deisgn but I've to keep in mind.
为了使该系统高效运行，我想通过参考Room和一个365个元素的数组（表示1年）来创建一个YEAR合成表，其中每个元素可以是0或1，当0表示可用，而1表示出租。
To get this system working in a efficient way, I thought to create a table YEAR composite by a reference to the Room and an array (representing 1 year) of 365 elements when each element could be 0 or 1, when 0 means available and 1 means rented.
采用此解决方案时，我将不得不面对一些问题：
Adopting this solution I'll have to face with some problems:
- 当用户在几个月内搜索房间时，返回非常快速的结果。
- 管理跨年范围（例如（从2012年12月20日到2013年1月13日）
- Le年
最后，我想避免使用SQL过程或午夜过程（如果可能的话）
and the last thing, I'm tying to avoid SQL procedure or mid-night processes (if it's possible)
我确定有更好的方法来设计此问题在SQL中...
你们中的一些人可以帮助我吗？还是给我一些提示？
I'm sure there are better ways to design this problem in SQL... Could some of you help me? or giving me some hint?
推荐答案
保留房间预订列表，其中包含开始日期和结束日期。不要尝试为可用时间空位建模，也不要像预订电子表格那样考虑预订数据库。这只会导致您毫无意义的复杂性。日期范围很容易使用。
Keep a list of room reservations which have a starting date and ending date. Don't try to model availability slots or think of your reservation database like a spreadsheet. This will just lead you to pointless complexity. Date ranges are easy to work with.
最重要的要知道的是如何在查询中检测重叠的日期范围。这是测试房间是否已预订或是否空闲的基础。假设您有一个RESERVATION表，并且想要查找与给定日期范围重叠的预订：@FromDate和@ToDate。用于查找重叠预订的WHERE子句如下：
The most important thing to know is how to detect overlapping date ranges in your queries. This is the basis for testing whether a room is already reserved or whether it is free. Let's say you have a RESERVATION table and you want to find reservations that overlap a given date range: @FromDate and @ToDate. Your WHERE clause for finding overlapping reservations looks like this:
WHERE RESERVATION.start_date < @ToDate
AND RESERVATION.end_date > @FromDate
可用的房间不会有冲突（即WHERE NOT IN ...）和不可用的房间会有冲突。
Available rooms won't have conflicts (i.e. WHERE NOT IN...) and unavailable rooms will have a conflict.
这篇关于如何根据天数或月数来管理房间的可用性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
