Database design - how can I have a recurring database entry?

Question

I am currently using the FullCalendar JQuery module to allow a user to create a personal timetable. The events are added/updated to an SQL Server database. This is working fine.

I am trying to create a facility where each user has a database which has stored all of their events for the year, some of which can be recurring and occur every week.

I then wish to have users be able to organize meetings with other users based on the timeslots available in their timetables.

I'm not sure how to integrate these recurring events into my system, or how my algorithm would work with these recurring events.

The design I have at the moment is :

CREATE TABLE Users (
  user_id INT NOT NULL AUTO_INCREMENT,
  email VARCHAR(80) NOT NULL,
  password CHAR(41) NOT NULL,
  PRIMARY KEY (user_id)
);

CREATE TABLE Events (
    event_id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(80) NOT NULL,
    description VARCHAR(200),
    start_time DATETIME,
    end_time DATETIME,
    group_id INT NOT NULL,
        recurring boolean
);

CREATE TABLE Groups (
    group_id INT NOT NULL,
    user_id INT NOT NULL
);

Will this be sufficient? How will I have it so that recurring events are rendered on the calendar for every week? If I am lacking in any detail, please ask! Thank you very much.

How are you determining the recurrence start and end date? The start_time and end_time columns? — John Dewey, Mar 07 '12 at 22:13
Yes, the start_time and end_time of the event table. When a user creates an event, these times are inserted into the table. — Simon Kiely, Mar 07 '12 at 22:20
I would suggest a read of the iCal or .ics spec (eg http://en.wikipedia.org/wiki/ICalendar) would be a good place to start in coming to grips with the concepts and issues. — RET, Mar 07 '12 at 22:35
He asked you about the dates.ie, let's see each other every monday from 1pm to 2 pm beginning the 03/08/2012 until the 12/31/2012. — Kharaone, Mar 07 '12 at 23:03
Your design is lacking the details of the recurrence, unless you're considering it going on ad vitam eternam. — Kharaone, Mar 07 '12 at 23:11

score 2 · Accepted Answer · answered Mar 07 '12 at 22:26

2

You could use something like the following:

SELECT  *
FROM    Events
WHERE   Recurring = 0
UNION
SELECT  Event_ID,
        Title,
        Description, 
        DATEADD(WEEK, Interval, Start_Time) [Start_Time],
        DATEADD(WEEK, Interval, End_Time) [End_Time],
        Group_ID,
        Recurring
FROM    Events, 
        (   SELECT  ROW_NUMBER() OVER(ORDER BY Object_ID) [Interval]
            FROM    SYS.ALL_OBJECTS
        ) i
WHERE   Recurring = 1
AND     Interval <= 52 -- reccurs for 1 year

This will make all events repeat for 52 weeks (or whatever period you want).

As an aside, in the question you mentioned sql server, and you have tagged the question as SQL server but all your syntax appears to be MySQL (AUTO_INCREMENT, Boolean data type).

answered Mar 07 '12 at 22:26

GarethD

68,045
10
83
123

Wow, thank you very much for this answer! Yes, I wrote it using SQL syntax as this is what I am familiar with most and didn't want to mess it up! This is great, can I ask you how Interval is populated in this case? – Simon Kiely Mar 07 '12 at 22:47
1

It is just a list of numbers created by counting the rows in sys.all_objects which is a system view in sql_server containing (not surprisingly) all object (tables, keys, indexes, views, procedures etc). Even in an otherwise empty database this view will still contain well over a thousand rows which should be more than enough for your requirements. For more info see [row number](http://msdn.microsoft.com/en-us/library/ms186734.aspx) or [all objects](http://msdn.microsoft.com/en-us/library/ms178618.aspx) – GarethD Mar 07 '12 at 23:01
I see, thank you very much for the explanation. So in the line DATEADD(WEEK, Interval, Start_Time) [Start_Time] may I ask exactly what is happening here? – Simon Kiely Mar 07 '12 at 23:56
It is adding weeks to the start date, so in the example I have posted the cross join between the subquery i and the event table causes each event to be repeated 52 times, each with a start date one week after the previous record. – GarethD Mar 08 '12 at 00:03
How do we handle incase if we have recurring type like `daily/weekly/monthly/orfewdaysinaweek` in the event table? – Murali Murugesan Nov 28 '13 at 19:54
@GarethD, similar but its different.. can you please give ur suggesstion on http://stackoverflow.com/questions/20286332/display-next-event-date – Billa Nov 29 '13 at 14:51

Database design - how can I have a recurring database entry?

1 Answers1