如何创建显示一组公寓的可用性日历的数据库

Question

上下文：

最好的例子是AirBnB。假设我有5套公寓。每间公寓都有一个日历，表示其空房情况。当度假者前往我的城市并使用给定的开始日期和结束日期搜索公寓时，如果该时间段在我的任何公寓的日历上显示为可用，我希望这些公寓显示在度假者的搜索结果中。

一次一点：

显然上面有很多。这个问题的范围是我应该如何为包括其可用性的公寓列表设置数据库。在构建数据库之前，我花了一些时间在 Excel 中手动协调，只是为了在脑海中更清楚地了解一切应该是什么样子。在Excel中，我发现表格的列标题是：

• apartment_name
• owner_id
• apartment_description
• 日历

日历现在是我遇到的问题。从字面上看，在我的Excel中，这些列只是永恒的日期。每当度假者提交请求时，我都会发现每个日期单元格为空的所有公寓(例如，可用(。然后我把这些公寓寄给度假者。当他/她进行预订时，我会返回Excel并在所选特定公寓的每个日期单元格中标记为不可用。

我想得到更多的意见...这是我应该在PostGreSQL中想象我的数据库的正确方式吗？如果是这样...我可以进行如下所示的迁移吗？

class CreateApartments < ActiveRecord::Migration
  def change
    create_table :apartments do |t|
      t.string  :apt_name
      t.integer :apt_owner
      t.text    :apt_description
      Date.today..Date.new(2034, 12, 31)).each do |date|
          t.date :date
      end
      t.timestamps
    end
  end
end

Answer 1

您不应该存储空房情况，而是相反(公寓预订特定日期(。没有任何更深入的分析，我会做一些简单的事情，比如：

owner
  owner_id
  owner_name
apartment
  apartment_id
  apartment_name
  apartment_description
  owner_id
customer
  customer_id
  customer_name
booking
  booking_id
  customer_id
  apartment_id
  booking_start
  booking_end

如果可以预订不相交的日子：

booking
  booking_id
  customer_id
  apartment_id
booking_calendar
  booking_id
  booking_date

无论如何，您都可以轻松返回可用公寓的列表。

select
 *
from
 apartments a
 where not exists 
  (select 
     1
   from 
     bookings b 
   where 
      a.apartment_id = b.apartment_id 
      and (
        <<required_start>> between booking_start and booking_end
        or
        <<required_end>> between booking_start and booking_end
        )

Answer 2

我想你会发现PostgreSQL对范围类型的支持和相关重叠测试在这里很有用。

您可以根据范围编制索引，甚至可以使用约束来防止在同一天两次预订公寓。

通过范围，您可以根据需要存储预订或可用性。

Answer 3

这个问题在PostgreSQL中，但我想分享我在MySQL中所做的工作及其性能结果。我希望这适用于其他数据库。

方法

我创建了一个listings表和listing_availabilities，用于存储列表可用的可用时间范围。我是否创建可用性或"不可用性"并不重要，因为可用性的补充是不可用，因此，它唯一改变的不是数据集的大小，而是查询条件。

CREATE TABLE `listings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 
CREATE TABLE `listing_availabilities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `listing_id` int(11) NOT NULL,
  `start_time` int(11) NOT NULL,
  `end_time` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `start_time` (`start_time`),
  KEY `end_time` (`end_time`),
  KEY `listing_id` (`listing_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

有人

担心，如果您要将该列表提供一年，则需要实现 365 行数据。假设您有 100 万个列表，那么您将至少有 3.65 亿行列表可用性。是的，这是正确的。它要么有那么多行，要么有一行包含难以过滤的大数据包。因此，知道的唯一方法是实际测试。

测试

我在 listings 中添加了 100 万行，在 listing_availabilities 中添加了 3.65 亿行，因此一行具有 365 天的可用性范围。

以下是一些实用的查询及其性能。

获取

获取特定列表的所有列表的可用性

> SELECT * FROM `listing_availabilities` WHERE `listing_id` = 716384;
> 365 rows in set (0.01 sec)

查看此列表是否有特定时间。 如果可用，则为 1/如果不可用，则为 0

> SELECT count(*) as exists FROM `listing_availabilities` 
> WHERE `listing_id` = 1234 
> AND 1481527584 BETWEEN `start_time` AND `end_time`
> LIMIT 1;
> 1 row in set (0.00 sec)

获取前 50 个列表，并加入第一个可用时间段

> SELECT * 
> FROM listings AS l 
> LEFT JOIN listing_availabilities a 
>    ON a.listing_id = l.id 
>   AND a.start_time = (SELECT start_time FROM listing_availabilities WHERE l.id = listing_id ORDER BY start_time ASC LIMIT 1) 
> LIMIT 50;
> 50 rows in set (0.05 sec)

获取我的特定时间戳可用的前 50 个列表(假设条件满足(

> SELECT * 
> FROM listings AS l 
> LEFT JOIN listing_availabilities a 
>    ON  a.listing_id = l.id 
>    AND a.start_time = 
>        (SELECT start_time FROM listing_availabilities WHERE l.id = listing_id AND start_time < 1481536932 AND end_time > 1481536932 LIMIT 1)
> WHERE a.start_time IS NOT NULL 
> LIMIT 50;
> 50 rows in set (0.05 sec)

获取我的特定时间戳不可用的前 50 个列表(当未满足可用时间时(

> SELECT * 
> FROM `listings` AS l 
> LEFT JOIN `listing_availabilities` a 
>    ON  a.listing_id = l.id 
>    AND a.start_time = 
>        (SELECT `start_time` FROM `listing_availabilities` WHERE l.id = listing_id AND `start_time` < 1481530494 AND `end_time` > 1481530494 LIMIT 1)
> WHERE a.start_time IS NOT NULL 
> LIMIT 50;
> Empty set (3.00 sec)

这个花费了更长的时间，并且在 EXPLAIN EXTENDED 的帮助下，当在子查询中使用多个列不等式时，它似乎扫描了整个表。我仍然不能 100% 确定为什么会这样......有人可以在这里启发我。

另一种方法是将所有可用性左联接到每个列表，然后从该集合中筛选：

> SELECT l.id, a.start_time, a.end_time 
> FROM listings as l
> LEFT JOIN listing_availabilities a 
>    ON l.id = a.listing_id 
> WHERE 1481530494 
>    BETWEEN a.start_time 
>    AND a.end_time limit 50;
> Empty set (0.01 sec)

插入

自动插入第 365 天的listing_availabilities

> INSERT INTO `listing_availabilities` 
>    (listing_id, start_time, end_time) 
> VALUES 
>    (8, 1481689555, 1481689556) ...
> Query OK, 500 rows affected (0.01 sec)
> Records: 500  Duplicates: 0  Warnings: 0

删除

删除旧的可用性

> DELETE FROM `listing_availabilities` 
> WHERE `end_time` < 1481671237 
> LIMIT 100000;
> Query OK, 100000 rows affected (10.43 sec)

这似乎需要一段时间并锁定数据库，因此我决定以较小的块进行：

> DELETE FROM `listing_availabilities` 
> WHERE end_time < 1481671237 
> LIMIT 500;
> Query OK, 500 rows affected (0.09 sec)

并执行此操作，直到受影响的行变为零。

显然，您需要一个脚本来删除旧的可用性并在未来几个月添加新的可用性。

这似乎适用于我的应用程序，它应该满足 OP 想要的要求。我希望这有助于其他数据库模式。