Hi, I need to implement a feature similar to the one provided by Microsoft Outlook to make your meeting appointment recurrent. I am trying to figure out the optimised Database design that i will be requiring for implementing this feature. The requirement is something like that each run or task entered by the user will also be applicable for scheduling like a recurrent event - weekly,monthly or yearly. Could you please suggest me the Database model - table structure (with constraints) for storing these details in the DB which can be afterwards accessed by the program to do the appropriate task. Screenshots for some of the possible scheduler details can be found at the following link - ImageShack - . Please let me know if you require any other details.

Extremely difficult task.
I implemented a fairly generic calendering system on a project not too long ago, but the coding and structure are very advanced, and it would take someone with considerable skills to implement it.
I suggest you define your requirements as narrowly as absolutely possible (only allow weekly occurrences, etc) before you begin, and code specifically to those requirements.

Looking at the images it looks like your program is offering exactly the same functionality as MS Office - so why don't you just use MS Office?

I understand that it is a very difficult task, but i just need a db model for storing th details for the recurrence request so that the same can be accessed and parsed to schedule the user intended task.

We have a mysql DB running at the backend for storing these details. As soon as the user submits a request, a request id with the details of the request is stored in the table and then a action corresponding to it is taken by the program. More clarification would be like that the users intent is to run a sql script,getting the values and then performing statistical analysis to it. But as the oracle reference DB is dynamically updated by many users, he wants to run it in a recurrent manner and get the analysis done. Note that the mysql db and the ref DB are different.

@mike
the images were shot from the Outlook itself .
The UI for my project i have not yet designed. They were just to clarify the kind of granularity that we might be needing for this feature.

The data structure to record the recurrance isn't that difficult. The scripting to make it work will be much harder.

I can't see your pictures, my work blocks access to imageshack. But the structure that would match outlook 2007 would be:

EventID (FK - the key to your meeting)
LastEventDate datetime (The last time the meeting was held)
StartDate datetime (The first time the scheduler runs)
EndDate datetime (The last time the scheduler runs)
StartTime datetime (The time the meeting starts)
EndTime datetime (The time the meeting ends
Daily bit (True if meeting is run daily)
DailyEveryXDays int (Meeting is done every X days)
DailyEveryWeekday bit (Meeting is done every weekday)
Weekly bit
WeeklyEveryXDays int
WeeklySunday bit
WeeklyMonday bit
WeeklyTuesday bit
WeeklyWednesday bit
WeeklyThursday bit
WeeklyFriday bit
WeeklySaturday bit
Yearly bit
YearlyEveryXYears int
YearlyOnMonth int (representing month)
YearlyOnDay int (representing day of month)
YearlyOnGivenX int (representing 1st, 2nd)
YearlyOnGivenDayofWeek int (representing monday, tuesday, etc)
YearlyOnGivenMonth int

Note that most of the options are mutually exclusive so much of each row will be null.

The pictures were small and my eyes are sadly not what they were. For the full functionality are you planning on adding a word processing feature and perhaps electronic mail?

I've been doing a little project parsing calendar and task manager files here and I can tell you, that stuff's no joke. Handling the recurrence rules is a good 400 lines of Python. (And, no, that's not even a release candidate yet.)

I'd strongly recommend looking at the ical standard, RFC 2445 to start with. That's actually a fairly lightweight standard compared to what something like MS Exchange handles.

Why don't I store that stuff in a database? Simply because my app a. runs as a batch process (literally, it runs off cron) and doesn't have to be particularly fast and b. I have to fetch it from user's application anyway. And, as someone hinted at, it's a lot of work to reinvent all that interface that the user is already comfortable with. Most of that app is designed to leverage those existing programs.

kshtjsnghl

There are two parts to the problem/solution.

1 Model
Get the data model absolutely correct, the reason will become clear later on. No incomplete Normalisation, No "denormalised for performance" nonsense; that severely impedes the production of clean and stable code. For recurrence, in addition to the Event (whatever) table you are recurring, you will need discrete child tables tables as follows. Each table will require different columns to support the full capability in the applicable [Outlook tab]. Keep the actual [past] Event occurrence and details out of them (keep that in the Event table):
[Event]RecurDay
[Event]RecurWeek
[Event]RecurMonth
[Event]RecurYear

A single denormalised table with mostly null columns will make the code very ugly, and you will be debugging it for years (no exaggeration, my customer was doing that until they got me to replace it). A single denormalised table is also far more prone to errors (from the app that sets the recurrence)

2 Code
You need to fully understand that SQL allows projections. In theoretical terms, a simple or complex join is a projection [of all possibilities] first, then limited by predicates or qualifiers (the WHERE clause). You need SQL code that:
- projects the Event (whatever) rows
- through the applicable Recur% table rows
Get that working without any issues first, then
- limit the projection to the timeframe (eg. Nov 2011) that the app actually needs to paint or whatever
- that result set needs to be a single generic type (not one of the four types above).

As long as you have a clean data model, you can produce the recurrence/projection with a single clean SELECT statement (one for each recurrence type above). Four simple SELECTs replaced over 600 lines of complex SQL that never quite worked. I did not use UNION because it demands a worktable. Of course, put it in a sproc, so that the four SELECTs are in one place, and the caller does not need to determine the Recur% type to project; and for performance.

You can allow just one Event or all Events that occur in the recurrence timeframe, the difference is only one clause in the SELECT.

Ask further specific questions.

Could you give all the fields and tables required so we can make comments. Obviously, without a concrete example, your suggestion is nothing more than loose conjecture.

Derek

Obviously you haven't produced any fields yet so I'm guessing how things might work but I can't see how your design has any fewer null fields than a single table design. Does your database design need to be modified if we require a new type of re-occurrence of an event - let's say we now want to have hourly events? The SQL involved when writing schedulers always tends to be quite complex but does splitting the data across (at least) 5 tables serve to make things any simpler? It would definitely help in our understanding if you provided some fields for your tables.