Re: How to deal with almost recurring data?

I am creating a database which is supposed to contain many data entries
(events) that differ only in the date they occur.

So let's say event 1 occurs every Monday, Tuesday and Sunday between
January 1st and May 30th 2005.

How do I store and manage such data in a meaningful way?

The simple idea would be to store the event itself in one table and have
another table containing all the dates (all Mondays, Tuesdays and Sundays
between 2005-01-01 and 2005-05-30) plus a foreign key to event_ID =>
(date, event_id).

The problem is that we are dealing with several tenthousand events,
resulting in several million single dates if I stored it in the described
manner.

That is why I would like to know if there is a better way to store and
manage such information?

Thanks

Matt

I would say it all depends on what you want to do with the data.
If you want to look up all the possible occurences for an event, it
might be useful to have the simple solution you described. If you will
only look up the next n occurences starting from a given date, you might
be better off storing the rule to derive the dates, and then calculate
them in your software, but this will make your software more complicated
for sure (dealing with date arithmetics can be incredibly complex, I've
been there). I would go with the simple solution as long as there is
enough space in the DB to store all the occurences...

HTH,
Csaba.

On Tue, 2004-11-16 at 15:53, matthias(at)cmklein(dot)de wrote:
> I am creating a database which is supposed to contain many data entries
> (events) that differ only in the date they occur.
>
> So let's say event 1 occurs every Monday, Tuesday and Sunday between
> January 1st and May 30th 2005.
>
> How do I store and manage such data in a meaningful way?
>
> The simple idea would be to store the event itself in one table and have
> another table containing all the dates (all Mondays, Tuesdays and Sundays
> between 2005-01-01 and 2005-05-30) plus a foreign key to event_ID =>
> (date, event_id).
>
> The problem is that we are dealing with several tenthousand events,
> resulting in several million single dates if I stored it in the described
> manner.
>
> That is why I would like to know if there is a better way to store and
> manage such information?
>
> Thanks
>
> Matt
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

matthias(at)cmklein(dot)de wrote:
> I am creating a database which is supposed to contain many data entries
> (events) that differ only in the date they occur.
>
> So let's say event 1 occurs every Monday, Tuesday and Sunday between
> January 1st and May 30th 2005.
>
> How do I store and manage such data in a meaningful way?
>
> The simple idea would be to store the event itself in one table and have
> another table containing all the dates (all Mondays, Tuesdays and Sundays
> between 2005-01-01 and 2005-05-30) plus a foreign key to event_ID =>
> (date, event_id).
>
> The problem is that we are dealing with several tenthousand events,
> resulting in several million single dates if I stored it in the described
> manner.

This seems like a reasonable approach. Don't forget that PostgreSQL is
designed to store large amounts of data.

Of course, if there is some pattern to the dates then you might be
better off storing the pattern rather than the individual dates.

--
Richard Huxton
Archonet Ltd