There was a magazine "Database Programming & Design", now defunct...too
much detail for the average IT Manager so it didn't sell well...that did
a nice multi-part series on the subject. Including the SQL3 connection.
I could only locate a couple of fragments online...it used to be all
online if you were a subscriber and had your password...I'll have look
around for mine and see if I can locate more info. You'll find some
references to and from "Rick Snodgrass" in the links, most anything he
writes I find worth reading. The short of it is, at this point you have
to roll your own...you can implement the "NOW" time slice using a VIEW
over a main table(s) where the view`s "WHERE" clause has as it`s
end_date set to a magic value to indicate infinity or current state
"1/1/3333" or "1/1/9999" if you engine accepts the value. The main table
or tables would contain a start_time & end_time representing the time
slice. For most engines, using multiple indexes concurrently on the same
table is not possible so you must either use self joins or store the
start and end date in separate tables with a common id. Cluster tables
can help significantly when dealing date extraction as long you are not
updating the rows and cause significant page splits. This is where
separate tables for the start & end times would allow you to create two
clustered indexes, one for each table.

We use this type environment, a little, on our test floor to represent
all our running equipment and their current state. Production is mostly
concerned with the "NOW" view of things and engineering tends to be more
interested in various times in the past to help make decisions for the
future. To speed up the "NOW" view for production we strategically place
triggers and some procs to funnel the state/info from dozens of tables
to just one small table (wide but short) representing key data. This
table is used directly for many reports and is heavily index to allow
good joins back to the source tables when needed.

It's hard to get to complicated with this time slice thing and still
have your average app developer keep up with everything and use it
correctly...so for the most part we wait for full support by the DBMS.

Good luck
Ed

http://www.dbpd.com/vault/9810/temporal.html

http://www.dbpd.com/vault/9810snod.html
  

-----Original Message-----
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Daniel BODEA
Sent: Tuesday, April 05, 2005 11:11 AM
To: mysql@lists.mysql.com
Subject: Re: Temporal databases & MySQL

Hi Shawn,

I really meant temporal and not temporary. Temporal as in TSQL2.
Databases
that on the one hand accumulate all changes to data over time along with
accurate time information and on the other hand provide varying degrees
of
transparency in querying this data based on the theory of instants and
aggregated intervals of time.

Most of the resources available online are largely academic though.

Google :
http://www.google.com/search?hl=en&q=temporal+database

Troels' links has a good temporal databases section :
http://troels.arvin.dk/db/rdbms/links/

The TAU Project that has some experimental code for several engines of
which
MySQL :
http://www.cs.arizona.edu/tau/

I need to use this fully in a project that uses MySQL 4.1.latest and in
a
way that's independent of the structure of tables comprising the
application.

I'm not looking for TSQL2 implementations for MySQL or other types of
esoteric implementations at the SQL level. I was just interested in
hearing
from people who have used MySQL to implement this model in a production
environment and what they could say about both the storage of temporal
data
and the optimization of queries on past instants and intervals.

There are several partially incompatible ways of doing this in a generic
relational context but as always, only one is most fit for a given SQL
engine and I'm currently asking about it for MySQL.

I can't possibly be the first one to push this thing onto MySQL based on
production-quality requirements.

Thanks,
Daniel

> I am not familiar with the use of the adjective "temporal" with the
word
> "database". To me "temporal" means "of or having to do with time or
it's
> measurement". Could you have meant "temporary" which means to me
> "non-permanent or transitory in nature."?
>
> Even if you had meant "temporary", I rarely hear it used as a database
> design term except when used with the word "table" as in "temporary
> table". (http://dev.mysql.com/doc/mysql/en/create-table.html)
>
> However, if the TAU project is doing research on databases that are
> displaced or movable through time, this may be something I want to get
> involved with. What is their URL?
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to