Impossible Database Design?
Nikolai Onken
I was just wondering if it is possible to create a database design
where you can have
1. Infinite repeating events like "every year, every last monday,..."
2. The possibility to test whether events overlap other events
I would say it is not possible since I would need to run an infinite
query to catch a singular event..
Any ideas?
What would be the 'nearest' approach?
Cheers,
Nikolai
Bob Badour
The word "infinite" renders the answer trivial when dealing with finite
computers. Otherwise, I suggest you read the temporal database book by
Date, Darwen and Lorenzos. Or Lorenzos' other work.
Nikolai Onken
entries...
Only that would mean that they can't support overlapping of events at
any time (does this event overlap another one at some point..)...
So I guess I have to fundamentally decide whether I want to support a
feature like "you already have a gig in 3056" or whether "I have a
something every january until forever"..
hmm...
Bob Badour
> Well though there are calendar applications which support 'infinite'
> entries...
Not really. I have yet to see one that uses an infinite representation
for time, which means they are inherently finite.
> Only that would mean that they can't support overlapping of events at
> any time (does this event overlap another one at some point..)...
> So I guess I have to fundamentally decide whether I want to support a
> feature like "you already have a gig in 3056" or whether "I have a
> something every january until forever"..
> hmm...
Why would you stop at 3056? Many systems should not encounter problems
until 9999/12/31 at the minimum.
I reiterate my suggestion to read the existing literature on temporal
databases. I assume you could not possibly have obtained a copy of the
Date/Darwen/Lorenzos book and read it in the seven minutes it took you
to reply.
Nikolai Onken
>Why would you stop at 3056?
I was thinking: I want to add a new event with no defined ending date
and a pattern like 'every 1st monday and 4th tuesday a month'..
Then the program should tell me 'You already have an event on the 16th
of february 3056'.
I know that all this isn't possible but I am just curious what the
closest solution would be.
> I assume you could not possibly have obtained a copy of the
> Date/Darwen/Lorenzos book and read it in the seven minutes
> it took you
Indeed I haven't read the book yet :) I am having trouble finding the
it.
Are you referring to 'Databases, Types and the Relational Model' by C.
J. Date, Hugh Darwen?
Thank you for your replies!
Regards,
Nikolai
to reply.
Bob Badour
> Bob Badour wrote:
>
>>Why would you stop at 3056?
>
> I was thinking: I want to add a new event with no defined ending date
> and a pattern like 'every 1st monday and 4th tuesday a month'..
> Then the program should tell me 'You already have an event on the 16th
> of february 3056'.
> I know that all this isn't possible but I am just curious what the
> closest solution would be.
The closest solution would be very well described in the
Date/Darwen/Lorentzos book. I don't know why you would think it
impossible if you simply altered your requirement to accept large finite
ranges.
>>I assume you could not possibly have obtained a copy of the
>>Date/Darwen/Lorenzos book and read it in the seven minutes
>>it took you
>
> Indeed I haven't read the book yet :) I am having trouble finding the
> it.
> Are you referring to 'Databases, Types and the Relational Model' by C.
> J. Date, Hugh Darwen?
No, if you look on Fabian Pascal's books page,
http://www.dbdebunk.com/books.html , it is the one just below that:
/Temporal Data and The Relational Model/
Oh, and I see I have been misspelling Lorentzos. My apologies to
everyone and particularly to the author.
> Thank you for your replies!
> Regards,
You are very welcome.
Kenneth Downs
To answer part of your question, overlapping events can be handled with a
constraint.
In fact, you can can consider a conventional unique constraint to be a
simplified case of a generalized exclusion constraint. The more general
case can provide various rules that determine which rows exclude others, in
the case of the unique constraint it is simple equality, in the case of
time it is interval overlap.
--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)
Nikolai Onken
days of internet and newsgroups :) ...
Kenneth Downs wrote:
> The more general case can provide various rules that
> determine which rows exclude others, in the case of the unique
> constraint it is simple equality, in the case of time it is interval overlap.
Wouldn't the problem occur when I am looking for equality on an
'infinite' or really long scale (do 2 functions cross each other at any
point?) rather than interval overlap?
Kenneth Downs
If i understand your question, there is no problem. Two intervals are
defined by their endpoints. Simple comparison of their endpoints
determines if the intervals overlap.
Nikolai Onken
>Simple comparison of their endpoints determines if the intervals overlap.
Yeah, so if I don't want endpoints (forever repeating events) I
wouldn't be able to compare events. So I guess I have to decide whether
I need functionality like 'do two repeating events overlap at any time
they exist' or 'repeat every year until forever'.
I am just curious to whether there is a middle way supporting both
requirements (Though it doesn't make sense to me :) )
Bob Badour
If one accepts the limitation of a finite representation, which in the
end one has no choice over, then there will always be a finite endpoint
at the extreme end of the representation. For a time representation,
that might be 999999/12/31 or some other large finite value.
Marshall
>
> If one accepts the limitation of a finite representation, which in the
> end one has no choice over, then there will always be a finite endpoint
> at the extreme end of the representation. For a time representation,
> that might be 999999/12/31 or some other large finite value.
Trivia:
The java Date class uses a 64 bit number, interpreted as
miliseconds since Jan. 1, 1970, to capture time/date
values. Amusingly, sometimes people get upset with this
idea because, you know, time is "infinite." If I did the
math correctly, the Date class can "only" represent dates
until the year 292280995. Pointing that out usually quiets
them down.
Marshall
David Cressey
"Marshall" <marshal...@gmail.com> wrote in message
news:1147811975....@j33g2000cwa.googlegroups.com...
And you know, for sure, that around October 292280994, there's going to be
one hell of a market for people to rework code!
Nikolai Onken
I probably have not enough experience to really start arguing but I am
still curious whether there actually is a way to set up a db which
would
1. allow infinite events (thats what the client wants or it should at
least I should make him believe he got it)
2. allow the calculation whether ('infinitly') repeating events overlap
(Is required because I have to see whether a repeating event conflicts
with another one when adding)
3. Not have lots of entries per year (say you have a university with
5000 students having 12 lessons 40 weeks a year), and is performant
4. (allow exceptions: repeat every monday except monday 15th of May
2006)
> Pointing that out usually quiets them down.
Pointing out the limits of the computer definitely moves the whole
issue into perspective - though the problem remains the same and I am
still curious as to how a workable solution would look like..
It would be quite simple to write a calendar supporting true infinite
events by just leaving the end date empty but as far as I can think of
it won't be possible anymore to calculate overlaping events..
Anyways I apprechiate your posts and if you have any links or working
examples I would be more than happy to try them out..
Nikolai
Bob Badour
> Hehe.. thats absolutely true!!
> I probably have not enough experience to really start arguing but I am
> still curious whether there actually is a way to set up a db which
> would
>
> 1. allow infinite events (thats what the client wants or it should at
> least I should make him believe he got it)
Make believe is very easy. Let him specify the open ended-range with
'...', and just be aware that that range is actually going to end at
some finite time.
> 2. allow the calculation whether ('infinitly') repeating events overlap
> (Is required because I have to see whether a repeating event conflicts
> with another one when adding)
The only way they will NOT overlap is if the period of one is an
integral multiple of the other. Otherwise, they are guaranteed to
overlap an infinite number of times (assuming eternity).
Think harmonics.
> 3. Not have lots of entries per year (say you have a university with
> 5000 students having 12 lessons 40 weeks a year), and is performant
Define 'lots'.
> 4. (allow exceptions: repeat every monday except monday 15th of May
> 2006)
See the book.
>>Pointing that out usually quiets them down.
>
>
> Pointing out the limits of the computer definitely moves the whole
> issue into perspective - though the problem remains the same and I am
> still curious as to how a workable solution would look like..
See the book.
> It would be quite simple to write a calendar supporting true infinite
> events by just leaving the end date empty but as far as I can think of
> it won't be possible anymore to calculate overlaping events..
You have not been listening. Unless you have an infinite computer, you
cannot create an infinite calendar.
Marshall
>
> > It would be quite simple to write a calendar supporting true infinite
> > events by just leaving the end date empty but as far as I can think of
> > it won't be possible anymore to calculate overlaping events..
>
> You have not been listening. Unless you have an infinite computer, you
> cannot create an infinite calendar.
Seconded. Why not state a more realistic requirement, such as:
support events up to 1000 years in the future? 1000 is much,
much less than infinite, and so has the advantage of being actually
possible.
Also remember Scott McNealy's comment: "Most software
has the shelf life of a banana." Your software probably won't
still be in use in even 10 years.
Marshall
mAsterdam
Also remember that knowing that people actually built
software with that assumption led to the millennium paranoia.
Arbitrary limits where none are logically
necessary have quite a track-record (640Kb anyone?).
Nikolai Onken
first :) since my question of how such a DB structure looks like is
still out there...
> Define 'lots'.
Say you have a university with 10.000 (its a real case) students, each
having 4 lessons a week 35 weeks a year - 1.400.000 records a year only
for scheduling and then we also have to add tracking information for
each lesson... (is that actually a lot?)
> You have not been listening. Unless you have an infinite computer,
> you cannot create an infinite calendar.
Why not? Wouldn't it depend on the requirements? As long as you don't
need to calculate whether events overlap in the future you could make a
calendar like
datetime_start
datetime_end
repeat_pattern
series_id
And fill in
2005-06-12 12:00:00
"" (never ending)
"5" (every 5 days)
""
and then say: today may 17th, you calculate the days between today and
2005-06-12 and then and do a days_between%5 to see whether today falls
into this pattern (if days_between%5 == 0 you display the event in the
calendar or not).
> Your software probably won't
> still be in use in even 10 years.
I really can't tell... at least I want to make it easy for the
following developers to migrate a huge database :)
Bob Badour
> Thanks for all the replies... Probably I really should read the book
> first :) since my question of how such a DB structure looks like is
> still out there...
>
>>Define 'lots'.
>
> Say you have a university with 10.000 (its a real case) students, each
> having 4 lessons a week 35 weeks a year - 1.400.000 records a year only
> for scheduling and then we also have to add tracking information for
> each lesson... (is that actually a lot?)
Not really.
>>You have not been listening. Unless you have an infinite computer,
>>you cannot create an infinite calendar.
>
> Why not?
Because an infinite calendar would require infinite memory to represent
the largest date.
Wouldn't it depend on the requirements?
Nope. Not at all. It's inherent in the infinite.
As long as you don't
> need to calculate whether events overlap in the future you could make a
> calendar like
[Finite calendar example snipped]
Nikolai Onken
Hmm, so probably then I should go for every event gets stored as a
single entry in the db...
> Because an infinite calendar would require infinite memory to represent
> the largest date.
I prefer to tell a client though that "something doesn't work because
computers can't yet handle it" rather than "we have to reprogramm
because the concept was not proof when developed" (I know that it is
really irrelevant since my client won't go ask me in 243678 why
something doesn't work but I am just curious to what the best solution
is)
Would you argue that the calendar example I gave is not a working
infinite calendar (with some constraints, querying into the future is
only possible on a defined scale)? It still would work like a charm in
2432654376 :)
x
"Nikolai Onken" <nikola...@gmail.com> wrote in message
news:1147793576.6...@j73g2000cwa.googlegroups.com...
It's easy.
Ask your client what events will want to store in the computer and what is
the best way for him to input that data.
Work from there.
Nikolai Onken
> the best way for him to input that data.
Hehe :) the client wants:
- 'infinitely repeating dates' (whatever thats supposed to mean in the
end)
- being able to predict whether courses overlap
- having exceptions to series (every monday except xxx)
My conclusion would be that probably the best way would be to simply
not support queries for predicting overlaps for dates without ending
point..
This probably makes most sense anyways since the only type of never
ending event is something like a birthday or memorial or so.. and there
you probably don't really need to know whether it overlaps with another
never ending event..
x
> > Ask your client what events will want to store in the computer and what
is
> > the best way for him to input that data.
>
> Hehe :) the client wants:
> - 'infinitely repeating dates' (whatever thats supposed to mean in the
> end)
How he would input that data into the computer ?
Have you asked him ?
> - being able to predict whether courses overlap
> - having exceptions to series (every monday except xxx)
So what. "My client is my master."
Ask him to provide some examples.
My guess that you will only have to solve some equations.
The method you will use will depend on the specific case you have.
Experiment with several approaches and ask your client which one he will
prefer and if he has the money to pay you for implementing that specific
solution.
> My conclusion would be that probably the best way would be to simply
> not support queries for predicting overlaps for dates without ending
> point.
See above.
> This probably makes most sense anyways since the only type of never
> ending event is something like a birthday or memorial or so.. and there
> you probably don't really need to know whether it overlaps with another
> never ending event..
Nothing is never ending. Everything is transformation. :-)
Nikolai Onken
> Have you asked him ?
Basically like Outlook or so... but with the adding of a warning if an
event overlaps
> Nothing is never ending. Everything is transformation. :-)
If databases will be able to handle transformation we're all set :)
x
> > Nothing is never ending. Everything is transformation. :-)
> If databases will be able to handle transformation we're all set :)
Exactly. Some steps are already done.
Nikolai Onken
Regards,
Nikolai
-CELKO-
1. Infinite repeating events like "every year, every last monday,..."
<<
Create a view which uses the CURRENT_DATE to get the year and to form
a window of (-x, +x) years around it. I use this trick for a table of
5 second intervals in a day.
>> 2. The possibility to test whether events overlap other events <<
The usual way to do that is see if a calendar date falls between the
start and end dates of more than one event. But you already know that
a Monday event will not overlap a Tuesday event, July event will not
overlap a June event, etc.
-CELKO-
J. Date, Hugh Darwen? <<
No, the book Bob refers to is from Morgan-Kaufmann and has gone out of
print. It is not really very useful for a working programmer, since
Date invented a language that not implemented for the book. It also
uses a "Chronons" (discrete points of time) model for temporal data,
which is rejected by virtually all of the temporal database academics
because it fails to model time as a continuum.
A better and cheaper cholice would be to download the PDF book by Rick
Snodgrass at the University of Arizona website. Rick has been doing
temporal RDBMS work for over 20+ years and has actual SQL code in the
major dialects in his book, as well as accepted theoretical basis.
Nikolai Onken
I'll also have a look at the PDF of Rock Snodgrass!
Regards,
Nikolai
-CELKO-
You can use a NULL end_date for an "eternity marker" then "COALESCE
(end_date, CURRENT_DATE)" is a VIEW or queries for the current state of
affairs. For longer term, use something like "COALESCE (end_date,
(SELECT MAX(end_date FROM Calendar))".
You do not have to generate all the dates (which can only go to
9999-12-31 in ISO Standards), but just a subset of them as needed. The
days-of-the week cycle is every 19 years (i Think) so you can use that
fact to set your window.
Nikolai Onken
requiring a time window...
> You do not have to generate all the dates
How do you mean exactly? Using one table for Pattern/Repetition
definitions and one for the actual generated single events?
Then when and how would you trigger the generation of events? (Thats
the easier part though :) )
Regards,
Nikolai
David Cressey
"x" <x...@not-exists.org> wrote in message news:e4ep0i$v22$1...@emma.aioe.org...
>
> "Nikolai Onken" <nikola...@gmail.com> wrote in message
> news:1147855888.0...@j55g2000cwa.googlegroups.com...
> > > Ask your client what events will want to store in the computer and
what
> is
> > > the best way for him to input that data.
> >
> > Hehe :) the client wants:
> > - 'infinitely repeating dates' (whatever thats supposed to mean in the
> > end)
>
> How he would input that data into the computer ?
> Have you asked him ?
Simple. Join a newsgroup. Start an open ended discussion. Sooner or later
the infinite number of participants will type in all the data, in addition
to all of Shakespeare's works, etc. Now you just have to weed out the
inputs that are not relevant.
Nikolai Onken
Bob Badour
>>Not really.
>
> Hmm, so probably then I should go for every event gets stored as a
> single entry in the db...
>
>
>>Because an infinite calendar would require infinite memory to represent
>>the largest date.
>
> I prefer to tell a client though that "something doesn't work because
> computers can't yet handle it" rather than "we have to reprogramm
> because the concept was not proof when developed" (I know that it is
> really irrelevant since my client won't go ask me in 243678 why
> something doesn't work but I am just curious to what the best solution
> is)
Since the requirement is a figment of your imagination, you would do
better by telling the client: "Yes, I can deliver what you need."
> Would you argue that the calendar example I gave is not a working
> infinite calendar (with some constraints, querying into the future is
> only possible on a defined scale)? It still would work like a charm in
> 2432654376 :)
Apparently, you lack comprehension of the terms 'finite' and 'infinite'.
I suggest you ponder the difference between them and re-read what I
already posted.
Bob Badour
> Or use a pen
A pen with infinite ink would be difficult to carry.
Nikolai Onken
> I suggest you ponder the difference between them and re-read what I
> already posted.
No I don't think so, there are only different ways to represent finity
and infinity in a database - its not about 64bit, or capacity or
computer power (it is when starting to calculate with the data but that
abviously isn't such a big deal since calendars usually don't need to
calculate more than say 3 years in advance).
It seems that If I go for the way where I set the date_end to NULL when
having infinite events then I compromise ability to calculate for
example overlapping events appearing in the infinite future (though i
could calculate overlaps in a timewindow).
Or I store events as single entries in a db, so I compromise 'infinity'
but can calculate overlapping events in the future... Funny, so both
solutions are the same ? :)
Anyways - I don't want to tire you guys and really apprechiate your
replies... Is this such a common problem? I can't find so many sites
discussing this issues though there are lots of calendar
applications...
Regards,
Nikolai
Volker Hetzer
> Hehe.. thats absolutely true!!
> I probably have not enough experience to really start arguing but I am
> still curious whether there actually is a way to set up a db which
> would
>
> 1. allow infinite events (thats what the client wants or it should at
> least I should make him believe he got it)
Most pda calendars somewhere at 2100.
> 2. allow the calculation whether ('infinitly') repeating events overlap
> (Is required because I have to see whether a repeating event conflicts
> with another one when adding)
No idea.
> 3. Not have lots of entries per year (say you have a university with
> 5000 students having 12 lessons 40 weeks a year), and is performant
Databases have no problem at all with such dwarfish amounts of data.
How long do you expect your students to stay enrolled?
> 4. (allow exceptions: repeat every monday except monday 15th of May
> 2006)
> Pointing out the limits of the computer definitely moves the whole
> issue into perspective - though the problem remains the same and I am
> still curious as to how a workable solution would look like..
You can, of course, describe an entity of arbitrary complexity.
You can leave off the end date, have a list of exceptions and so on
and do everything in the client. However, at this point you should
ask yourself what the point of a database is.
If I was you I'd first ask the customer how long he expects that
application to live. If he's realistic, he'll say something in the range
of five to ten years.
Then I would convince him that it's much more important to have a
migration path to the next system than to allow some stupid infinities
inside.
Which in turn would lead to a design where the appointment is described
correctly, infinity and all and individual elements of a series are generated
from that description up to 2100 a.d. or so.
Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.
Volker Hetzer
> Thanks for all the replies... Probably I really should read the book
> first :) since my question of how such a DB structure looks like is
> still out there...
>
>> Define 'lots'.
>
> Say you have a university with 10.000 (its a real case) students, each
> having 4 lessons a week 35 weeks a year - 1.400.000 records a year only
> for scheduling and then we also have to add tracking information for
> each lesson... (is that actually a lot?)
No need for partitioning or other things databases use for handling
really large amounts of data.
Do you have *any* idea at all about the amount of data amazon or ebay
handle each day? Or the Lufthansa booking system?
Volker Hetzer
>> How he would input that data into the computer ?
>> Have you asked him ?
>
> Basically like Outlook or so... but with the adding of a warning if an
> event overlaps
Was that a problem for him til now?
Nikolai Onken
thank you for your reply!! Thats what I was wondering! How long could
Outlook actually run..
I think the best solution for my case would be to have three tables:
events:
datetime_start
datetime_end
name
description
other_fields
event_exceptions:
datetime_start
datetime_end
replacingperiod_datetime_start
replacingperiod_datetime_end
name
description
other_fields
event_rules:
datetime_start
datetime_end (if NULL = never ending)
repeat_pattern
name
description
other_fields
Then I write the data into the events table according to repeat_pattern
and event_exceptions...
This at the same time will enable me to run queries on future events
and have some sort of support for infinite repeating events.
Would anyone concider this a workable design for a calendar?
The repeat pattern could then easily be using CRON Syntax or some user
defined syntax which will be executed by the application or by the
database if it supports events...
Regards,
Nikolai
Marshall
>
> It also
> uses a "Chronons" (discrete points of time) model for temporal data,
> which is rejected by virtually all of the temporal database academics
> because it fails to model time as a continuum.
I don't see the problem. I am fairly confident that computers cannot
fully model a continuum, because of the difficulties with infinity.
The real numbers form a continuum--there are an infinite number of
them between 0 and 1. I note that, despite the importance of real
numbers, we use instead floats: finite, discrete points on the number
line. It works reasonably well, and I'm not aware that there's any way
to do it better. (Besides adding more precision.)
As I mentioned earlier, Java's Date class (irony?) uses a 64 bit
long as milliseconds, giving it millisecond resolution, and the
ability to represent dates crazy-far into the future. But we would
have to describe it as "discrete points of time." Specifically
milliseconds.
Marshall
Nikolai Onken
Bob Badour
Whether it is a workable design will be a direct consequence of the full
set of requirements -- the vast majority of which you have omitted.
Anyone who gives detailed or emphatic opinions on the merits of the
design above is a crank.
Bob Badour
>>Apparently, you lack comprehension of the terms 'finite' and 'infinite'.
>>I suggest you ponder the difference between them and re-read what I
>>already posted.
>
> No I don't think so, there are only different ways to represent finity
> and infinity in a database - its not about 64bit, or capacity or
> computer power (it is when starting to calculate with the data but that
> abviously isn't such a big deal since calendars usually don't need to
> calculate more than say 3 years in advance).
With all due respect, inifinite is about capacity and the impossibility
of representing each of an infinite set of values in a finite space.
I reiterate what I said above: You lack comprehenseion of the terms
'finite' and 'infinite'. I suggest you ponder the difference between
them and re-read what I already posted.
Plonk.
Bob Badour
> -CELKO- wrote:
>
>>It also
>>uses a "Chronons" (discrete points of time) model for temporal data,
>>which is rejected by virtually all of the temporal database academics
>>because it fails to model time as a continuum.
Can you think of any better evidence that Joe is a self-aggrandizing
ignorant who wouldn't recognize logic if it bit him on the ass?
> I don't see the problem. I am fairly confident that computers cannot
> fully model a continuum, because of the difficulties with infinity.
> The real numbers form a continuum--there are an infinite number of
> them between 0 and 1. I note that, despite the importance of real
> numbers, we use instead floats: finite, discrete points on the number
> line. It works reasonably well, and I'm not aware that there's any way
> to do it better. (Besides adding more precision.)
At best, computers use 0.000000000000000000000000000000000000000000... %
of the set of rational numbers, which are themselves
0.000000000000000000000000000000000000.... % of the reals.
Nikolai Onken
> design above is a crank.
Hmm, why? Didn't I already state the set of requirements?
1. Repeating events without defined ending
2. Querying whether events overlap
3. Support of repeating patterns
An calendar entry could be a lesson, holiday, a graduation party, a
semester, a birthday, a always repeating memorial,...
Weird..
I think that this design would support some sort of infinitely
repeating events (limited on a time window though), would support
queries over events in the future and would allow a pretty freely
definable pattern syntax (depending how the software handles it)
Regards,
Nikolai
Bob Badour
>>Anyone who gives detailed or emphatic opinions on the merits of the
>>design above is a crank.
>
>
> Hmm, why? Didn't I already state the set of requirements?
At best, you stated a tiny proper subset of the requirements for what
you are doing.
As I said, anyone who gives you detailed design advice online is a crank.
JayDee
Nikolai Onken wrote:
> > Anyone who gives detailed or emphatic opinions on the merits of the
> > design above is a crank.
>
> Hmm, why? Didn't I already state the set of requirements?
>
> 1. Repeating events without defined ending
> 2. Querying whether events overlap
> 3. Support of repeating patterns
Well, I'm not sure about what you've asked for. From the thread's
subject, one would guess that you really don't want a database. After
all, do you envision a table with an infinite number of rows for your
on-going Thursday Night Poker Party? I doubt it.
I think, instead, you want a function/table function/procedure/operator
that emits a stream of events which are scheduled to occur during some
interval. In relational terms, I'd guess you seek a read-only operator
giving a relation-valued result. The inputs? Two instants. The store
of data used to generate the relation? I suspect you need something
that represents time in a manner analgous to representations of lines,
rays, and line segments -- along with something that represents a
period.
Seems rather straightforward...
Marshall
> Marshall wrote:
> >
> > Seconded. Why not state a more realistic requirement, such as:
> > support events up to 1000 years in the future? 1000 is much,
> > much less than infinite, and so has the advantage of being actually
> > possible.
> >
> > Also remember Scott McNealy's comment: "Most software
> > has the shelf life of a banana." Your software probably won't
> > still be in use in even 10 years.
>
> Also remember that knowing that people actually built
> software with that assumption led to the millennium paranoia.
It is a fair point. But that was people building systems with less
than 50 years of headroom. It turns out some systems last
that long. I am fairly confident that no software built during
my lifetime will be in use 100,000 years from now; probably
not even 1000 years from now.
> Arbitrary limits where none are logically
> necessary have quite a track-record (640Kb anyone?).
If you don't put any limit in the dates you can specify, you
still have the physical limits of the computer, as Bob pointed
out. I think there are a lot of fine compromises; there are a
*lot* of distinct values in a 64 bit number.
Marshall
Nikolai Onken
> of representing each of an infinite set of values in a finite space.
I am not talking about having an infinite amount of events at the same
time and i revised my condition of having to query overlapping infinite
events! It is absolutely obvious that that is not possible.
Whats wrong with the following representation of an infinite event:
datetime_start: 2005-12-24
datetime_end: NULL
repeat: every year
I could even store it on a Floppy :)
> At best, you stated a tiny proper subset of the requirements for what
> you are doing.
You say so :)
> As I said, anyone who gives you detailed design advice online is a crank.
You say so! Thanks for the book hint though :)
Have a look at sitepoint.com - maybe this can be an inspiration on
online advice...
Though i get your point, but I am not asking advice on the entire db
design of some application. I am asking whether the design proposal I
posted would be a good one for the conditions I gave - to more
experienced people than me..
Regards,
Nikolai
Marshall
>
> > You have not been listening. Unless you have an infinite computer,
> > you cannot create an infinite calendar.
>
> Why not? Wouldn't it depend on the requirements? As long as you don't
> need to calculate whether events overlap in the future you could make a
> calendar like
>
> datetime_start
> datetime_end
> repeat_pattern
> series_id
>
> And fill in
>
> 2005-06-12 12:00:00
> "" (never ending)
> "5" (every 5 days)
> ""
Will your software be able to use this schema to display a yearly
calendar for the year
9999999999999999999999999999999999999999999999999999?
What about the year that is that number raised to the power of
that number? What about a year that has a number of digits in it
that is that number times larger than all the bits of storage in
the computer you're using? Because all of those numbers are
very small in comparison with infinity.
You might wish to consider discontinuing the use of the word
"infinite" because it has a very specific meaning that you haven't
mastered yet. I suggest using a less formal term like "indefinite."
Marshall
JayDee
Snodgrass and Jensen and Christianson and Ben Ziv (I'm apologize if the
names aren't correct) and others have made immense contributions to the
field of temporal data.
But have you looked at the solutions presented in the Snodgrass book?
Yes, it's a significant achievement and, yes, it gets things done using
SQL, and yes, it may well work. But the complexity! The redundancy!
The work-arounds! The number of situations which result in
'RAISE...ERROR' with no way out! It's an absolute horror. Sure,
convincing someone to implement such a system will keep plently of
arcane SQL coders pounding keys for quite a long while -- but the
chances of actually delivering something significant (Say, a design
that results in more than 100 bi-temporal tables.) and correct are
slim.
Date's approach seems completely reasonable to me. After all, we are
dealing with computers, right? The best we can hope for are acceptable
representations of continuous systems. I mean, is there really a need
to handle a time interval as an infinite number of instants?
Yes, he presented a language that isn't implemented. Judging from what
I've seen in SQL-implementations of semi-temporal and bi-temporal data
stores, it should be!
Nikolai Onken
calculate the number of
9999999999999999999999999999999999999999999999999999 to the power of
9999999999999999999999999999999999999999999999999999..
I care about whether a concept I use would work if computers would have
the power.
And in the year 9999999999999999999999999999999999999999999999999999 I
am sure that computers will be able to handle these calculations ;)
I am also sure that any computer will be able to handle date_end = NULL
as a representation of an infinite event rather than hoping that
date_end =
54387589437583454356778567568757685685754353563456356356356356 will be
supported by a computer at some time in the future.. Though I might be
wrong!
hmm... otherwise please refer me to some more literature on infinite :)
Regards,
Nikolai
Alvin Ryder
> Hey Volker,
>
Hi Nikolai,
Hope you don't mind me joining in here.
> thank you for your reply!! Thats what I was wondering! How long could
> Outlook actually run..
It crashed badly at 2Gb for a person I know ...
> I think the best solution for my case would be to have three tables:
>
To do all this purely and properly you need a temporal database, but
last time I looked they were still only at the theoretical stage, there
are competing theories and I'm not aware of any main-line commercial
products (that I'd wanna buy).
Meantime, I've had to solve something similar and I did it pretty much
the way you described. I used a rdbms to store some (non-relational)
data and a procedural language to do the rest.
You can't take a pure relational approach because the RM is based on
sets, which contain *unordered* elements but time is ordered! Queries
range from awkward to impossible. For instance, how can you test for
overlap in the events table?
There are several types of databases the relational model can't handle
effectively, this is one of them. This isn't to say you can't use a
rdbms in a non-relational way. (My excuse was this is an OO program
that happens to use a db for persistence).
Yes I consider it workable, it works for me.
My events were between any two timestamps and I only needed forward
data for a couple of years and nothing in arrears. My repeated or
perpetual events were only down to the day. If you need billions of
nanosecondly events I dare say you'll need to reconsider ;-)
> The repeat pattern could then easily be using CRON Syntax or some user
> defined syntax which will be executed by the application or by the
> database if it supports events...
Agreed, I used CRON as inspiration too.
> Regards,
>
> Nikolai
HTH
Cheers.
Nikolai Onken
> Agreed, I used CRON as inspiration too.
Did you use a stored procedure to parse the pattern or did you move
that on the application side?
the remaining worry I would have is how to ensure integrity...
What if someone somehow deletes an event through say MySQLAdmin?
Since the repeating rules are not tied to the actual events (though
events know of their rule) I would have to make sure that events only
get added/modified through a clearly defined data storage class or
so...
Maybe it would be possible to create some sort of checksum after each
time a pattern changes the entries (what could that consist of? Number
of rows related to a pattern could be misleading since I theoretically
could accidentally delete a row and add one)
This especially gets tricky if I want to connect other data to
events....
Its quite interesting that there's no commercial main line temporal
database existing yet but I'm curious to see what kind of concepts
'Temporal Data and the Relational Model' will reveal. Are there any
projects being pushed by bigger companies?
Regards,
Nikolai
mAsterdam
...
[Temporal data and the relational model by Date, Darwen & Lorentzos]
> uses a "Chronons" (discrete points of time) model for temporal data,
> which is rejected by virtually all of the temporal database academics
> because it fails to model time as a continuum.
And "Chronons" failing to model time as a continuum is
bad because ...?
Do you know some of the academics' arguments?
This is what DD&L say about it (p62, note 8.):
"It seems to us that the confusion over whether chronons and
granules are intervals stems from a confusion over intuition
vs. formalism. An intuitive belief about the way the world
works is one thing; a formal model is something else entirely.
In particular, we might /believe/ the timeline is continuous
and infinite, but we nevertheless /model/ it for computing
purposes as discrete and finite."
> A better and cheaper cholice would be to download the PDF book by Rick
> Snodgrass at the University of Arizona website. Rick has been doing
> temporal RDBMS work for over 20+ years and has actual SQL code in the
> major dialects in his book, as well as accepted theoretical basis.
>
mAsterdam
> ... I am fairly confident that no software built during
> my lifetime will be in use 100,000 years from now; probably
> not even 1000 years from now.
:-)
http://www.collusion.org/Article.cfm?ID=329
>>Arbitrary limits where none are logically
>>necessary have quite a track-record (640Kb anyone?).
>
>
> If you don't put any limit in the dates you can specify, you
> still have the physical limits of the computer, as Bob pointed
> out. I think there are a lot of fine compromises; there are a
> *lot* of distinct values in a 64 bit number.
Agreed.
paul c
> Cheaper? No doubt. Better? I'm not sure.
>
> Snodgrass and Jensen and Christianson and Ben Ziv (I'm apologize if the
> names aren't correct) and others have made immense contributions to the
> field of temporal data.
>
> But have you looked at the solutions presented in the Snodgrass book?
> Yes, it's a significant achievement and, yes, it gets things done using
> SQL, and yes, it may well work. But the complexity! The redundancy!
> The work-arounds! The number of situations which result in
> 'RAISE...ERROR' with no way out! It's an absolute horror.
Based on other solutions from some of the SQL crowd, I can believe that
without looking at it and the usual unspoken motive probably applies -
"jobs for the boys".
> ... Sure,
Bob Badour
And there are just as many distinct (ie. non-overlapping) ranges the
entire size of a 64 bit number in a 128 bit number. And a 128 bit number
occupies a minute fraction of the memory available in a modern laptop
computer while the entire state machine defined by the largest computer
ever built is still finite.
I strongly suspect some people come to the newsgroup not to learn but
because they are enamoured with their latest creation and think everyone
else should be just as enamoured.
Alvin Ryder
> >> Are you referring to 'Databases, Types and the Relational Model' by C.
> J. Date, Hugh Darwen? <<
>
> No, the book Bob refers to is from Morgan-Kaufmann and has gone out of
> print.
Amazon can ship it within 24 hours. You can get used ones too.
> It is not really very useful for a working programmer, since
> Date invented a language that not implemented for the book. It also
> uses a "Chronons" (discrete points of time) model for temporal data,
> which is rejected by virtually all of the temporal database academics
> because it fails to model time as a continuum.
>
Actually as a working programmer I found the work of Lorentzos, Date et
al useful, yes it is theoretical but by knowing the limits of the RM I
was able to deal with the situation better and more quickly.
As for the "rejected by virtually all academics" remark I can't accept
it unless you have researched the topic? All the academics I worked
with held Lorentzos'/Date/Darwen's work as the shinning light. Maybe I
was just sucked into a cult ;-)
As for the continuum of time, I'm only aware of the physics definitions
which are based on discreet sequences of events, like tick tock or so
many cesium atom vibrations. I'm interested to know who uses a
continuum?
As for contraversy, of course, I've never encountered one theory of man
which is free from contention or opposing views. Especially in relation
to time we've been debating it in philosophy, religion, physics, math
... well dare I say, since the beginning of time (whatever that means).
> A better and cheaper cholice would be to download the PDF book by Rick
> Snodgrass at the University of Arizona website. Rick has been doing
> temporal RDBMS work for over 20+ years and has actual SQL code in the
> major dialects in his book, as well as accepted theoretical basis.
Dialects of SQL don't do a lot for me. Especially since the problems
are at the model level. I can't imagine any theory of time, discreet or
continuum based, that does not require ordering, sets are unordered.
But thanks for the reference I'll check it out.
Cheers ;-)
Alvin Ryder
> Hey Alvin,
>
> > Agreed, I used CRON as inspiration too.
>
> Did you use a stored procedure to parse the pattern or did you move
> that on the application side?
>
I had to be vendor neutral pl/sql, ms-sql, mysql, hypersonic ... which
made my decision easy. I just did it all in the application code.
> the remaining worry I would have is how to ensure integrity...
> What if someone somehow deletes an event through say MySQLAdmin?
> Since the repeating rules are not tied to the actual events (though
> events know of their rule) I would have to make sure that events only
> get added/modified through a clearly defined data storage class or
> so...
Yes.
I just solved the problem with acess via my classes and policies like
"no delete access shall be granted". "Hacking is not allowed, otherwise
restore from backups".
> Maybe it would be possible to create some sort of checksum after each
> time a pattern changes the entries (what could that consist of? Number
> of rows related to a pattern could be misleading since I theoretically
> could accidentally delete a row and add one)
Sounds too complicated. One strength of the RM is its simplicity, I
wouldn't stray from that. And you can't make things fool proof no
matter what.
> This especially gets tricky if I want to connect other data to
> events....
Yep, sounds too tricky to me. Avoid complexity and just employ a
policy.
> Its quite interesting that there's no commercial main line temporal
I guess it comes down to supply, demand and commercial buzz.
Back in the 90s it was a hot research topic, well at least warm, but
from what I can tell when the web and XML came, they plundered most of
the temporal reseach.
Spatial databases where also in a similar boat but with lots of geo-nav
devices, satellites, cell phones, geographic and demographic
applications they've managed to see the light of commerce.
> database existing yet but I'm curious to see what kind of concepts
> 'Temporal Data and the Relational Model' will reveal.
It's too involved to get into here, I tried but it gets too long too
quickly.
But you've already discovered some of the problems and you you could
see the book's contents at Amazon and you may be able to find some of
Lorentzos or Darwen's papers. This material is also covered in some
grade courses as well but I'm finding it hard to find any info now,
it's lilke a ghost town.
> Are there any
> projects being pushed by bigger companies?
Oracle have extensions for spatial database, web, data-warehousing,
XML, object ... but nothing purely in the temporal realm AFAIK.
The temporal db topic intersects with spatial dbs and data-warehousing
so you may find stuff burried in there?
> Regards,
>
> Nikolai
Even though it isn't a perfect fit I think you should be able to make
good use of a rdbms and arrive at a reasonable solution.
Cheers.
x
"Nikolai Onken" <nikola...@gmail.com> wrote in message
news:1147860004.9...@i40g2000cwc.googlegroups.com...
> Where? Are there any links about that?
Think about the I:U--->U, I(x)=x transformation :-)
http://ocw.mit.edu/index.html
http://citeseer.ist.psu.edu/
http://www.uspto.gov/patft/index.html
http://www.cs.cmu.edu/~mjw/recipes/
Bon apetit!
x
"Bob Badour" <bba...@pei.sympatico.ca> wrote in message
news:m2Hag.8871$A26.2...@ursa-nb00s0.nbnet.nb.ca...
> At best, computers use 0.000000000000000000000000000000000000000000... %
> of the set of rational numbers, which are themselves
> 0.000000000000000000000000000000000000.... % of the reals.
Wow! You just represented the infinity. :-)
x
> hmm... otherwise please refer me to some more literature on infinite :)
Look in an empty glass of vodka. :-)
Look in your eyes. :-)
x
"Marshall" <marshal...@gmail.com> wrote in message
news:1147879954....@i39g2000cwa.googlegroups.com...
> mAsterdam wrote:
> > Marshall wrote:
> > >
> > > Seconded. Why not state a more realistic requirement, such as:
> > > support events up to 1000 years in the future? 1000 is much,
> > > much less than infinite, and so has the advantage of being actually
> > > possible.
> > >
> > > Also remember Scott McNealy's comment: "Most software
> > > has the shelf life of a banana." Your software probably won't
> > > still be in use in even 10 years.
> >
> > Also remember that knowing that people actually built
> > software with that assumption led to the millennium paranoia.
>
> It is a fair point. But that was people building systems with less
> than 50 years of headroom. It turns out some systems last
> that long. I am fairly confident that no software built during
> my lifetime will be in use 100,000 years from now; probably
> not even 1000 years from now.
They don't last that long because they are built by someone like you. :-)
> > Arbitrary limits where none are logically
> > necessary have quite a track-record (640Kb anyone?).
> If you don't put any limit in the dates you can specify, you
> still have the physical limits of the computer, as Bob pointed
> out. I think there are a lot of fine compromises; there are a
> *lot* of distinct values in a 64 bit number.
No. They have those limits because they are built by someone like you. :-)
x
"Alvin Ryder" <alvi...@telstra.com> wrote in message
news:1147887327.0...@j55g2000cwa.googlegroups.com...
> You can't take a pure relational approach because the RM is based on
> sets, which contain *unordered* elements but time is ordered!
Who ordered Time ? :-)
There are peoples that live in a loop. Ask Neo.
Alfredo Novoa
>>>It also
>>>uses a "Chronons" (discrete points of time) model for temporal data,
>>>which is rejected by virtually all of the temporal database academics
>>>because it fails to model time as a continuum.
>Can you think of any better evidence that Joe is a self-aggrandizing
>ignorant who wouldn't recognize logic if it bit him on the ass?
Indeed, virtually all the temporal database research is based on
discrete models.
In fact, there are quantum theory models which propose that time (like
distance) is discrete. The the smallest possible measurement of time is
known as Planck time, roughly: 5.391 10-44 sg.
But in most practical situations, miliseconds are more than enough.
Regards
Alfredo
x
"Alfredo Novoa" <alfred...@hotmail.com> wrote in message
news:1147947938.9...@g10g2000cwb.googlegroups.com...
> Indeed, virtually all the temporal database research is based on
> discrete models.
> In fact, there are quantum theory models which propose that time (like
> distance) is discrete. The the smallest possible measurement of time is
> known as Planck time, roughly: 5.391 10-44 sg.
Maybe we should try indiscrete models for a change ?
x
"x" <x...@not-exists.org> wrote in message news:e4hidp$5l6$1...@emma.aioe.org...
Oops. Correction: indiscreet. Wrong e.
Alfredo Novoa
>Maybe we should try indiscrete models for a change ?
Do you mean Paris Hilton and the likes? :)
Regards
Alfredo
x
> x wrote:
>
> >Maybe we should try indiscrete models for a change ?
>
> Do you mean Paris Hilton and the likes? :)
Yeah.
Frank Hamersley
> Cheaper? No doubt. Better? I'm not sure.
>
> Snodgrass and Jensen and Christianson and Ben Ziv (I'm apologize if the
> names aren't correct) and others have made immense contributions to the
> field of temporal data.
Safer to say they have made immense efforts as none of it has made it
into the "standard" yet.
> But have you looked at the solutions presented in the Snodgrass book?
> Yes, it's a significant achievement and, yes, it gets things done using
> SQL, and yes, it may well work. But the complexity! The redundancy!
> The work-arounds! The number of situations which result in
> 'RAISE...ERROR' with no way out! It's an absolute horror. Sure,
> convincing someone to implement such a system will keep plently of
> arcane SQL coders pounding keys for quite a long while -- but the
> chances of actually delivering something significant (Say, a design
> that results in more than 100 bi-temporal tables.) and correct are
> slim.
I agree and I believe it was a large part of the warhead use to sink the
proposal to incorporate it into the standard during the 90's. I think
it was Darwen who led the charge to sink the TSQL proposals.
> Date's approach seems completely reasonable to me. After all, we are
> dealing with computers, right? The best we can hope for are acceptable
> representations of continuous systems. I mean, is there really a need
> to handle a time interval as an infinite number of instants?
>
> Yes, he presented a language that isn't implemented. Judging from what
> I've seen in SQL-implementations of semi-temporal and bi-temporal data
> stores, it should be!
I have no info to frame a comment on Dates proposal (yet) but certainly
the 1996 TSQL proposal was very messy. FWICT having scanned lots of the
peer reviewed papers published on the subject, the whole thing did not
evolve a great deal from its original inceptions which suggests the
promoters were unable to come up with the simple and compelling solution
that would sell itself!
Cheers, Frank.
Nikolai Onken
> >Maybe we should try indiscrete models for a change ?
> Do you mean Paris Hilton and the likes? :)
that would probably fresh it all up a little...
Axel Schwenk suggested a design around these lines:
CREATE TABLE event {
id INT PRIMARY KEY,
desc_short VARCHAR(30),
desc_long VARCHAR(255)
}
CREATE TABLE eventrule {
id INT PRIMARY KEY,
event_id INT REFERENCES event.id ON DELETE CASCADE,
time_start TIME,
time_end TIME,
date_start DATE NOT NULL,
date_end DATE,
type ENUM ('single', 'exception', 'daily', 'weekly', ...)
}
CREATE TABLE timeslot {
start DATETIME,
end DATETIME,
rule_id INT REFERENCES eventrule.id ON DELETE CASCADE,
event_id INT REFERENCES event.id ON DELETE CASCADE
}
I think it makes sense as well only event rules get a little tricky...
Nikolai Onken
> I strongly suspect some people come to the newsgroup not to learn but
> because they are enamoured with their latest creation and think everyone
> else should be just as enamoured.
Bob, who are you refering to? :)
I actually still haven't seen almost any marvelous creations in this
thread and thats really what would make it interesting... we can keep
writing about who knows better what infinite means or not :) maybe a
simple SQL example would just make it clear?... (yeah and I know we
don't still know anything about the requirements of a calendar and it
is impossible to post an example without having them exactly down to
the point... hell, calendars are really a pain in the axx :) )
Ohh, and I really am curious how you would then design a simple
calendar DB with say all functions like Outlook (Patterns included!)...
hoooooooowww?
Regards,
Nikolai
Nikolai Onken
I wanted to go to buy tickets to Israel today and guess what..
Inbetween the time our agent called her agent to find out the price and
the call she made to confirm the flight after we decided that it was
the right flight the price of the flight raised by 6 percent... yeah
the guys who have writte that kind of software must have had a good
imagination of time and sense.........
Better than that our agent had to tell us after we said that we want
the flight, that the online based system crashed and that she would
have to call us back today or tomorrow - with no guarantee whether the
price stais or whether the flight actually would be still available
(since yeah of course 500 other people could have tried to book the
same flight at the exact same point...)...
Anyways... of course i don't know why their system crashed but
obviously these guys didn't have a clear idea how to handle time in
their software...
Not only where they not able to make an offer without having the price
change within 5 minutes because the system is online based, but they
where not even able to freeze a reservation or so....
I don't even want to know how they handle price changes every few
minutes and stuff in a relational database :)
Luckily we have processors who can handle 64bit ... and dates until
forever...
Jay Dee
I've done this. Some of my other responses hinted at the approach
I used.
The key to success was that limitlessly recurring events (Are those
the patterns to which you refer?) don't have to be made manifest
until someone wants to look at a specific date interval in order to
see what's on -- and can evaporate when the user isn't looking.
There were, of course, practical bounds: a calendar table was populated
with a row for each date the system knew about -- 200 years seems
right. That table held things like year, month, day, day of week,
week of year. Something else held holidays generated from SQL which
did "the second Sunday in May is Mother's Day." and "the first Sunday
in September after Labor Day as National Grandparents Day." (Actually,
that part was fun!) Something else stamped "beginning of quarter,"
"end of year," &c. all over the place.
With that in place, things like "the first Monday" and "the last
Thursday" and the "latest business day on or before each payday" were
easily handled. I glued a web interface on it and gave others on
the intranet a button they could glue on their web sites. It was
way cool -- especially since individuals with database privileges
could roll their own "patterns."
The trick? (Ah, geez, do I have to tell you everything?) Obviously,
not everything was in the data -- but the data necessary to generate
the data were. (Is that too obscure?) The thing that spewed data
looked like a table, but it wasn't. All the "patterns" were data
and they - along with the calendar table - were sources for the thing
that spewed data.
But if you want something "like Outlook (Patterns included!)" I'd say,
"It already exists. It's called Outlook."
Jay Dee
> I didn't tell you yet:
> I wanted to go to buy tickets to Israel today and guess what..
> Inbetween the time our agent called her agent to find out the price and
> the call she made to confirm the flight after we decided that it was
> the right flight the price of the flight raised by 6 percent... yeah
> the guys who have writte that kind of software must have had a good
> imagination of time and sense.
[You're stepping on my toes, today...]
I did a couple projects for a US air carrier. Prices change all the
time. Most travelers realize that prices increase as departure time
nears. Most travelers don't realize that prices on some legs drop
like a rock as departure time gets very near. Shop wisely if price
is your criterion.
You mentioned Israel: was that an international flight? Price
fluctuations in that arena don't happen - at least, not the way I
gathered from your description. Something else is in the mix...
> Better than that our agent had to tell us after we said that we want
> the flight, that the online based system crashed and that she would
> have to call us back today or tomorrow - with no guarantee whether the
> price stais or whether the flight actually would be still available
I doubt it was "the" system.
> (since yeah of course 500 other people could have tried to book the
> same flight at the exact same point...)...
> Anyways... of course i don't know why their system crashed but
> obviously these guys didn't have a clear idea how to handle time in
> their software...
I suspect the contrary is true.
> Not only where they not able to make an offer without having the price
> change within 5 minutes because the system is online based, but they
> where not even able to freeze a reservation or so....
Price changes don't have anything to do with whether "the system is
online based." As for "freez[ing] a reservation:" that depends on
with whom you're dealing; it might be time to buy better agency.
> I don't even want to know how they handle price changes every few
> minutes and stuff in a relational database :)
Very easily - but I suspect the underlying model their system uses is
network rather than relational.
> Luckily we have processors who can handle 64bit ...
Your 64 bit processors ain't nothing compared to the processors they're
using.
and dates until
> forever...
We'll just have to wait to see whether or not that's true.
Nikolai Onken
> I've done this. Some of my other responses hinted at the approach
> I used.
I'll definitely have a closer look again!!
> The thing that spewed data
> looked like a table, but it wasn't.
I want to see the thing ;)
> But if you want something "like Outlook (Patterns included!)" I'd say,
> "It already exists. It's called Outlook."
Not as a source of ideas on how to build a calendar, since now I know
that it only supports dates until .... hmmm... I forgot...
> You mentioned Israel: was that an international flight? Price
> fluctuations in that arena don't happen - at least, not the way I
> gathered from your description. Something else is in the mix...
I have never made this experience myself, but yes, it is an
international flight and yes, I was sitting in the travelagency and in
between the first phonecall (where the agent asked for the price) and
the second one (where she confirmed the booking) the price changed....
> I suspect the contrary is true.
Well probably you're right
> Price changes don't have anything to do with whether "the system is
> online based."
I know, thats what the agent said :)
Anyways, its probably all pretty off-topic, sorry for that...
I would find it quite interesting to have some sort of ressource where
you can find information on how to handle time based database issues
(maybe I should set it up.. though i know least about it...) I haven't
seen so many sites and not everybody can or wants to order books to
learn about such an issue... it doesn't seem to be such a straight
forward issue...
Otherwise why is this thread so long?
Regards,
Nikolai
Jay Dee
> Jay Dee wrote:
>
>>I've done this. Some of my other responses hinted at the approach
>>I used.
>
> I'll definitely have a closer look again!!
>
>>The thing that spewed data
>>looked like a table, but it wasn't.
>
> I want to see the thing ;)
It's not magic. In Postgres: a function. In Oracle: a table
function. In RM: a read-only operator which returns a value of
type relation. Use 'em in SQL queries like
select <what_i_want_to_see>
from not_magic
where not_magic.date between <from_now> and <until_then>
Nikolai Onken
Probably the more fun part is really the creating of the patterns,
rules e.g. "2 days after payday".. --- but only once you have a working
and valid syntax for patterns and repeating events and the engine which
understands the syntax to do all the work...
What would you base such a syntax on? Some people suggested using
something like CRON, which at the moment seems most logical to me..
Regards,
Nikolai
Nikolai Onken
Maybe the iCalendar standard is a good thing to start with?
Jay Dee
>>What would you base such a syntax on?
>
>
> Maybe the iCalendar standard is a good thing to start with?
>
were marked-up.
I invented a tiny language that had some tokens for "beginning of"
and "end of" various things, some "first" and "last" qualifiers,
some day of week and month of year specifiers, and addition and
subtraction -- so things like "last fri - 2" meant the Wednesday
before the last Friday in a month. And I parsed and tokenized
and interpreted the things in procedural language.
Man, talk about awkward! substr() this and instr() that and those
stinky loop; exit when; end loop; constructions filled with if
then end if; and my own push and pop and dynamic sql... But once
you find the groove -- and the data have been correctly analyzed
and properly stored -- you can crank out some product.
-CELKO-
I am self-aggrandizing when I mention someone else's book? Wow!
Great logic.
Check the literature; Chronons are out of fashion and have been for
some time. For example, TSQL projects have had about 18-20 people
working on them, all with a decade or two in temporal data and they
reject that model. Jan Hidders is more to date of this than I am, if
you want current info.
I do not need to materialize Aleph null or Aleph one rows in a table
if I can test any requested value to see if it is an element of the
set. Remember basic set theory? I can either enumerate a (finite) set
or I can give a membership rule (infinite sets). Then we argue about
what rules the rules must follow. Dedekind cuts at pi and all that
jazz.
-CELKO-
The old FIPS-127 specs said that SQL had to go out to at least 5
decimal seconds, but önly" to 9999-12-31; do java programmers think
their code will still be in use the year "crazy-far"? :)
-CELKO-
code and feel pretty sure about it. Your code will always look better
in a make-believe language that you do ot have to implement. But I
find some of Date's code to be problematic. Various arrangements of
PACK() and UNPACK() produce different representations of the same
facts, which he considers a problem in SQL, but not in his language.
I can live with the assumption that time has a starting point -- some
temporal logic uses that model because time moves in one direction.
But I cannot see time with a pre-determined end point.
The one that really got me, however, was some charts in the back of the
book where one axis is parts (P1, P2, etc.). He puts them into
intervals, just the way he does Chronons. Parts are discrete and
keyed by a nominal scale, and are not continous or dense. If you stop
and look at it, this is a multi-value DBMS model, not a relational one.
And time is a bitch to work with by its nature-- remember the old kids
math puzzle Ïf a hen and half can lay an egg and half in a day and a
half, then how long does it take for ..?"
>> Date's approach seems completely reasonable to me. After all, we are dealing with computers, right? The best we can hope for are acceptable
representations of continuous systems. I mean, is there really a need
to handle a time interval as an infinite number of instants? <<
No, not as an "infinite number of instants", but as a continuum, which
is very different. A continuum has no points, so everything is an
interval. This is what explains Zeno's paradoxes. This is a model,
not an implementation. What we should have done in SQL was require the
(start,end) model instead of points in time either directly or by
implication
Frank Hamersley
> Marshall wrote:
>> mAsterdam wrote:
>>> Marshall wrote:
[..]
> I strongly suspect some people come to the newsgroup not to learn but
> because they are enamoured with their latest creation and think everyone
> else should be just as enamoured.
<Pls forgive the indulgence but I couldn't resist a comment viz the source>
And others come to CDT not to learn because they are enamoured with
their knowledge of everything and think everyone else should be just as
enamoured.
</>
One certainty is that it takes all types.
Cheers, Frank.
mAsterdam
> Yes, there is some complexity in doing it in SQL, but I can copy the
> code and feel pretty sure about it. Your code will always look better
> in a make-believe language that you do ot have to implement. But I
> find some of Date's code to be problematic. Various arrangements of
> PACK() and UNPACK() produce different representations of the same
> facts, which he considers a problem in SQL, but not in his language.
"different representations of the same facts" and no way
to decide on a canonical form.
It looks as if this occurs whenever one tries to capture
some meaning, attributed to order per se, in a relational
context. You describe several representations of hierarchy
in your book(s), but it also happens when the aim is as
- seemingly - simple as representing lists:
numbered items/successive items (see Marshall
Spights' recent question/thread on
"A Logical Model for Lists as Relations").
> I can live with the assumption that time has a starting point -- some
> temporal logic uses that model because time moves in one direction.
> But I cannot see time with a pre-determined end point.
There is no smallest or biggest number.
Most representations do have them. Very similar, no?
> The one that really got me, however, was some charts in the back of the
> book where one axis is parts (P1, P2, etc.). He puts them into
> intervals, just the way he does Chronons. Parts are discrete and
> keyed by a nominal scale, and are not continous or dense. If you stop
> and look at it, this is a multi-value DBMS model, not a relational one.
MV resticted to sets. The representation does not
immediately show that, though.
Nikolai Onken
> and "end of" various things, some "first" and "last" qualifiers,
Did you also implement some sort of recursion so you can support things
like eveery 1st monday and every 3rd tuesday.. Probably its easiest
though to make two rules out of it..
> Man, talk about awkward! substr() this and instr() that and those
> stinky loop; exit when; end loop;
I'll definitely give it a try and write my own little engine. Most
important is that its extendable... Weird, but isn't there anything
like that already somewhere viewable on some website? If not I'll make
my engine public once its somehow working..
Nikolai Onken
http://groups.google.com/group/comp.databases.theory/browse_thread/thread/d76015b7f68d8daa
"The most dangerous enemy of a better solution is an existing codebase
that is just good enough."
Funny :)
x
"Nikolai Onken" <nikola...@gmail.com> wrote in message
news:1147970211....@i39g2000cwa.googlegroups.com...
> x wrote:
> > >Maybe we should try indiscrete models for a change ?
> > Do you mean Paris Hilton and the likes? :)
>
> that would probably fresh it all up a little...
> Axel Schwenk suggested a design around these lines:
> CREATE TABLE eventrule {
>....
> type ENUM ('single', 'exception', 'daily', 'weekly', ...)
> }
> I think it makes sense as well only event rules get a little tricky...
You mixed positives, negatives, points , intervals. It's a mess.
But you know that.
Nikolai Onken
> But you know that.
The only weird one in the list is 'exception' I find.. But aren't the
eventrules just definitions of different somehow repeating timespans?
So saying NOT timespan (exception) should be fine as well.. As long as
the eventrule syntax is easy to understand... What I liked about Axels
design was the denormalisation of timeslot.event_id..
David Cressey
"Jay Dee" <ais0...@aeneas.net> wrote in message
news:%04bg.26699$YI5....@tornado.ohiordc.rr.com...
> Nikolai Onken wrote:
> > I didn't tell you yet:
> > I wanted to go to buy tickets to Israel today and guess what..
> > Inbetween the time our agent called her agent to find out the price and
> > the call she made to confirm the flight after we decided that it was
> > the right flight the price of the flight raised by 6 percent... yeah
> > the guys who have writte that kind of software must have had a good
> > imagination of time and sense.
>
> [You're stepping on my toes, today...]
Back in the 1980s, I used to teach database programming and design in an
industrial setting. The course was product specific, (DEC Rdb/VMS), but
many of the concepts were not. I used to use the ariline reservation system
as a classic example of why atomicity in a transaction was important. In my
example, if your transactions can't be atomic, you can't prevent
overbooking.
Then one time, I got someone as a student who had been on the SAABRE
project. He told me that they never did solve that poblem, but management
decided that it was a feature and not a bug! I don't know whether I believe
it, but it's an interesting fable anyway.
More to the point, back in that time frame, the number of fare changes was
about 10,000 per day. I expect it's much greater now.
Anne & Lynn Wheeler
"David Cressey" <dcre...@verizon.net> writes:
> Back in the 1980s, I used to teach database programming and design
> in an industrial setting. The course was product specific, (DEC
> Rdb/VMS), but many of the concepts were not. I used to use the
> ariline reservation system as a classic example of why atomicity in
> a transaction was important. In my example, if your transactions
> can't be atomic, you can't prevent overbooking.
>
> Then one time, I got someone as a student who had been on the SAABRE
> project. He told me that they never did solve that poblem, but
> management decided that it was a feature and not a bug! I don't
> know whether I believe it, but it's an interesting fable anyway.
>
> More to the point, back in that time frame, the number of fare
> changes was about 10,000 per day. I expect it's much greater now.
recent post referencing rewriting major portion of one the airline
res. systems (about a decade ago)
http://www.garlic.com/~lynn/2006j.html#6 The Pankian Metaphor
part of the issue was the paradigm had been established in the 60s and
while things like scale-up had been done ... there hadn't really been
any re-examination of the various design decision trade-offs
established in the 60s ... and whether advances in technology might
result in making other kinds of design decision trade-offs.
lets say there are around half-million take-off/landings a day (flight
segments) and for round numbers there are 50 passengers per flight
segment ... so you could have on the order of 25million updates per
day (independent of changes in fares and schedules). that is besides
possibly similar number of inserts of new PNR records for reservations
and then similar number of deletes for old PNR records.
part of the issue is that there has been some amount of bookings where
the person is no-show. somewhat as a result there tends to be some
fine tuning of capacity planning attempting to get every seat filled,
despite some percentage of no-shows.
--
Anne & Lynn Wheeler | http://www.garlic.com/~lynn/
-CELKO-
But his ënd of time" is part of the *model* and not the
implementation.
>> MV resticted to sets. The representation does not immediately show that, though. <<
But Date's [p1: p5] notation requires that there must exist {p1, p2,
p3, p4, p5} and that they can be ordered by their names. That does not
make any sense. If I discontinue p3 (pink lawn flamingoes) what
happens to [p1:p5]? If I replace p3 with p3.1 (yellow lawn flamingoes,
which are popular here in Austin, TX because of Lance Armstrong), what
to [p1:p5]? If I replace the "p#" with a UPC code, what to [p1:p5]?
A countable set is not like a time continuum. I cannot remove Tuesday
from the interval [Monday : Thursday]. I cannot add 'Floobday" to the
week. Etc.
mAsterdam
>>>There is no smallest or biggest number. Most representations do have them. Very similar, no? <<
>
> But his ënd of time" is part of the *model* and not the
> implementation.
Well, yes. As far as as I understand it, they (DD&L) discuss a
discrete (vs continuus) universe representation - at least as
far as time is concerned. Why the 'but'?
>>>MV resticted to sets. The representation does not immediately show that, though. <<
>
> But Date's [p1: p5] notation requires that there must exist {p1, p2,
> p3, p4, p5} and that they can be ordered by their names. That does not
> make any sense.
I'm not sure if I am getting this the way you mean it.
You mean p1, p2 etc. as time points?
I am guessing here:
Something like (whatever it may mean - you could maybe
provide a sensible interpretation?) this:
During Birds
[p1:p5] pink lawn flamingoes
?
> If I discontinue p3 (pink lawn flamingoes) what
> happens to [p1:p5]?
During Birds
[p1:p2] pink lawn flamingoes
[p4:p5] pink lawn flamingoes
> If I replace p3 with p3.1 (yellow lawn flamingoes,
> which are popular here in Austin, TX because of Lance Armstrong), what
> to [p1:p5]?
No p3.1, just:
During Birds
[p1:p2] pink lawn flamingoes
[p3:p3] yellow lawn flamingoes
[p4:p5] pink lawn flamingoes
> If I replace the "p#" with a UPC code, what to [p1:p5]?
I'm still wondering wether my interpretation of your
questions is correct.
> A countable set is not like a time continuum.
> I cannot remove Tuesday
> from the interval [Monday : Thursday].
Not as such, removing Tuesday from an interval over
cyclic weekday points renders two weekday intervals.
[Monday:Monday] ...
[Wednesday:Thursday] ...
> I cannot add 'Floobday" to the week. Etc.
Heh - it would become a strange week indeed.
ISTM that's the only reason why I wouldn't add such a day -
or are you suggesting there is some reason why, with the
ideas of the academics you mentioned it /is/ possible to add
"Floobday" to the week, while with DD&L's timepoints it
isn't possible, and somehow this makes the academics'
approach better?!
-CELKO-
I cannot shake Zeno's paradoxes which occur with a discrete model of
time.
>> I'm not sure if I am getting this the way you mean it. You mean p1, p2 etc. as time points? <<
NO, P# as part numbers! He uses the [<start> : <terminal>] notation
for anything.
>> During Birds [p1:p5] pink lawn flamingoes <<
Exactly! See what I mean about how it does not make sense! Then they
have PACK() and UNPACK(), etc.
>> I'm still wondering wether my interpretation of your questions is correct. <<
Yes, and it really is that crazy for a set of unordered distinct
elements whose names can be put into a sorted order. It crossses
logical and physical bounds and makes no sense.
>> Not as such, removing Tuesday from an interval over cyclic weekday points renders two weekday intervals. <<
But I cannot remove Tuesday (the time period whose name is "Tuesday")
from the cycle. About 40 years ago, the US had an animated cartoon on
the Captain Kangaroo Show called "Tom Terrific"; in one episode, the
villian found a way to remove the weekend so the kids would have to be
school all the time and coudl not play. It was so absurd that young
children could laugh at it.
[Monday:Monday] ...
[Wednesday:Thursday] ...
Does not destroy [Tuesday: Tuesday] -- you mean that your entity was
doing something we do not know about in that time slot. That model
does not apply to Parts.
>> Heh - it would become a strange week indeed. <<
The WB television network in the US did a series of advertisements for
extra days of the week in which children could watch their cartoons.
I granmt that we could l go back to the Roman and African ten-day
cyclces, but this is just re-naming. In the DD&L model, I can actually
create new temporal periods.
-CELKO-
LOL! Look up "Julian Simon" the economist who came up with the idea of
bribing passengers with free trips, money and booze when they were
over-booked!! He was a really smart guy!
Jay Dee
> I cannot shake Zeno's paradoxes which occur with a discrete model of
> time.
See, now I thought that to be a consequence of a continuum in that it
may be divided infinitely. And I thought the concept of concrete
mathematics bridged the gap for those of us who, because we use
computers as general-purpose tools, have to work in the discrete world.
>>>I'm not sure if I am getting this the way you mean it. You mean p1, p2 etc. as time points? <<
>
> NO, P# as part numbers! He uses the [<start> : <terminal>] notation
> for anything.
Well, not anything. (More to follow...)
>>>During Birds [p1:p5] pink lawn flamingoes <<
>
> Exactly! See what I mean about how it does not make sense! Then they
> have PACK() and UNPACK(), etc.
>
>>>I'm still wondering wether my interpretation of your questions is correct. <<
>
> Yes, and it really is that crazy for a set of unordered distinct
> elements whose names can be put into a sorted order. It crossses
> logical and physical bounds and makes no sense.
That may be why you're having difficulty. DD+L specifically excluded
a continuum. The domains for which their interval operators work
are necessarily comprised of discrete values for which predecessor and
successor operators will return a value in that domain. Using bits of
your example,
red flamingo < pink flamingo < white flamingo
may be the domain and
next(red flamingo) = pink flamingo = previous(white flamingo)
is defined. You don't like pink? Okay. Change the domain. Of course,
you have to paint all the pink flamingos in existence -- but maybe
there aren't any. Your idea of eliding Tuesday has nothing to
do with their proposed technique; it's patently absurd. (As would be
yellow flamingos! But folks in Austin have all sorts of strangeness
on their lawns)
Regarding domains: the question, "Is absorption a property of some value
in the domain?" must be answered depending on what the domain is and how
it can reasonably be represented. If, as someone else said in this
thread, "all time intervals have a starting point but they may not
always end" (Or something to that effect. Seems strange to me, but...)
one may want to use something they call NULL to represent the end of
time which will never occur. Then, next(NULL) = NULL and infinity has
been represented. The question, "What does previous(NULL) give me?" is
up to the domain modeler. If he thinks he can put a date/time stamp
on the instant before a time which will never come: go for it!
DD+L were also very careful about the types of intervals for which
their approach worked. And that's not all; there was more.
Overall, I believe they carefully qualified the techniques they
described -- so well, in fact, that your misunderstandings indicate
that you aren't very familiar with the material.
paul c
You're trying to change the subject again.
p
mAsterdam
>>>Well, yes. As far as as I understand it, they (DD&L) discuss a discrete (vs continuus) universe representation - at least as far as time is concerned. Why the 'but'? <<
>
> I cannot shake Zeno's paradoxes which occur with a discrete model of
> time.
The computerized model by the academics you
mentioned can? Which model? Who are they?
>>>I'm not sure if I am getting this the way you mean it. You mean p1, p2 etc. as time points? <<
>
>
> NO, P# as part numbers!
Part intervals do sound weird, I can't think of
a sensible interpration right now.
I had to look it up. I didn't remember they used part-points
on page 355.. (is that what you are talking about? - if
not could you please provide I reference so I don't have
to guess again?) in their example with two interval
attributes in a relation.
Part intervals do sound weird. I read over it and
at the time did not think it was important,
because they just use it to demonstrate that
the PACK and UNPACK operators not only work
on the typical case where there is just one
interval attribute, but still work on relations
with more of those.
> He uses the [<start> : <terminal>] notation
> for anything
Well, anything as long as the requirements for
the Allen-operators on intervals are met.
One needs very specific parts for
that - maybe e.g. rail-sections?
>>>During Birds [p1:p5] pink lawn flamingoes <<
>
>
> Exactly! See what I mean about how it does not make sense! Then they
> have PACK() and UNPACK(), etc.
They use PACK and UNPACK to show that working with
interval attributes does not compromize the
relational model as they see it.
>>>I'm still wondering wether my interpretation of your questions is correct. <<
>
>
> Yes, and it really is that crazy for a set of unordered distinct
> elements whose names can be put into a sorted order.
I don't think that is what is going on in the examples.
The 'names' you are referring to are, so it seems to me,
just nicely chosen symbols in order to not confuse the
reader.
> It crossses
> logical and physical bounds and makes no sense.
No, it is discrete - and you don't seem to accept that approach.
>>>Not as such, removing Tuesday from an interval over cyclic weekday points renders two weekday intervals. <<
>
>
> But I cannot remove Tuesday (the time period whose name is "Tuesday")
> from the cycle. About 40 years ago, the US had an animated cartoon on
> the Captain Kangaroo Show called "Tom Terrific"; in one episode, the
> villian found a way to remove the weekend so the kids would have to be
> school all the time and coudl not play. It was so absurd that young
> children could laugh at it.
>
> [Monday:Monday] ...
> [Wednesday:Thursday] ...
>
> Does not destroy [Tuesday: Tuesday] -- you mean that your entity was
> doing something we do not know about in that time slot. That model
> does not apply to Parts.
I'll give it a try with the rail segment interpretation.
Here's a working schedule for our rail maintenance crew:
During Segments
[d1:d5] [p1:p4]
Interpretation: on day one to day five we'll do
maintenance work on rail segments one to four.
Event: we got orders to repair segments six to nine on day
two with all our capacity at the cost of postponing our work
on [p1:p4] one day:
During Segments
[d1:d1] [p1:p4]
[d2:d2] [p6:p9]
[d3:d6] [p1:p4]
Event: Segment p3 appears in good shape,
the work is finished after d1.
During Segments
[d1:d1] [p1:p4]
[d2:d2] [p6:p9]
[d3:d6] [p1:p2]
[d3:d6] [p4:p4]
>>>Heh - it would become a strange week indeed. <<
It's a pity that you have a tendency to snip
the comments I think are most relevant.