Modeling single/multiple/indefinite dates for appointments

[MrMeric]

How would I go about representing appointment dates that could either occur once, multiple times, or indefinitely?
For example, I'd like to know how an application like Microsoft Outlook stores its appointment/meeting date information.

Thanks!

 

[MajP]

It is does it dynamically. Only one appointment exists with a recurrence value. You could do the same thing, and when you display your user interface you generate the recurring appointments first then load the non reoccurring.

 

[MrMeric]

What exactly do you mean? How could the same type of thing be stored in a database?

 

[johnherman]

Something like this

Appointment Table
Appointment ID unique key
Appointment Time timestamp
Appointment Recurrence Indicator
O = One Time, D = Daily, K = Weekdays Only, W = Weekly,
M = Monthly, L = Last Working Day of Month, etc
Appointment Description
etc

Recurrence Table
Recurrence Code (as above O,D,K,W,M,L, etc)
Recurrence Time Window (0 for O, 24 for D, etc.)
Recurrence Calculation
etc

Next Appointment = dateadd (timestamp + recurrence factor)

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[MrMeric]

johnherman
Thanks for the reply! This is looking good. Could you please elaborate on these two fields:

Recurrence Time Window (0 for O, 24 for D, etc.)
Recurrence Calculation


Thanks!

 

[johnherman]

Recurrence Time Window is a codeindicating when the next recurring appointment will occur.

Calculation is not always used. For instacnce, if Weekly recurrence, then NEXT_APPT = DATEADD(LAST_APPT,7) {using Oracle's DATEADD function.

If the recurrence is last Friday of day of month, there would be some sort of special calculation you would need to do. Example:
WHILE DayOfWeek <> '/Friday'
WHILE Month = CurMonth
etc


-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright