The model I described is indeed tailored more for tracking changes and archiving historical records because those were the requirements of the project I was implementing ;-) I just had to figure out the most optimal way of addressing these requirements using MySQL 4.1.
MERGE tables are an excellent way of segmenting the history views to span different storage containers and (I haven't tried this yet) packing the tables that form the view might further speed things up while saving storage space. I was wondering though whether an incremental SQL storage engine existed in open source, MySQL or otherwise. One capable of inserting a row in a table based on some other row by storing only the fields that differ between the two, and ideally performing diffs between large text fields. Any clues ? Thank you, Daniel ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <mysql@lists.mysql.com> Sent: Friday, April 15, 2005 5:23 PM Subject: Re: Temporal databases & MySQL What you describe makes sense and would certainly work, I don't know that I would call it a temporal solution. The ENUM (I, U, D) seams a bit redundant with time. This model resembles a traditional application log or trace file, which is highly desirable for a records keeping system, like for a phone company, or auto dealership. But if your looking for a dynamic time based model to support systems that might track plants and animals that have lived or live on earth that show extinction, reintroduction and evolution...your stuck with start times and end times marking valid entries and a large "WHERE clause" using "BETEEN st_date AND en_date". If you stick with the T_HIST model you may want to check out the MERGE tables, they can help segment your older histories while still giving you a VIEW like access, assuming you can't go to the MySql 5 release. Ed -----Original Message----- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Daniel BODEA Sent: Saturday, April 09, 2005 8:46 PM To: mysql@lists.mysql.com Subject: Re: Temporal databases & MySQL I was thinking about the following model for the application I'm working on: Any given table T holds the conventional data associated with instant NOW, no temporal data at all. There are tables T_HIST for every table T with an identical structure plus one date column which is set to NOW on every insert (these tables are only inserted into) and another ENUM column holding an identifier for the operation (I, U or D). An index would be created over both (date, operation). An INSERT in T is duplicated in T_HIST. An UPDATE is first performed in T and the resulting row is INSERTed in T_HIST. A DELETE first copies the column into T_HIST and the row is deleted from T. As long as all T tables have PKs that are guaranteed not to be recycled, I suppose the benefits would be the following: --- T tables can be kept compact and fast for all mundane operations. Since the history can only grow and is logically separated, it can use separate storage strategies better fit for much larger amounts of data compared to tables T. Temporal data remains a minimal addition while allowing for all (?) temporal queries to be performed. I initially thought there would be circumstances where queries would have to perform a join to the corresponding T tables but then the ENUM column should fully replace the join. Index usage for temporal queries in the MySQL context should be optimal when using the date column as the main index on a table that is naturally guaranteed to have this column ordered at all times. --- Views and triggers would be simulated by the application which should not be too incumbent considering that the application needs to provide some easy means of changing the "querying instant" anyway. I'm in no way favoring any temporal model if it's not for its ability to perform best on a given SQL engine (MySQL in this case). Not being that familiar with the inner workings of MySQL though, I can only submit the module above to the attention of MySQL specialists who may have the time to post back their thoughts. Thanks, Daniel "Daniel BODEA" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
