How would one represent scheduled events in an RDBMS?

Question

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?

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

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 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.

Answer 1

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]

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.

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.

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!).

Answer 2

To record the rules for "periodic repetition", you could take inspiration from crontab's format, except of course that you do not need constraints on minutes and hours, but rather day of week, day of month, and the like. Since more than one (e.g.) weekday could be in the schedule, for NF purposes you'll want typical intermediate tables as used to represent many to many relationships, i.e. one with just two foreign keys per row (one to the main table of events, one to a table of weekdays) -- and similarly of course for days-of-month, and the like.

Presumably each scheduled event would then also have a duration, a category, perhaps a location, a name or description description.

"How normal" is the form (once you've taken care of the "sets" with the many-many relationship mentioned above) depends mostly on whether and how these various attributes depend on each other - for example if every event in a certain category has the same duration, you'll want to have a separate auxiliary table with id, category and duration, and use foreign keys into this table rather than repeat the paired info. But from what you say I don't see any intrinsic violation of normal-form rules, save for such dependency possibilities (which are not inherent in what little you have specified about the event scheduling).