Thanks The standard case is: I enter a date and 2 parameters of the event and I get the result set. Even though with Java date arithmetics are quite simple and I could therefore extend my software in that fashion, I would much rather perform all queries entirely in the database. So in case I go about it the way I described, I will have 4 million entries in the table that stores the dates and the references to the event. Now - how do I handle that database if I want to maintain very good query times (<1-3 seconds)? I have no idea how to handle a 1GB database in terms of query performance, like what tricks to use, what to do and what to avoid. Thanks Matt --- Ursprüngliche Nachricht --- Datum: 16.11.2004 16:32 Von: Csaba Nagy <nagy@xxxxxxxxxxxxxx> An: matthias@xxxxxxxxxx Betreff: Re: How to deal with almost recurring data? > 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@xxxxxxxxxx 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@xxxxxxxxxxxxxx) > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
