Data model for Recurring Events -> performance

Posted on 2009-02-18
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?
Question by:erwin_des
    LVL 37

    Expert Comment

    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 Comment

    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.

    LVL 37

    Accepted Solution

    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)
          insert into numbers values (@i)
          set @i = @i + 1

    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:
     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
    LVL 37

    Expert Comment

    i checked it, and it is very fast

    Author Comment

    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.
    LVL 37

    Expert Comment

    you can have another table indicating the limitations you specified and join that table as well

    Author Closing Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now