7

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.

pmr
  • 58,701
  • 10
  • 113
  • 156
astine
  • 276
  • 2
  • 9
  • It really won't do you much good in the long run to post your homework assignments on forums. Have you given it any thought at all? How far have you gotten? – Aaron Alton Jun 19 '09 at 03:09
  • 1
    Although this _could_ be homework, it could also certainly not be homework. I don't think it's particularly appropriate to give a question a tag of homework unless it obviously is, plus this *is* a problem with interesting solutions. – Paolo Bergantino Jun 19 '09 at 03:17
  • ... that being said, this is definitely a duplicate, I just can't find the other ones.... – Paolo Bergantino Jun 19 '09 at 03:18
  • Reinstating Homework tag. Justification: Who needs 3NF as a requirement but students? In a business, it'd be 'whatever gets it done' first, and 'whatever gets it done most efficiently (or quickly)' second. – George Stocker Jun 19 '09 at 03:26
  • This niether homework, nor strictly, speaking business. It is for a project of a friend and mine. We are doing it for ourselves so we can afford to be anal about our self-imposed requirements. @Aaron I do have a solution, It just feels over engineered. @Paolo there was a similar question about Python scheduling, but is was answered with a library and didn't address my needs. – astine Jun 19 '09 at 03:53
  • 2
    @Gortok: I disagree. I've programmed applications with requirements very close to this and to this day I've never been happy with how I handled it. This is certainly a legit question and as the OP said this is not homework. @astine: no, that's not it. These are the two questions I was thinking of: http://stackoverflow.com/questions/85699/whats-the-best-way-to-model-recurring-events-in-a-calendar-application, http://stackoverflow.com/questions/123793/design-question-how-would-you-design-a-recurring-event-system – Paolo Bergantino Jun 19 '09 at 06:03

2 Answers2

3

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

Alex Martelli
  • 854,459
  • 170
  • 1,222
  • 1,395
  • Thanks. I've already thought of a solution almost exactly like this, though I'd probably use an enum (I'm on postgres) than a table of weekdays. As I've mentioned above, the events most certainly won't have the same duration. Possibly, they should even have arbitrary durations. – astine Jun 19 '09 at 04:10
  • I'd suggest the cron model as well. And don't worry about normalization - not all data structures are relational. In my experience, you may want to use some means other than SQL to materialize actual instances of the schedulable elements. And rather than storing arbitrary durations, maybe store begin date and end date. – dkretz Jun 19 '09 at 04:24
2

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

cfeduke
  • 23,100
  • 10
  • 61
  • 65