erwin_des
asked on
Data model for Recurring Events -> performance
I need to create a data model to store recurring events based on a calendar (date and time on a highly detailed base).
For example, an event is introdfuced on a specific date in the database. This is done once. But, I need to visualize the occurences in the future (and past) based on a complex RecurrencePattern (for example, each day, or every 2 days/weeks/months => daily - weekly or monthly pattern). I need a very fast query that enables me to visualize for a specific period (in the future or the past) to visualize all the available events.
My question is, how to model this in a data model so it is still fast enough to search (over 20.000 events).
What are the good modeling practices? Any examples for a generic solution?
For example, an event is introdfuced on a specific date in the database. This is done once. But, I need to visualize the occurences in the future (and past) based on a complex RecurrencePattern (for example, each day, or every 2 days/weeks/months => daily - weekly or monthly pattern). I need a very fast query that enables me to visualize for a specific period (in the future or the past) to visualize all the available events.
My question is, how to model this in a data model so it is still fast enough to search (over 20.000 events).
What are the good modeling practices? Any examples for a generic solution?
ASKER
Sort of...
I'm looking for a good implementation model where i can easily store Recurrent Events providing me the ability to extract in a very fast way (!!!!), all the events for a specific (future or past) timespan.
It is the model I want to construct.
I'm looking for a good implementation model where i can easily store Recurrent Events providing me the ability to extract in a very fast way (!!!!), all the events for a specific (future or past) timespan.
It is the model I want to construct.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i checked it, and it is very fast
ASKER
I was already at this solution which works fine for the part of the days, but the problem is that the model I use has more details on the planning level for weeks and months.
- For weeks : it should be possible to select one or more specific days in the week like mon, thu, fr ...
For example : every week (or even every 2 weeks) , only on mon, thu, fri, sat.
- For months : there also it should be possible to select a specific day(number) in the month or every (specific) weekday every first, second ... week in the month. For example (1): every month (or even every 2 months) but on the 23rd day only ; (2) every month , every second thursday on the month.
If i arrive to construct a fast query for week and month separately and use a union on both, I have the solution.
- For weeks : it should be possible to select one or more specific days in the week like mon, thu, fr ...
For example : every week (or even every 2 weeks) , only on mon, thu, fri, sat.
- For months : there also it should be possible to select a specific day(number) in the month or every (specific) weekday every first, second ... week in the month. For example (1): every month (or even every 2 months) but on the 23rd day only ; (2) every month , every second thursday on the month.
If i arrive to construct a fast query for week and month separately and use a union on both, I have the solution.
you can have another table indicating the limitations you specified and join that table as well
ASKER
Thanks ! I'm now trying to do some performance tuning on my query. I will post my solution and hope I can count on your expertise to fine-tune my solution.
you have a record in the table that says an event happens every 2 days,
now you want a query that will return the dates when this event will happen?