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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
