Re: Impossible Database Design?

Nikolai Onken wrote:
> Hey Volker,
>

Hi Nikolai,

Hope you don't mind me joining in here.

> thank you for your reply!! Thats what I was wondering! How long could
> Outlook actually run..

It crashed badly at 2Gb for a person I know ...

> I think the best solution for my case would be to have three tables:
>

To do all this purely and properly you need a temporal database, but last time I looked they were still only at the theoretical stage, there are competing theories and I'm not aware of any main-line commercial products (that I'd wanna buy).

Meantime, I've had to solve something similar and I did it pretty much the way you described. I used a rdbms to store some (non-relational) data and a procedural language to do the rest.

You can't take a pure relational approach because the RM is based on sets, which contain *unordered* elements but time is ordered! Queries range from awkward to impossible. For instance, how can you test for overlap in the events table?

There are several types of databases the relational model can't handle effectively, this is one of them. This isn't to say you can't use a rdbms in a non-relational way. (My excuse was this is an OO program that happens to use a db for persistence).

> events:
>
> datetime_start
> datetime_end
> name
> description
> other_fields
>
> event_exceptions:
>
> datetime_start
> datetime_end
> replacingperiod_datetime_start
> replacingperiod_datetime_end
> name
> description
> other_fields
>
> event_rules:
>
> datetime_start
> datetime_end (if NULL = never ending)
> repeat_pattern
> name
> description
> other_fields
>
> Then I write the data into the events table according to repeat_pattern
> and event_exceptions...
> This at the same time will enable me to run queries on future events
> and have some sort of support for infinite repeating events.
> Would anyone concider this a workable design for a calendar?

Yes I consider it workable, it works for me.

My events were between any two timestamps and I only needed forward data for a couple of years and nothing in arrears. My repeated or perpetual events were only down to the day. If you need billions of nanosecondly events I dare say you'll need to reconsider ;-)

> The repeat pattern could then easily be using CRON Syntax or some user
> defined syntax which will be executed by the application or by the
> database if it supports events...

Agreed, I used CRON as inspiration too.

> Regards,
>
> Nikolai

HTH
Cheers. Received on Wed May 17 2006 - 19:35:27 CEST