Handling reoccurance appts in Scheduling application

Discussion in 'ASP .Net' started by Cirene, May 14, 2008.

  1. Cirene

    Cirene Guest

    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.
    Cirene, May 14, 2008
    #1
    1. Advertising

  2. Cirene

    sloan Guest

    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" <> wrote in message
    news:...
    > 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, May 14, 2008
    #2
    1. Advertising

  3. Cirene

    sloan Guest

    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" <> wrote in message
    news:...
    > 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, May 14, 2008
    #3
  4. Cirene

    Cirene Guest

    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" <> wrote in message
    news:...
    >
    > 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" <> wrote in message
    > news:...
    >> 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.
    >>

    >
    >
    Cirene, May 14, 2008
    #4
  5. Cirene

    sloan Guest

    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
    sloan, May 14, 2008
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. arcvonz

    Scheduling a .NET component

    arcvonz, Aug 17, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    1,658
  2. JIK
    Replies:
    0
    Views:
    655
  3. mirek
    Replies:
    3
    Views:
    522
    Chris Jackson
    Dec 17, 2003
  4. =?Utf-8?B?Sm9obg==?=

    Job Scheduling

    =?Utf-8?B?Sm9obg==?=, Apr 8, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    423
    Cowboy \(Gregory A. Beamer\) [MVP]
    Apr 8, 2004
  5. Jake
    Replies:
    2
    Views:
    158
    Chris Hohmann
    Aug 15, 2005
Loading...

Share This Page