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]