我应该如何（如何）在数据库中保存计划(How should I (how would you) save schedules in database)

我应该如何（如何）在数据库中保存计划(How should I (how would you) save schedules in database)

我有一个应用程序，用户可以在特定日期范围内为某个实体设置日程安排。 计划编辑与输入日历约会类似：

何时开始和何时结束（小时） 开始日期并设置每周复发天数（即每周六和周日或仅每个Moday） 重复日期结束 - 设置重复发生结束的日期

例

我想将2011年1月的周末时间安排从早上7点到下午5点。

我将开始日期设置为1.1.2011 将结束日期设置为31.1.2011 我从7:00开始到17:00结束 每周复发掩码启用星期一

然后我还会输入一个新的时间表，与现有的时间表重叠，但仅限于1月的上半部分：

设置开始日期1.1.2011 设定结束日期为14.1.2011 设定时间为9:00至15:00 启用星期一。

题

我可以将每天的时间表存储在一个表中，如：

create table EntitySchedule ( EntityID int not null references Entity(EntityID), ForDay date not null, StartAt time not null, EndAt time not null )

对照

它们中的每一个（每日表和每个时间表）都有其优点和缺点：

每天的表格很容易获得特定日期的时间表 每天的计划不可能编辑重复数据，您总是会输入覆盖现有数据的新计划 每天几年后，表将有大量的记录，这些记录必须减轻 如果没有计算，则在每个计划表中获取特定日期的计划是不可能的 按计划表允许将计划从一个日期范围复制到另一个日期范围，这将是一个非常受欢迎的功能，通过简单地从不同的日期范围导入计划数据并将其应用于新的日期范围，可以简化创建计划。

使用场景

不要将此视为我们通过个人日历了解的日常约会。 而是将其视为非常灵活的商店/商店（实体）营业时间（时间表）。 所以我的数据库将有很多商店，他们有非常灵活的营业时间，通常每周重复一次。

编辑计划通常是对现有数据的覆盖，因此当在已定义的日期范围内的开放时间已经存在时，我们不会真正更新现有的计划定义，而是创建一个覆盖现有计划的新计划。 如果有每日时间表，这很简单。 我只是覆盖那些适用于新计划日期范围的日子。

但是在每个计划表的情况下，这变得更加复杂：

我可以在表格中插入新记录，然后在阅读特定日期的最新记录获胜时（LIFO方式）。 这意味着每个调度读取（选择）将包括更复杂的查询，其中我将必须返回与特定实体相关的所有记录，这些记录定义正确范围内的日期，然后返回最后一个。 这对于获取特定日期的日程安排很好，但是当我想要日程安排的时间表时，我的生活很难... 定义的计划范围不重叠，当我插入新的计划范围时，它也可能意味着应该更改现有的计划定义（或许多计划定义），甚至拆分为两个。

第一个似乎是一个更好的方法。 但是获取日期范围的计划的查询变得相当复杂并且可能不是非常快。 想象一下获得2011年1月的实体时间表。 读取数据应始终以每日表的形式产生结果。

题

有没有保存计划数据的标准方法？ 您如何建议我应该保存这些数据？

I have an application where users are able to set schedules for certain entity within a certain date range. Schedule editing will be similar to entering calendar appointments:

when it starts and when it ends (hours) start date and set weekly recurrence days (ie. every Saturday and Sunday or every Moday only) end of recurrence date - sets date when recurrence should end

Example

I would like to set weekend schedule for January 2011 from 7am to 5pm.

I set start date as 1.1.2011 Set end date as 31.1.2011 I set start at 7:00 and end at 17:00 enable Monday on weekly recurrence mask

Then I would also enter a new schedule that overlaps with existing one but just for the first half od January:

Set start date 1.1.2011 Set end date 14.1.2011 Set time from 9:00 to 15:00 Enable Monday.

Question

I could be storing schedules for each day in a table like:

create table EntitySchedule ( EntityID int not null references Entity(EntityID), ForDay date not null, StartAt time not null, EndAt time not null )

Comparison

Each of them (per-day table and per-schedule) has its advantages and disadvantages:

Per day table would be easy to get schedule for a particular day Per day schedule would have no possibility of editing recurrence data, you would always enter a new schedule overriding existing data After few years per-day table will have huge amount of records which would have to be mitigated In case of per-schedule table getting schedule on a particular day isn't possible without calculation Per-schedule table allows copying of schedule from one date range to another, which would be a very welcome feature that would make creating schedules simple by simply importing schedule data from a different date range and applying it to a new date range.

Usage scenario

Don't think of this as usual calendar appointments that we know from our personal calendars. Rather think of this as very flexible shop/store (entity) opening hours (schedule). So my database will have many many stores and they have very flexible opening hours that usually repeat on weekly basis.

Editing schedules is usually an overwrite of existing data, so when opening hours within a defined date range already exist we wouldn't really update existing schedule definition but rather create a new one that would override the existing one. In case of having a per-day schedule this is very simple. I'd just overwrite those days that are applicable for the new schedule date range.

But in the case of per-schedule table this becomes more complicated:

I could just insert a new record in the table and then when reading schedule of a particular day newest record wins (kind of LIFO approach). This means that every schedule read (select) will include a bit more complex query where I would have to return all records related to a particular entity that define dates in correct range and then return the last one. This is fine for getting schedule of a particular day but makes my life hard when I'd want schedules for a date range... Defined schedule ranges don't overlap and when I would insert a new one It could as well mean that an existing schedule definition (or many of them) should be changed or even split into two.

The first one seems a better approach. But query of getting schedules for a date range becomes rather complex and probably not very fast. Imagine of getting entity schedule for January 2011. Reading data should always yield results in the form of per-day table.

Question

Is there any standard way of saving schedule data? How would you suggest I should be saving this data?

最满意答案

我曾几次与这个问题搏斗过。 我认为最好的方法是将开始日期/时间和结束日期/时间存储为开始和结束的单个字段，或者按照建议将日期和单独的时间字段存储起来。

我认为这是最灵活的，虽然它需要在您的应用层进行更多计算以重叠计划等，但它是所有备选方案中“最不好的”。 我不确定它是一种标准方式。

PS。 我曾经实现过一个系统，其中一年中的每一天都有一个表中的记录和一个单独的表存储了当天使用的所有时间表，但它变得比它的价值更麻烦，因为它需要在应用层上进行大量编码，但在不同的地方。 使用日期/时间我认为是最好的解决方案。

I have wrestled with this problem a few times. I think the best way is to simply store the start date/time and end date/time either as a single field for start and end or as a date and a seperate time field as you have suggested.

I think this is the most flexible and although it requires more calculation at your app layer for overlapping schedules etc, its the 'least bad' of all the alternatives. I'm not sure its a standard way though.

ps. I did once implement a system where each day of the year had a record in a table and a seperate table stored all the schedules using that day, but it just becomes more hassle than its worth as it requires substantial coding on the app layer also, but in differnt places. using Date/Times I think is the best solution.