History Tables

So, what do you do if you want to have high quality data (i.e. no fake dates for effective end dates) but don't really like to use columns that can contain nulls?  Well, for rows that contain effective dates, have you ever thought about using a history table?

If the vast majority of accesses to the table involve just the current data and not historical data, then a history table may solve your problems.  A history table contains all of the "old" rows and as such it will have an effective start date and an effective end date.  No need for nulls here as you precisely what these dates are.  As for the main table, depending upon the application, it may not even need any effective dates at all!!!!  Need the current address?  Just get it from the Address table.  Need an historical address?  Get it from the Address history table.  Going to be doing this a lot?  Put an index on the date/time fields.  (Sorry about that shameless plug for some other posts of mine.)

Is this effective date nirvana?  No, not really.  There are some applications that make effective use of historical data and for them a history table would only make things more complicated.  In other cases, you aren't really keeping track of history, what the effective start and end dates are being used for is for auditing who made what change on what date.  If what you want is audit information, then create an audit table.  Similar in concept to the history table but designed for auditing.

You see, it's not a sin to take a single table and make it two tables.  Indeed, there are really good reasons why you should.  But, if you aren't sure, talk to your DBA.  They can help you out, if only by asking you questions from a different perspective. That alone is worth the price of a visit.