List:General Discussion« Previous MessageNext Message »
From:Douglas Sims Date:April 10 2006 4:32am
Subject:Effective-dating
View as plain text  
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
Doug@stripped



Thread
Effective-datingDouglas Sims10 Apr
  • Re: Effective-datingShawn Green10 Apr
  • Re: Effective-datingsheeri kritzer4 May
  • Re: Effective-datingRhino4 May