如何在RDBMS中代表安排的事件？ [英] How would one represent scheduled events in an RDBMS?

问题描述

我必须按照每周，每天或每月的方式存储预定的活动（例如说上课时间）。事件可能发生在每个星期一和星期三，或每月的第二个星期四。有没有办法将此信息存储在遵守3NF的RDBMS中？

I have to store scheduled events, (like say class times, for example) that can be organized on a weekly, daily or monthly basis. Events can occur, say, every Monday and Wednesday, or every second Thursday of the month. Is there a way to store this information in an RDBMS that adheres to 3NF?

编辑：这不是功课;我正在和朋友一起建立自己的教化，我们希望在3NF。

This is not homework; I'm building something with a friend for our own edification and we want it in 3NF.

具体来说，我试图在RC教区存储大量和供认时间的时间表。这些可以在很多方式的地狱中安排，例如每个星期天在x时间或每个星期二/星期四在不同的时间。有时这只是本月的第三个星期五，而其他的只能在一定时间内每年提供一次。我不仅需要存储这些信息，而且可以查询它，以便我能够在第二天或周内快速得到可用时间的全面列表。或者任何一个。

To be specific, I'm trying to store the schedules for mass and confession times at RC parishes. These can be scheduled in a hell of a lot of ways, such as every Sunday at x time or every Tue/Thu at a different time. Sometimes it's only the third Friday of the month,and others are only offered at a certain time once a year. I need to not only store this information, but query it, so that I can quickly get a comprehensive list of available times in the next day or week or whatever.

I假设严格来说，3NF不是一个要求，但是如果它是更容易的，并且比以后更改我们的模式更好地获得它的正确。

I suppose that strictly speaking 3NF isn't a requirement, but it would be easier for us if it were and it's better to get it correct off the bat than to change our schema later.

推荐答案

是的，我已经通过以下方式解决了我的同事问题：

Yes I have solved this problem with my co-worker in the following way:

CREATE TABLE [dbo].[Schedule](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL
)

CREATE TABLE [dbo].[ScheduleInterval](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ScheduleID] [int] NOT NULL,
    [ScheduleIntervalUnitID] [int] NOT NULL,
    [Interval] [smallint] NOT NULL
)

CREATE TABLE [dbo].[ScheduleIntervalUnit](
    [ID] [int] NOT NULL,
    [Name] [varchar](50) NULL
)

INSERT INTO ScheduleIntervalUnit (ID, Name)
SELECT '1' AS [ID], 'Day' AS [Name] UNION ALL
SELECT '2' AS [ID], 'Week' AS [Name] UNION ALL
SELECT '3' AS [ID], 'Month' AS [Name] 

计划跨越一段时间和间隔发生在该时间长度内。计划间隔单位确定间隔的长度（每隔一个（2）或每三分之一（3）等），星期（星期一，星期二等）的周数，以及月（日历年）。使用这种方式，您可以对数据库执行查询和逻辑以检索日程表。

A schedule spans a length of time and intervals occur within that length of time. The schedule interval unit determines the length of the interval (days as in "every other" (2) or "every third" (3) etc.), week (day of the week, such as Monday, Tuesday, etc), and month (of the calendar year). Using this you can conduct queries and logic against your database to retrieve schedules.

如果您的日程安排需要更好的解决方案 - 时间至数小时，分钟，秒 - 查看Unix实现的 cron 。我最初开始这条路线，但发现以上是一个更加简单和可维护的方法。

If your schedules need better resolution - down to hours, minutes, seconds - look at the Unix implementation of cron. I originally started down that route, but found the above to be a much more simplistic and maintainable approach.

单一的日期/时间跨度 - 如定义的学校学期开始9月9日和11月4日结束 - 可以包含多个时间表（所以每个星期一的艺术类和每隔一天为物理编辑 - 但你需要做更多的工作考虑假期和周末！）。

A single date/time span - such as a defined school semester starting Sept 9th and ending Nov 4th - can contain multiple schedules (so every Monday for Art class, and "every other day" for Phys Ed - but you'll need to do more work for considering holidays and weekends!).

这篇关于如何在RDBMS中代表安排的事件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！