database design question - appointment database with recurring appointments

From: Andrew Coyle <>
Date: Mon, 13 May 2002 18:24:42 +1200
Message-ID: <vZID8.2117$>


Firstly, I hope this question is appropriate for this group, I looked for but didnt find a FAQ and other posts seem to be in a similar vein. If I am in the wrong place, please let me know and direct me to where I should be asking, thanks :)

I have a database design question.

Is someone able to provide me with references (web, book etc) which discuss designing an appointments database (the more explicit the better, skills are not high but willing to learn and spend time on it). The issue is with appointments which are recurring, for example tom has the same meeting every monday at 9am. It is possible for this appointment to be cancelled (but not cancel the recurrence, eg the NEXT monday he will there, and the next one etc, just not this coming monday), or for the recurrence to change days (eg from now on it is at 9am every Tue rather than Monday)

I am not sure how to store the data (ie design the data structures and tables). Taking a recurrence, and pre-filling appointment data into the future seems like such a bad idea? Surely it makes problems if that schedule is ever changed, such as Tue in the above example.

My initial thought is that for a certain date you first look to see if there is an explicit entry, and if not then go back through all the entries looking to see if any "recurring" appointment would have generated an entry onto that day. Is this really inefficient, or are there better ways???

Aside, there will be over 100 seperate entities making their own individual appointments, and how many there are, and how often they recur etc is not yet known. I think it is safe to assume that there will be 15 appointments per day per entity, and over 1/2 of the entities will want to set up recurring appointments.

thanks in advance.

PS: The email address is now so overloaded with spam that I only scan and then discard. If you are replying by email please put a whole lot of

++++++++++++++++++'s at the front of the message header so that I will see
it. I will check this group first. Received on Mon May 13 2002 - 08:24:42 CEST

Original text of this message