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 "RecurrencePare nt" column in their Appts table. But
didn't understnad the implementation. A sample RecurrenceRule data was:
DTSTART:2007033 0T063000Z
DTEND:20070330T 073000Z
RRULE:FREQ=DAIL Y;INTERVAL=1;UN TIL=20070406T21 0000Z;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]

Re: Handling reoccurance appts in Scheduling application


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.



"Cirene" <cirene@nowhere .comwrote in message
news:eDqxaTftIH A.3780@TK2MSFTN GP03.phx.gbl...

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 "RecurrencePare nt" column in their Appts table.
But didn't understnad the implementation. A sample RecurrenceRule data
was:
DTSTART:2007033 0T063000Z
DTEND:20070330T 073000Z
RRULE:FREQ=DAIL Y;INTERVAL=1;UN TIL=20070406T21 0000Z;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]

Re: Handling reoccurance appts in Scheduling application


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...becaus e 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" <cirene@nowhere .comwrote in message
news:eDqxaTftIH A.3780@TK2MSFTN GP03.phx.gbl...

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 "RecurrencePare nt" column in their Appts table.
But didn't understnad the implementation. A sample RecurrenceRule data
was:
DTSTART:2007033 0T063000Z
DTEND:20070330T 073000Z
RRULE:FREQ=DAIL Y;INTERVAL=1;UN TIL=20070406T21 0000Z;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.
>

[Cirene]

Re: Handling reoccurance appts in Scheduling application

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" <sloan@ipass.ne twrote in message
news:e0BQKkftIH A.5268@TK2MSFTN GP06.phx.gbl...

>
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...becaus e 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" <cirene@nowhere .comwrote in message
news:eDqxaTftIH A.3780@TK2MSFTN GP03.phx.gbl...

>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 "RecurrencePare nt" column in their Appts table.
>But didn't understnad the implementation. A sample RecurrenceRule data
>was:
>DTSTART:200703 30T063000Z
>DTEND:20070330 T073000Z
>RRULE:FREQ=DAI LY;INTERVAL=1;U NTIL=20070406T2 10000Z;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]

Re: Handling reoccurance appts in Scheduling application


I use the EventInstance.R emoveDate as the "IsSoftDele te" 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
--EventDefinition ID ,
EventDefinition UUID ,
CAST(EDTS as int) as EDTS ,
EventDefinition TypeUUID ,
EventDefinition Name ,
EventICalendarE xpression ,
EventMasterStar tTime ,
EventMasterEndT ime ,
CreateDate ,
UpdateDate ,
LastInstanceUpd ateDate ,
RemoveDate ,

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

From
dbo.EventDefini tion 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('Even tInstance'))
DROP TABLE EventInstance

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





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

CREATE TABLE dbo.EventDefini tionType (

--EventDefinition TypeID int IDENTITY (1,1) PRIMARY KEY NONCLUSTERED, --self
explanatory
EventDefinition TypeUUID uniqueidentifie r NOT NULL DEFAULT NEWID() PRIMARY
KEY NONCLUSTERED ,
EventDefinition TypeName varchar(128) NOT NULL UNIQUE ,

CONSTRAINT edt_key UNIQUE ( EventDefinition TypeUUID )
)





CREATE TABLE dbo.EventDefini tion (

--EventDefinition ID int IDENTITY (1,1) PRIMARY KEY NONCLUSTERED, --self
explanatory
EventDefinition UUID uniqueidentifie r NOT NULL DEFAULT NEWID() PRIMARY KEY
NONCLUSTERED ,
EDTS timestamp null ,
EventDefinition TypeUUID uniqueidentifie r NOT NULL FOREIGN KEY
(EventDefinitio nTypeUUID) REFERENCES
EventDefinition Type(EventDefin itionTypeUUID) DEFAULT
'00000000-0000-0000-0000-000000000000', --

EventDefinition Name varchar(128) NOT NULL UNIQUE ,
EventICalendarE xpression varchar(1024) NULL , --NULL for manual entries


EventMasterStar tTime varchar(24) NULL DEFAULT '00:00:00' ,
EventMasterEndT ime varchar(24) NULL DEFAULT '00:00:00' ,


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

LastInstanceUpd ateDate datetime NULL ,

RemoveDate datetime NULL ,



CONSTRAINT ed_key UNIQUE ( EventDefinition UUID )
)


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



CREATE TABLE dbo.EventInstan ce (

--EventInstanceID int IDENTITY (1,1) PRIMARY KEY NONCLUSTERED, --self
explanatory
EventInstanceUU ID uniqueidentifie r NOT NULL DEFAULT NEWID() PRIMARY KEY
NONCLUSTERED ,
EITS timestamp null ,

EventDefinition UUID uniqueidentifie r NOT NULL FOREIGN KEY
(EventDefinitio nUUID) REFERENCES EventDefinition (EventDefinitio nUUID), --

OccurenceDate datetime NOT NULL ,

EventInstanceSt artTime varchar(24) NULL ,
EventInstanceEn dTime 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 ( EventInstanceUU ID )


)



GO

INSERT INTO dbo.EventDefini tionType ( EventDefinition TypeUUID ,
EventDefinition TypeName )
VALUES ( '00000000-0000-0000-0000-000000000000' , 'Unknown' )

INSERT INTO dbo.EventDefini tionType ( EventDefinition TypeUUID ,
EventDefinition TypeName )
VALUES ( '00000000-0000-0000-0000-000000000001' , 'No Rule' )

INSERT INTO dbo.EventDefini tionType ( EventDefinition TypeUUID ,
EventDefinition TypeName )
VALUES ( '00000000-0000-0000-0000-000000000002' , 'Simple Rule' )
INSERT INTO dbo.EventDefini tionType ( EventDefinition TypeUUID ,
EventDefinition TypeName )
VALUES ( '00000000-0000-0000-0000-000000000003' , 'Complex Rule' )

GO