We help IT Professionals succeed at work.

Data model for Recurring Events -> performance

Medium Priority
632 Views
Last Modified: 2013-11-15
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?
Comment
Watch Question

i'm not sure i understand
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?

Author

Commented:
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.

this is what i have in mind:

have a table with numbers:

create table numbers (num int);

declare @i int
set @i = -300

while (@i <= 300)
begin
      insert into numbers values (@i)
      set @i = @i + 1
end

and create a table for your events, and store the interval for that event
create table events (event_name varchar(100),
    interval_amount int , interval_type varchar(100))

now, when you want to generate the future events, try something like this:
select  
 case interval_type
  when 'day' then dateadd(day, t2.num*t1.interval_amount, getdate())
  when 'month' then dateadd(month, t2.num*t1.interval_amount, getdate())
  when 'year' then dateadd(year, t2.num*t1.interval_amount, getdate())
 end as next_date
from events t1 cross join numbers t2
where t2.num between -50 and 50

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
i checked it, and it is very fast

Author

Commented:
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.
you can have another table indicating the limitations you specified and join that table as well

Author

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.