Handling reoccurance appts in Scheduling application

[Cirene]

I'm designing my db for a online Scheduling web application. How do you
suggest I handle reoccuring events?

In my appointments table should I just add 1 record for EACH time the
appointment will appear (and link all "related" appts with a key field)? Or
should I just have 1 record for each appointment, whether it's reoccuring or
not.

I downloaded the RadScheduler app and noticed in their sample db they had a
"RecurrenceRule" and "RecurrenceParent" column in their Appts table. But
didn't understnad the implementation. A sample RecurrenceRule data was:
DTSTART:20070330T063000Z
DTEND:20070330T073000Z
RRULE:FREQ=DAILY;INTERVAL=1;UNTIL=20070406T210000Z;BYDAY=MO,TU,WE,TH,FR;

It looks like they use 1 record for each appt, whether its a one time or
recurring appt. But, I guess you would have to write to code figure out the
future "phantom" recurring appts????

Any advice would be appreciated.

 

[sloan]

I'm sure there are other products that implement the standard, but here is
one:

http://www.aspose.com/categories/utility-components/aspose.icalendar-for-.net/default.aspx

If you're dealing with dates and rules, I'd pay the little bit of $$$ for
something like the iCalendar.

 

[sloan]

What I did was keep a instance row in the db....and I had an internal cutoff
date.
Usually, up to 2 years.

The problem is ... when they say "never ends"...how far in the db do you
store them?

I picked a 2 year max (configurable # years, but something besides
"forever".

THEN I had a IsSoftDeleted column...because if they removed an instance, but
you reran the rule....the removed instance would reappear.

Thus I used to IsSoftDeleted flag to know the difference and avoid the
re-generation.

I hope that makes sense.

...

 

[Cirene]

So, if they created a new appt with a reocurrence of every 2 weeks, you
would create all the future records with a little "tag" to show that they
are all grouped together? That makes sense and that's kind of what I was
planning to do.

But, if they come back and edit the appts from every 2 weeks, to every 3
days, but only M, T, and S, then you would delete the "group" and recalc?

Something like that?

Thanks!!!

 

[sloan]

I use the EventInstance.RemoveDate as the "IsSoftDelete" flag.
If it is null, then I assume its legit, if it is populated, I assume it was
softdeleted.


This is 2 year old code, so don't ask too many detailed questions about it.

My soft delete "IsRemoved" case statement in sql server looks like this:

Select
--EventDefinitionID ,
EventDefinitionUUID ,
CAST(EDTS as int) as EDTS ,
EventDefinitionTypeUUID ,
EventDefinitionName ,
EventICalendarExpression ,
EventMasterStartTime ,
EventMasterEndTime ,
CreateDate ,
UpdateDate ,
LastInstanceUpdateDate ,
RemoveDate ,

'IsRemoved' =
CASE
WHEN RemoveDate IS NULL THEN 0
ELSE 1
END

From
dbo.EventDefinition ed




Here is my DDL. I can't help much beyond this, I never finished everything
myself.





if exists (select * from sysobjects
where id = object_id('EventInstance'))
DROP TABLE EventInstance

if exists (select * from sysobjects
where id = object_id('EventDefinition'))
DROP TABLE EventDefinition





if exists (select * from sysobjects
where id = object_id('EventDefinitionType'))
DROP TABLE EventDefinitionType

CREATE TABLE dbo.EventDefinitionType (

--EventDefinitionTypeID int IDENTITY (1,1) PRIMARY KEY NONCLUSTERED, --self
explanatory
EventDefinitionTypeUUID uniqueidentifier NOT NULL DEFAULT NEWID() PRIMARY
KEY NONCLUSTERED ,
EventDefinitionTypeName varchar(128) NOT NULL UNIQUE ,

CONSTRAINT edt_key UNIQUE ( EventDefinitionTypeUUID )
)





CREATE TABLE dbo.EventDefinition (

--EventDefinitionID int IDENTITY (1,1) PRIMARY KEY NONCLUSTERED, --self
explanatory
EventDefinitionUUID uniqueidentifier NOT NULL DEFAULT NEWID() PRIMARY KEY
NONCLUSTERED ,
EDTS timestamp null ,
EventDefinitionTypeUUID uniqueidentifier NOT NULL FOREIGN KEY
(EventDefinitionTypeUUID) REFERENCES
EventDefinitionType(EventDefinitionTypeUUID) DEFAULT
'00000000-0000-0000-0000-000000000000', --

EventDefinitionName varchar(128) NOT NULL UNIQUE ,
EventICalendarExpression varchar(1024) NULL , --NULL for manual entries


EventMasterStartTime varchar(24) NULL DEFAULT '00:00:00' ,
EventMasterEndTime varchar(24) NULL DEFAULT '00:00:00' ,


CreateDate datetime NOT NULL DEFAULT getDate(),
UpdateDate datetime NOT NULL DEFAULT getDate() ,

LastInstanceUpdateDate datetime NULL ,

RemoveDate datetime NULL ,



CONSTRAINT ed_key UNIQUE ( EventDefinitionUUID )
)


if exists (select * from sysobjects
where id = object_id('EventInstance'))
DROP TABLE EventInstance



CREATE TABLE dbo.EventInstance (

--EventInstanceID int IDENTITY (1,1) PRIMARY KEY NONCLUSTERED, --self
explanatory
EventInstanceUUID uniqueidentifier NOT NULL DEFAULT NEWID() PRIMARY KEY
NONCLUSTERED ,
EITS timestamp null ,

EventDefinitionUUID uniqueidentifier NOT NULL FOREIGN KEY
(EventDefinitionUUID) REFERENCES EventDefinition(EventDefinitionUUID), --

OccurenceDate datetime NOT NULL ,

EventInstanceStartTime varchar(24) NULL ,
EventInstanceEndTime varchar(24) NULL ,

Notes varchar(1024) NULL ,
ManualEntry bit NOT NULL DEFAULT 0 ,

CreateDate datetime NOT NULL DEFAULT getDate(),
UpdateDate datetime NOT NULL DEFAULT getDate() ,
RemoveDate datetime NULL ,


CONSTRAINT ei_key UNIQUE ( EventInstanceUUID )


)



GO

INSERT INTO dbo.EventDefinitionType ( EventDefinitionTypeUUID ,
EventDefinitionTypeName )
VALUES ( '00000000-0000-0000-0000-000000000000' , 'Unknown' )

INSERT INTO dbo.EventDefinitionType ( EventDefinitionTypeUUID ,
EventDefinitionTypeName )
VALUES ( '00000000-0000-0000-0000-000000000001' , 'No Rule' )

INSERT INTO dbo.EventDefinitionType ( EventDefinitionTypeUUID ,
EventDefinitionTypeName )
VALUES ( '00000000-0000-0000-0000-000000000002' , 'Simple Rule' )
INSERT INTO dbo.EventDefinitionType ( EventDefinitionTypeUUID ,
EventDefinitionTypeName )
VALUES ( '00000000-0000-0000-0000-000000000003' , 'Complex Rule' )

GO