Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2009
    Posts
    3

    Design of Databases for storing the details of the recurrent occurrence of an event

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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?

  4. #4
    Join Date
    Nov 2009
    Posts
    3
    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.

  5. #5
    Join Date
    Nov 2009
    Posts
    3
    @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.

  6. #6
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    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.

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by kshtjsnghl View Post
    @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 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?

  8. #8
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    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.

  9. #9
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    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.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Derek Asirvadem
    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.
    Last edited by mike_bike_kite; 11-08-09 at 08:02.

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.
    Last edited by mike_bike_kite; 11-09-09 at 07:01.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •