数据库设计:与租户合作的建筑时间

Question

我正在努力设计一个数据库模式，它表示建筑物及其租户的开放时间。以下是详细情况/要求：

建筑物：

一般情况下，每个建筑物的正常营业时间取决于一个季节(夏季、冬季等)，每个建筑物都有不同的hours.

• Numerous假日、事件等，都将凌驾于正常营业时间

之上。

租户：

• 租户在建筑物内运营，因此应该受到建筑的限制，这也取决于一个季节，但与其建筑的季节性时间不一样。

理想情况下，我希望能够查询以下内容：

• ，无论一栋楼还是租客现在营业，

，

• ，什么是今天的建筑时间，

，

• ，未来X天的建筑时间，

到目前为止，我(未完成)的工作是这三个表，但我仍然很难创建一个有效的解决方案。

[Season]
id
building_id
title
start_date
end_date

[Schedule]
id
season_id
day_of_week (0-6)
open_time
close_time

[Override]
id
schedule_id
date
is_closed
is_holiday

感谢您的时间和投入。所有的答案都发展/完善了解决方案。Catcall存储单个日期的想法是我们最容易开发的模型和管理/管理接口。

Answer 1

让我们以不同的方式来看待这个问题。对于楼宇和租户来说，营业时间并无本质上的不同。也就是说，价值可能是不同的，但建筑物开放与否的概念与房客开放与否并无本质上的不同。我创建了一个单独的模式“小时”来处理这个想法。

-- Essentially a supertype, but I couldn't think of a good noun to 
-- describe it.  So I named the table "x".  It holds all the attributes 
-- common to both buildings and tenants. (That is, not very much.)
--
create table hours.x (
  x_id integer primary key,
  x_name varchar(35) not null,
  x_type char(1) check (x_type in ('b','t')),
  unique (x_id, x_type)
);

insert into hours.x values 
(1, 'First building', 'b'),
(2, 'Second building', 'b'),
(3, 'First tenant', 't'),
(4, 'Second tenant', 't'),
(5, 'Third tenant', 't');

create table hours.buildings (
  bldg_id integer primary key,
  x_type char(1) not null default 'b' check (x_type = 'b'),
  foreign key (bldg_id, x_type) references hours.x (x_id, x_type),
  other_columns char(1) not null default 'x'
);

insert into hours.buildings (bldg_id) values 
(1), (2);

create table hours.tenants (
  tenant_id integer primary key,
  x_type char(1) not null default 't' check (x_type = 't'),
  foreign key (tenant_id, x_type) references hours.x (x_id, x_type),
  bldg_id integer not null references hours.buildings (bldg_id),
  other_columns char(1) not null default 'x'
);

insert into hours.tenants (tenant_id, bldg_id) values
(3, 1), (4, 1), (5, 2);


-- Operating hours records a half open interval [opening_time, closing_time).
-- If a queried time matches the opening time, the building or tenant is open.
-- But if it matches the closing time, it's *not* open.  Examples later.
create table hours.op_hours (
  x_id integer not null references hours.x (x_id),
  opening_time timestamp not null,
  closing_time timestamp not null,
  check (opening_time < closing_time),
  check (
    -- Open and close on the same date,
    ( opening_time::date = closing_time::date ) or
    -- or close at midnight following the opening.
    ( 
      (opening_time::date + interval '1' day = closing_time::date) and 
      (cast(closing_time as time) = '00:00')
    )
  )
);

insert into hours.op_hours values
-- Bldg 1 is normally Mon-Sat, 9:00 to 5:00. Closed on Jan 1, holiday
(1, '2012-01-02 09:00', '2012-01-02 17:00'),
(1, '2012-01-03 09:00', '2012-01-03 17:00'),
(1, '2012-01-04 09:00', '2012-01-04 17:00'),
(1, '2012-01-05 09:00', '2012-01-05 17:00'),
(1, '2012-01-06 09:00', '2012-01-06 17:00'),
(1, '2012-01-07 09:00', '2012-01-07 17:00'),
-- Closed on Jan 8, a Sunday.
(1, '2012-01-09 09:00', '2012-01-09 17:00'),

-- Bldg 2 is normally Mon-Fri, 7:30 to midnight.
(2, '2012-01-02 07:30', '2012-01-03 00:00'),
(2, '2012-01-03 07:30', '2012-01-04 00:00'),
(2, '2012-01-04 07:30', '2012-01-05 00:00'),
(2, '2012-01-05 07:30', '2012-01-06 00:00'),
(2, '2012-01-06 07:30', '2012-01-07 00:00'),
-- Closed on Jan 7 and 8, weekend.
(2, '2012-01-09 07:30', '2012-01-10 00:00'),

-- "First" tenant is open 9:00 to noon and 1:00 to 4:00, Mon-Fri.
(3, '2012-01-02 09:00', '2012-01-02 12:00'),
(3, '2012-01-03 09:00', '2012-01-03 12:00'),
(3, '2012-01-04 09:00', '2012-01-04 12:00'),
(3, '2012-01-05 09:00', '2012-01-05 12:00'),
(3, '2012-01-06 09:00', '2012-01-06 12:00'),
(3, '2012-01-02 13:00', '2012-01-02 16:00'),
(3, '2012-01-03 13:00', '2012-01-03 16:00'),
(3, '2012-01-04 13:00', '2012-01-04 16:00'),
(3, '2012-01-05 13:00', '2012-01-05 16:00'),
(3, '2012-01-06 13:00', '2012-01-06 16:00'),

-- "Second" tenant is open when the building is open.
(4, '2012-01-02 09:00', '2012-01-02 17:00'),
(4, '2012-01-03 09:00', '2012-01-03 17:00'),
(4, '2012-01-04 09:00', '2012-01-04 17:00'),
(4, '2012-01-05 09:00', '2012-01-05 17:00'),
(4, '2012-01-06 09:00', '2012-01-06 17:00'),
(4, '2012-01-07 09:00', '2012-01-07 17:00'),
-- Closed on Jan 8, a Sunday.
(4, '2012-01-09 09:00', '2012-01-09 17:00'),

-- "Third" tenant is open Mon-Thu 7:30 to 9:30, Fri until midnight.
(5, '2012-01-02 07:30', '2012-01-02 21:30'),
(5, '2012-01-03 07:30', '2012-01-03 21:30'),
(5, '2012-01-04 07:30', '2012-01-04 21:30'),
(5, '2012-01-05 07:30', '2012-01-05 21:30'),
(5, '2012-01-06 07:30', '2012-01-07 00:00'),
-- Closed on Jan 7 and 8, weekend.
(5, '2012-01-09 07:30', '2012-01-09 21:30');

应该清楚的是，这种记录每一幢大楼和每一位租户每天营业时间的结构，将适应任何季节、假日、事件等的定义。默认数据可以使用相当简单的存储过程生成。所需的查询非常简单。(能够看到某件事并发现它是正确的，这是很有价值的。)

-- Is building 1 open at 9:00 am on Jan 3? (Queries for tenants are essentially
-- identical. Returns the id number if it's open, but that could be massaged 
-- into an "is_open" derived column with Boolean values.)
select x_id
from hours.op_hours
where x_id = 1
  and opening_time <= '2012-01-03 09:00' 
  and '2012-01-03 09:00' < closing_time;

-- How about on Jan 1? (Returns an empty set if closed. See above.)
select x_id
from hours.op_hours
where x_id = 1
  and opening_time <= '2012-01-01 09:00' 
  and '2012-01-01 09:00' < closing_time;

-- Which tenants, regardless of building, are open on Jan 4 at 9:00 am?
select op_hours.x_id
from hours.op_hours
inner join hours.tenants on hours.tenants.tenant_id = hours.op_hours.x_id 
where opening_time <= '2012-01-04 09:00' 
  and '2012-01-04 09:00' < closing_time;

请注意，每个查询的操作间隔为半开间隔。您不能使用中间操作符，因为它的操作间隔是封闭的。

这种结构不符合哪些要求？

• 租户的营业时间必须是建筑物营业时间的子集。除非dbms支持断言，否则不能以声明方式执行。可以使用数据库中的过程代码来完成。

Answer 2

你的桌子设计有了一个很好的开端。

Building model (您的模型到目前为止)

可以调整[Override]表以简化数据维护和查询。

假设您的业务规则是覆盖总是针对(整个)一天，那么您希望覆盖在季节级别，而不是计划级别。这使您不必担心一周中哪一天会出现覆盖。通过在覆盖表中包含start_date和end_date列，您也可以更容易地进行泛化。此外，您的标志(is_closed、is_holiday)可以简化为单个枚举列(closed_reason)。这将允许您在将来添加新的原因，而不必更改架构和查询。

承租时间(下一步)

您的tennant计划模式应该类似于您的建筑时间。根据您期望Tennant时数与构建时数不同的频率，您可以在tennant表中添加一个标志来指示uses_building_schedule。这表明没有tennant计划条目。

对于使用与其占用的建筑物不同的时间表的租用者，只需使用相同的季节/计划/覆盖结构。

由于有一条规则规定tennant时数必须受构建时数的限制，因此需要添加过程代码来强制执行此规则。我会在查询时，而不是在数据维护时这样做。换句话说，当我查询tennant的时间时，我会看看他们是否有开放时间，然后将这些开放时间限制在建筑物的时间上。

Answer 3

我不确定它在多大程度上符合你的要求，但让我给出一个想法：

​

​

这种模式具有以下特点：

建筑物或租户有一个计划(可能与其他buildings/tenants).

• A计划共享，也可能不共享)，即一组intervals.

• Each间隔在计划中具有优先级:优先级较高的“覆盖”间隔和优先级较低的间隔。看看U1).

• Intervals是如何被约束的，这样没有任何间隔可以两次属于同一个调度，并且不能相对于属于同一计划的其他间隔有一个模糊的优先级(唯一约束SCHEDULE_ITEM可以在计划之间共享，因此您可以设置一个覆盖年度事件的单个间隔，然后在多个计划之间共享它。如果事件发生变化，您只需在一个地方更新它。

这个模型的问题在于，它的处理量很大，而且DBMS无法帮助您避免数据中的一些无意义的情况(您需要在应用程序级别这样做)。另一方面，它是非常灵活和强大的。

在给定的一天中为建筑物或租户寻找工作时间并不简单:您需要按照时间表下的所有时间间隔按优先级顺序进行交叉。要为特定的房客找到时间，你首先要为她的大楼找个时间，然后再把它们相交。

示例

一个时间表..。

• 全年上午9点到下午5点，
• 除5月至10月上午8点到晚上6点，
• (星期六除外)上午9点到下午1点，
• ，周日没有小时(

h 121)，1月1日、7月4日和12月25日没有小时h 222f 223

...could的表示方式如下：

SCHEDULE_ID     PRIORITY    MONTH_START     MONTH_END       DAY_OF_MONTH_START      DAY_OF_MONTH_END        DAY_OF_WEEK_START       DAY_OF_WEEK_END     HOUR_START      HOUR_END
1               1           NULL            NULL            NULL                    NULL                    NULL                    NULL                9 AM            5 PM
1               2           5               10              NULL                    NULL                    NULL                    NULL                8 AM            6 PM
1               3           NULL            NULL            NULL                    NULL                    6                       6                   9 AM            1 PM
1               4           NULL            NULL            NULL                    NULL                    7                       7                   NULL            NULL
1               5           1               1               1                       1                       NULL                    NULL                NULL            NULL
1               6           7               7               4                       4                       NULL                    NULL                NULL            NULL
1               7           12              12              25                      25                      NULL                    NULL                NULL            NULL