Re: Impossible Database Design?

On Wednesday, 17 May 2006 07:41:32 UTC+10, Nikolai Onken wrote:

> I probably have not enough experience to really start arguing but I am
> still curious whether there actually is a way to set up a db which
> would
>
> 1. allow infinite events (thats what the client wants or it should at
> least I should make him believe he got it)

Aboslutely. I have several running. To be clear, those are: • "recurring events", such as birthdays or "every Monday at 10:00am" • stored as a single orw in a table (not multiple times, to the end of finite or infinite time) • without using "temporal database" principles • one needs to understand the RM, and the use of Projection in SQL.

> 2. allow the calculation whether ('infinitly') repeating events overlap
> (Is required because I have to see whether a repeating event conflicts
> with another one when adding)

Yes. Just two projections.

Of course, one needs start and end times of the appointment (a full understanding of how to handle temporal data is not required). There are a few ways to go about catching the intersection. One is to create a temporary table with a smart use of start times as index columns. First copy rows from the real calendar (the real events, not the recurring events) into the temp table. Then create rows in the temp table from the first projection. Then create rows from the second projection: intersections (overlaps as you call them) will collide.

If you are only trying to check for overlaps, that can be done in a single SELECT, without temp tables. Again, a few ways of going about it. Easiest with two derived tables and a join.

> 3. Not have lots of entries per year (say you have a university with
> 5000 students having 12 lessons 40 weeks a year), and is performant

Yes. one row per recurring event (per person, if the calendar is personal).

> 4. (allow exceptions: repeat every monday except monday 15th of May
> 2006)

Yes. The way you do that depends on how you handle the exceptions. Eg. make a real entry in the calendar for 15 May 06: that will block scheduling of any events later (recurring or not).

> ... if you have any links or working
> examples I would be more than happy to try them out..

I have several calendars with your requirements running in production systems. I say "several", because they are integrated in to whatever tables they are required in, and the contexts are quite different. Eg. the date range is infinite, but most people want only the next month or three to show up on the calendar object on the screen (when the inifinite calendar ends, in a finite computer, is a non-issue. Eg. Scheduling (the real purpose of the calendar) is usually a very application-specific thing, not relevant out the app. Eg. I just designed one for a recurring set of blood samples to be taken; to track when the were taken correctly; or late; when re-scheduling is necessary, etc. All that is clutter, because the principle is the same, and their requirement in the scope of the calendar, is identical to yours.

I do not supply code. I am happy to provide direction.

Cheers
Derek Received on Mon Sep 30 2013 - 05:37:06 CEST