Fog Creek Software
Discussion Board




Datamadel for scheduling

Hey.
I was wondering if there is some standard datamodel for calendar/scheduling typ apps.

The requirements are that there should be visual representation of time that can stretch infinitly into the future. Events have a start time and a stop time (or duration).
No two events can overlap.

Now, the display thing is done all in code of course. And the events could simply go into a table. But how does one do the colision/overlap control without having to scan for and calculate all possible collisions.

Im thinking the solution must either be in some SQL command I dont know about, or in the datamodel itself.

How is it normally done?

Eric DeBois
Tuesday, July 01, 2003

order the events by start time.

find the greatest start time less than pending event.
  if (previous.startTime + previousduration > newvent.start time)  new event is invalid.

find the least start time greater than new event.

if (newevent.start time + newevet.duration > next.start time)  the new even is invalid.

The SQL for the queries is failrly easy.  but the test logic inside your domain model.

Adam Young
Tuesday, July 01, 2003

DOH.

Thanks.

I diddnt realise that if every new insertion is checked against the closest two events, those will be the only two events you ever need to check against since only those two can possibly be in conflict.
I figured you hade to scan through the entire list, but that would only be needed if there was invalid entires in there to begin with.

Cheers

Eric DeBois
Tuesday, July 01, 2003

When you start allow for recurring events it becomes a pain in the ass.

anon
Tuesday, July 01, 2003

Well put anon. :)

Chris
Tuesday, July 01, 2003

Yep.

Probably what you want to do is have a template of events, apply those templates to a give time slice, and check for conflicts.

Your object model would have the following classes (or something close)

tempolate event: start date, end date, start time , duration, repeatrule.

repeatrule (maybe some time of enum patterns) can be daily, weekly, monthly, etc. whatever your app requires.

scheduled event:  template event, date.


It gets more complicated if you want an even to reur more often than daily (every hour on the half hour).

Adam Young
Tuesday, July 01, 2003


CREATE PROCEDURE CheckEvents
    @StartDate DateTime,
    @EndDate DateTime,
    @EventCount int  OUTPUT
AS
  SELECT @EventCount=COUNT(*)
  FROM Events
  WHERE StartDate<@EndDate
  AND EndDate>@StartDate

If @EventCount>0 then there's a collision.

(This is my favorite SQL trick in the world)

Philo 
 

Philo
Tuesday, July 01, 2003

Philo,

Shouldn't it be:

WHERE StartDate<@EndDate
OR EndDate>@StartDate <-- The OR not AND

Or am I misunderstanding? Because your one will only catch events full between the enddate and startdate? Forgive me if I am wrong.

Chris
Tuesday, July 01, 2003

Yep, you got it wrong. Try these out:


@Start----------------@End
                Start--------------------End


                @Start------------------@End
Start--------------------End


Philo

Philo
Tuesday, July 01, 2003

Ok then I see it now. Thanks for taking the time to clear that up for me Philo.

Chris
Tuesday, July 01, 2003

Definately going to file this away tip away for our actions section. Thanks.

Chris
Tuesday, July 01, 2003

I have written more collision routines then I care to admit.

It is strange,but it really is a very simple query as to find a collision. It is a simple sql query (as Philo points out).

As long as you check (don’t allow) collisions at insert time, then the problem is simple.

For some reason,  this booking problem seems to be difficult, but in fact is not a complex problem at all.

My first gen of reservation  software had all kinds of weird logic. Now, it is so much simpler.

I will suggest to store “ranges” for testing. (you seem to hint that is what you want anyway). So often, if a booking is for a week is needed, then many developers wrongly assume that you need to write out 7 records to some huge booking table.

I have had to work with those types of designs in the past...and it was a mess.

If you store ranges, then you can delete, or change the range with ease. If you store a reocrd for each day of the booking, you have to write tons of code just to move, or remove a booking.

As for some ideas for “repeating” events? Take a look at how your palm pilot does it....it will give you some good ideas.


Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com
http://www.attcanada.net/~kallal.msn

Albert D. Kallal
Wednesday, July 02, 2003

*  Recent Topics

*  Fog Creek Home