Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: How to store temporal info?

Re: How to store temporal info?

From: OlegJay <olegjay_at_yahoo.com>
Date: 10 Mar 2003 12:47:04 -0800
Message-ID: <47d21956.0303101247.344ad218@posting.google.com>


It seems we want to logically model time but are driven by physical and SQL considerations. But I've always heard that the physical is not supposed to be of any relevance in logical modeling.

Just as a matter of logical design philosophy, since I don't have that many years experience, aren't SQL and implementation concerns part of physical design rather than logical modeling? Or is it that a) there isn't an established set of logical modeling rules for temporal databases yet and b) there aren't many additional principles (like avoiding redundancy and providing flexibility as in the usual normalization) or any to add temporal modeling steps to a logical model and so we just use physical concerns in this new field of modeling temporal databases.

Or are these ideas in this and other similar threads for the physical model and we shouldn't include temporal modeling in the logical, like SINCE, START and END for valid, revelation, and load times?

Thanks, for any answers.

Alan Gutierrez <ajglist_at_izzy.net> wrote in message news:<slrnb67l5t.6s3.ajglist_at_izzy.net>...
> In article <b40ea5$1qdp76$1_at_ID-167942.news.dfncis.de>, Lennart Jonsson wrote:
>
> > On Mon, 03 Mar 2003 19:43:53 +0000, John Gilson wrote:
> >
> >> "Lennart Jonsson" <lelle2_at_bonetmail.com> wrote in message
> >> news:b407bq$1qd9l2$1_at_ID-167942.news.dfncis.de...
> >>> On Mon, 03 Mar 2003 14:18:49 +0000, John Gilson wrote:
> >
> > [...]
> >> Two issues come to mind Lennart:
> >>
> >> 2. Consider the queries to get a resource R's category at time T when
> >> there's a start-time and when there's both a start-time and end-time.
> >> When there's just a start-time, the query might be
> >>
> >> SELECT RC1.*, :T
> >> FROM ResourceCategories
> >> WHERE resource = :R AND
> >> since = (SELECT MAX(since)
> >> FROM ResourceCategories
> >> WHERE resource = :R AND since <= :T)
>
> I had a since field myself. I got rid of it. I am happy.
>
> >> with both a start-time and end-time one can write
> >>
> >> SELECT *, :T
> >> FROM ResourceCategories
> >> WHERE resource = :R AND :T BETWEEN start_time AND end_time
> >>
> >> Similarly for queries that must calculate the duration of particular
> >> resource-category associations.
>
> > The main objections (for my scenario) that I have against start and stop is that:
>
> > 1 if end_time is allowed to be null (indicating that we dont know when it
> > will sease), one will have to deal with that in the queries. I.e.
> > something similar to:
> >
> > WHERE resource = :R AND :T > start_time AND (end_time is null or :T <
> > end_time)
>
> Then don't use NULL. It is often best to use a value for not-applicable
> or unknown on an indexed column since most database engines will perform
> better on an indexed column when the column is defined NOT NULL.
>
> And as far as a since field goes, a subquery will be run for each row in
> your query, SELECT MAX. BETWEEN can use an index if need be.
>
> > which is ok, but less elegant than "between" (and probably less
> > efficient, I guess)
>
> Both.
>
> > 2 changing the category for a resource means adding a new row, but also
> > that we will have to locate and update the "current row". With "the since"
> > case, it is sufficient to add a new row
>
> Yup. You pay for it at UPDATE, rather than for each row during a query
> with SELECT MAX.
>
> > 3. some kind of check has to be implemented to assure that we are not
> > creating "overlaps" for a resource. In the "since" case, overlaps are
> > impossible.
>
> Is that necessary? An SQL script woud read:
>
> BEGIN;
>
> UPDATE ResourceCategory
> SET stop = CURRENT_TIMESTAMP
> WHERE resource = :R
> AND stop = '9999/12/31 00:00:00';
>
> INSERT INTO ResourceCategory (resource, whatever, start, stop)
> VALUES (:R, :W, CURRENT_TIMESTAMP, '9999/12/31 00:00:00');
>
> COMMIT;
>
> In PostgreSQL CURRENT_TIME will hold the same value for the duration of
> the transaction. Hard to go wrong. Me thinks you worry to much.
>
> > Since I am equipped with a new hammer, everything suddenly looks like
> > nails :-)
>
> I like my new hammer better. What's that honey? Wash the dishes? Sure!
>
> *bang* *bang* *bang*
Received on Mon Mar 10 2003 - 14:47:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US