"Isaac Blank" <izblank (AT) yahoo (DOT) com> wrote

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.

Lauri Pietarinen <lauri.pietarinen (AT) atbusiness (DOT) com> wrote

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

"Bob Badour" <bbadour (AT) golden (DOT) net> wrote

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.

"Isaac Blank" <izblank (AT) yahoo (DOT) com> wrote

Another scheduling application would impose a different domain constraint.


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 suggest this reveals a flaw (or flaws) in SQL. The schedule granularity is
simply a domain constraint.