A neutral challenge.

[Database Administrator]

"Isaac Blank" wrote in message news:...
> "Bob Badour" wrote in message
> news:UYLKa.588$Sp2.78550140@mantis.golden.net...
> > "Peter Koch Larsen" wrote in message
> > news:61c84197.0306261140.6454f73c@posting.google.c om...
> > > "Bob Badour" wrote in message
> news:...
> > > [snip]
> > > >
> > > > Back to the scheduling problem you propose, what granularity of
> schedule
> did
> > > > you envision? I notice all the times are even hours. Is that just
> > > > incidental? Or do you propose a calendar to schedule events on an
> hourly
> > > > basis?
> > >
> > > Ideally, my belief is that the granularity should be arbitrarily small
> > > - this will make the solution applicable for other jinds of schedules
> > > as well. If you do choose a specific interval this should be allowed,
> > > but in that case I would expect some explanation as to why this
> > > specific interval was chosen.
> > >
> > > For the rest of your post allow me to return later.
> >
> > I don't think it makes sense to schedule rooms to the nanosecond. To the
> > quarter-hour is probably as great a granularity as anyone would ever
> really
> > use in practice and to the minute should provide a safe enough margin of
> > over engineering.
> >
> Ideally, the database layer should be able to handle any reasonable
> granularity and let the front end restrict it to whatever the policies are.

I explained above why 1 minute granularity is reasonable for
scheduling rooms. Why do you think the dbms should ignore systemic
constraints allowing applications to enforce them instead? That seems
to contradict almost every principle of good database management.

[Database Administrator]

Lauri Pietarinen wrote in message news:<3EFD52A3.C699C075@atbusiness.com>...
> Bob Badour wrote:
>
> > > I think using types for this purpose is a slight overkill. It would be
> > > easy to add a 'day of week' and
> > > a 'day of week in month' column into materialised calendar and just use
> > > them in selection criterias.
> >
> > This would complicate queries too much. If one wants to see the events on
> > thursday, one would have to query three or more columns instead of querying
> > a single interval column.
>
> OK, maybe I am a bit to used to thinking in SQL. How would your solution
> look like, using types?
>
> Lauri

I won't be able to provide a complete solution right away--partly
because I have more pressing tasks to complete and partly because I
have not fully developed my ideas regarding recasting intervals from
one type to another.

As a broad overview, however, the tuple describing a calendar event
would have a single interval attribute describing when the event
occurs. Since intervals are sets of points that need not be
contiguous, a single interval can describe an event occuring over
multiple days at the same time of day or even at different times of
day.

If one wants to query all of the events that occur on some day, one
selects an appropriate interval representing the day and returns all
events whose interval overlaps. In fact, the interval of interest need
not represent a day; it could just as easily represent a minute or a
year.

So, if we have an EVENT relation that has an OCCURS interval
describing when the event occurs and one wants to derive a relation of
those events occuring during an interval, i1, as well as when the
event occurs in the interval:

WITH ( EVENT WHERE OCCURS OVERLAPS i1 ) AS T1,
( EXTEND T1 ADD ( OCCURS INTERSECT i1 ) AS OCCURS_IN_INTERVAL ) AS
T2:
PACK T2 ON OCCURS_IN_INTERVAL

As for types, consider an ordered supertype P with an ordered subtype
C. (I chose P for parent and C for child.)

While C is a point type, it seems to me that any value of C defines at
least three intervals of P. Let's consider a value of C and call it
C0. Because C is a subtype of P, C0 is also a value of P. If one
considers the longest continuous interval of P that begins at C0 and
contains no other values of C, C0 defines one interval of P.
Similarly, C0 defines a longest continuous interval of P ending at C0.
Finally, because C0 is a value of P, it defines an interval beginning
and ending at C0.

It might make sense to have generic operations that cast a value of a
subtype to an interval of the supertype. Are the intervals defined
above equally important? Does C0 define any other important intervals
of P? What would we call these generic cast operations? Then again, it
might not make sense in the first place.

Assuming it does make sense, since INTERVAL_C selects a type
comprising a set of C values, if we have generic operations to cast C
values to INTERVAL_P values, it is possible to define operations to
cast INTERVAL_C values to INTERVAL_P values by taking the union of the
INTERVAL_P values derived by casting each of the C values in the
INTERVAL_C value to an INTERVAL_P value.

So, for instance, if we have a TIME type with a DAY subtype, one could
cast a DAY value to an interval of TIME representing the DAY.
Likewise, one could cast an interval of DAY to an interval of TIME
representing the days in the interval.

Then again, I might be wrong to consider DAY a subtype of TIME in the
first place. Perhaps DAY is a different type entirely that simply
defines an operation resulting in an interval of TIME representing the
DAY.

Regardless whether the operations are generic or explicit, one might
precede the query above with any of the following:

i1 := INTERVAL_TIME(DAY('2003/06/26'));
i1 := INTERVAL_TIME(YEAR(2003));
i1 := INTERVAL_TIME(MINUTE('2003/06/28 15:25'));
i1 := INTERVAL_TIME(INTERVAL_MINUTE(['2003/06/28 15:30','2003/06/28
15:45']));
i1 := INTERVAL_TIME(INTERVAL_THIRD_THURSDAY(['2003/06/19','2003/12/18']);
i1 := INTERVAL_TIME(['2003/06/28 15:30:12.000','2003/06/28
15:32:16.500']);

(I assume above that TIME resolves to milliseconds.)

Regards,
Bob

[Database Administrator]

"Bob Badour" wrote in message
news:cd3b3cf.0306280919.7a6ab963@posting.google.co m...
> "Isaac Blank" wrote in message
news:...
> > "Bob Badour" wrote in message
> > news:UYLKa.588$Sp2.78550140@mantis.golden.net...
> > > "Peter Koch Larsen" wrote in message
> > > news:61c84197.0306261140.6454f73c@posting.google.c om...

> > > > Ideally, my belief is that the granularity should be arbitrarily
small
> > > > - this will make the solution applicable for other jinds of
schedules
> > > > as well. If you do choose a specific interval this should be
allowed,
> > > > but in that case I would expect some explanation as to why this
> > > > specific interval was chosen.
> > > >
> > >
> > > I don't think it makes sense to schedule rooms to the nanosecond. To
the
> > > quarter-hour is probably as great a granularity as anyone would ever
> > really
> > > use in practice and to the minute should provide a safe enough margin
of
> > > over engineering.
> > >
> > Ideally, the database layer should be able to handle any reasonable
> > granularity and let the front end restrict it to whatever the policies
are.
>
> I explained above why 1 minute granularity is reasonable for
> scheduling rooms. Why do you think the dbms should ignore systemic
> constraints allowing applications to enforce them instead? That seems
> to contradict almost every principle of good database management.

For one thing, Peter's reasoning does have its merit - you could easily
re-use the same desiign and code for another scheduling application.
Also, imposing this limitation would probably make the database design
and code more complex than it should be. I can easily come up with a simple
and fast database design using native SQL types for timestamps, but
enforcing the granularity by means of appropriate database schema and
constarints will be a little bit challenging. So if someone wanted me to
enforce the granularity of 1 minute with the database native granularity,
say, 1 second, I would rather enforce this by checking parameters of the
stored procedures used to communicate between the front ent part of the
application and the database.

[Database Administrator]

"Isaac Blank" wrote in message
news:NIYLa.149$6N1.23088370@newssvr21.news.prodigy .com...
> "Bob Badour" wrote in message
> news:cd3b3cf.0306280919.7a6ab963@posting.google.co m...
> > "Isaac Blank" wrote in message
> news:...
> > > "Bob Badour" wrote in message
> > > news:UYLKa.588$Sp2.78550140@mantis.golden.net...
> > > > "Peter Koch Larsen" wrote in message
> > > > news:61c84197.0306261140.6454f73c@posting.google.c om...
>
> > > > > Ideally, my belief is that the granularity should be arbitrarily
> small
> > > > > - this will make the solution applicable for other jinds of
> schedules
> > > > > as well. If you do choose a specific interval this should be
> allowed,
> > > > > but in that case I would expect some explanation as to why this
> > > > > specific interval was chosen.
> > > > >
> > > >
> > > > I don't think it makes sense to schedule rooms to the nanosecond. To
> the
> > > > quarter-hour is probably as great a granularity as anyone would ever
> > > really
> > > > use in practice and to the minute should provide a safe enough
margin
> of
> > > > over engineering.
> > > >
> > > Ideally, the database layer should be able to handle any reasonable
> > > granularity and let the front end restrict it to whatever the policies
> are.
> >
> > I explained above why 1 minute granularity is reasonable for
> > scheduling rooms. Why do you think the dbms should ignore systemic
> > constraints allowing applications to enforce them instead? That seems
> > to contradict almost every principle of good database management.
>
> For one thing, Peter's reasoning does have its merit - you could
easily
> re-use the same desiign and code for another scheduling application.

Another scheduling application would impose a different domain constraint.


> Also, imposing this limitation would probably make the database design
> and code more complex than it should be.

I disagree. I see no reason to believe it would have any effect on
complexity; although, it would certainly have an effect on integrity.


> I can easily come up with a simple
> and fast database design using native SQL types for timestamps, but
> enforcing the granularity by means of appropriate database schema and
> constarints will be a little bit challenging.

I suggest this reveals a flaw (or flaws) in SQL. The schedule granularity is
simply a domain constraint.