营业时间数据库设计 [英] Opening Hours Database Design

问题描述

我们当前正在开发一个应用程序，其中多个实体具有关联的营业时间。开放时间可能跨越数天，也可能包含在一天之内。

We are currently developing an application in which multiple entities have associated opening hours. Opening hours may span multiple days, or may be contained within a single day.

例如。

或者

周一开放于06： ：00，周一关闭至15:00。

Opens Monday at 06:00 and closes Monday at 15:00.

此外，一个实体每天可能有多组开放时间。
到目前为止，我发现的最佳设计是将开放时间定义为以下内容：

Also, an entity may have multiple sets of opening hours per day. So far, the best design I have found, is to define an opening hour to consist of the following:

StartDay，StartTime，EndDay和EndTime。

StartDay, StartTime, EndDay and EndTime.

此设计可提供所有所需的灵活性。但是，数据完整性成为一个问题。我似乎找不到解决方案，不允许跨度重叠（在数据库中）。

This design allows for all the needed flexibility. However, data integrity becomes an issue. I cannot seem to find a solution that will disallow overlapping spans (in the database).

请分享您的想法。

编辑：数据库是Microsoft SQL Server 2008 R2

The database is Microsoft SQL Server 2008 R2

推荐答案

假定一个可靠的触发框架

Presuming a robust trigger framework

在插入/更新时，您将检查新的开始或结束日期是否在任何现有范围内。如果这样做了，那么您将回滚更改。

On insert/update you would check if the new start or end date falls inside of any existing range. If it does then you would roll back the change.

CREATE TRIGGER [dbo].[mytable_iutrig] on [mytable] FOR INSERT, UPDATE AS

IF (SELECT COUNT(*)
FROM inserted, mytable
WHERE (inserted.startdate < mytable.enddate 
          AND inserted.startdate > mytable.startdate)
      OR (inserted.enddate < mytable.enddate 
          AND inserted.enddate > mytable.startdate)) > 0 
BEGIN
    RAISERROR --error number
    ROLLBACK TRANSACTION
END

这篇关于营业时间数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！