I don't think I noticed the original post for this discussion when it first
arrived - or maybe I did and mistook it for spam having to do with social
dating ;-) - but I just saw Sheeri's reply and want to jump in if I may.
Most professional databases in corporate environments that I have seen over
the years use the approach of having begin and end dates on each row. Those
dates indicate when the row is valid. For instance, if the row is recording
interest rates, the table would get a new row whenever the current interest
rate changed. Assuming no one knew when the interest rate would change
again, the new row would typically set the current date as the begin date
and the end date would be set to null. The row for the previous interest
rate would be updated at the same time and have its end date set to the
current date (or the day before in some cases).
Queries could easily determine the current interest rate by just finding the
only row in the table whose end date was null. Older interest rates could be
find by searching for the row whose begin date was on or before the search
date and whose end date was on or after the search date. Eventually, as
older interest rates were no longer desired for queries very often, you
might move them to archive tables with names like "IntRates_2005" and remove
them from the main interest rates table altogether.
I don't know enough about performance in MySQL to venture an opinion of how
this would perform. You'll need to research that yourself.
--
Rhino
----- Original Message -----
From: "sheeri kritzer" <[EMAIL PROTECTED]>
To: "Douglas Sims" <[EMAIL PROTECTED]>
Cc: "mysql List" <mysql@lists.mysql.com>
Sent: Thursday, May 04, 2006 3:15 PM
Subject: Re: Effective-dating
Coming to the table SOOO late. But this has special relevance as I'm
working on an application that stores event dates and therefore will
also need to solve this problem.
The biggest question I have is "What will this be used for?" My first
thought is to have at least 2 tables -- one table with the rows that
are "expired" and another with "non-expired rows".
In an events database, for example probaby 75% of the queries will be
current and future events. Folks will be interested in past events,
but usually as a part of a separate logical flow. Users may want past
events for research, but probably won't need to compare previous
events to current/future ones. (they might want to compare events all
in the past, for instance how many people attended each meeting,
average rating, etc; or they might want to compare current/future
events for conflicts. etc. But rarely both in the same query).
If you have reporting to do that might include both tables, you could
replicate them to MyISAM tables and make a MERGE table for your
reporting purposes.
Sorry this is so late; usually I get to MySQL list mail about once a
week, but the Users Conference took up a lot of my time! I hope this
helps....
-Sheeri
On 4/10/06, Douglas Sims <[EMAIL PROTECTED]> wrote:
Does anyone know of a thorough discussion of effective dating on the
web, particularly with respect to MySQL, or have any opinions you
could share?
I've worked with effective-dated tables in MS SQL Server and never
been particularly awe-struck by how well it works. I can think of
three ways of doing it:
1) Store a "Begin" date and an "End" date for each row and then
reference the row with 'WHERE {transaction date} BETWEEN {begin} AND
END. This inevitably winds up with overlapping rows that shouldn't
be or gaps where you don't want them, and also requires an extra date
column, but the select queries are simplest. Also, what about
indexing the dates?
2) Store an "Expires" date with each row, but then to find the actual
row you have to do a subselect or some messy joins and I'm not at all
confident this will be optimized reasonably.
3) Store an "Effective as of" date with each row but this has
essentially the same problem as 2.
None of the SQL books on my shelf even mentions this, including
Jeremy Zawodny's "Hi-Performance MySQL" and the "MySQL Reference
Manual."
This page is interesting but doesn't explain the different options
nor try to analyze which is best and under what circumstances: http://
llamasery.com/forums/showthread.php?p=34945
Strangely enough, most of what I find by googling the topic
"effective dating" has to do with meeting girls efficiently - which
is also interesting, but outside the scope of this list and not
immediately relevant to the system I'm working on.
Douglas Sims
[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]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.3/331 - Release Date: 03/05/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.3/331 - Release Date: 03/05/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
