June 24, 2005 at 8:42 am
I have a table with a FromDate and ThruDate column. "Current" data is indicated by rows with a NULL ThruDate. Does this seem like a reasonable approach? Will I have problems if I index the ThruDate column? Any other caveats I should be aware of?
Thanks.
June 24, 2005 at 8:53 am
Create a compound index that is FromDate, ThruDate and then the null values on the ThruDate won't be a problem. Others will argue to set the ThruDate to the max value on the date date type you're using, but I'm not crazy about that. It will preclude this kind of code though:
@CurDate BETWEEN(FromDate AND COALESCE(ThruDate, @CurDate))
But even that's not that big a deal. I'm just against putting out data that isn't real, but others will provide their POV on that one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 24, 2005 at 9:59 am
NULL is a bad idea. I would use a DEFAULT value of '1900-01-01'. This way you deal with either a date value or a date value and not
WHERE Field BETWEEN start and end OR Field IS NULL but rather WHERE Field Between start and end OR Field = '1900-01-01'. Better performance, manageability, etc...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 24, 2005 at 10:55 am
My position is:
if you can avoid the use of nulls you must do it! nulls are bad and can give you many headaches when quering
* Noel
June 24, 2005 at 1:39 pm
..."But even that's not that big a deal. I'm just against putting out data that isn't real, but others will provide their POV on that one..."
I agree, I use null in this situation and it works just fine. It can be a small amount of extra work but not much. Using a default date can be confusing and get very messed up if someone uses the wrong default date.
I've learned to work with nulls and when you get used to it the nulls begin to work for not against you.
Just one persons opinion.
June 24, 2005 at 5:06 pm
my 2 cents worth.
IF you ALLOW NULL you have to determine: Is it really NULL or did someone TYPE NULL? I have been fighting NULL for a long time and have found very little VALID reasons for it.
Of course everyone can argue both sides of the coin so, it boils down to what you have been doing, personal preference and company policy...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 27, 2005 at 8:58 am
Delete from Suspect_List
Where blood_type <> 'O'
If Mr. Smith's blood type has not yet been determined and is set to NULL then he will correctly NOT be deleted.
If Mr. Smith's unknown blood type has been set to '' (empty string), then Mr. Smith will incorrectly be deleted from the list.
June 27, 2005 at 3:31 pm
Ron,
In answer to your Vampiric example:
How can you determine that IF his blood type IS NULL THEN NOT DELETING him is CORRECT?
Secondly, What is to stop someone from DELETE FROM Suspect_List WHERE Blood_Type IS NULL?
Third.. How can we properly determine Mr. Smith's blood type without tasting it? I mean testing it....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 27, 2005 at 4:00 pm
My preferred solution is to use an "archive table". Your current table would, by definition not have a ThruDate. When a row becomes inactive, DELETE it but use a DELETE trigger to write the data plus the thruDate to the archive table.
Now you have no ambiguity and a UNION view can easily be defined to retrieve records from either table that span any specified time period.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 28, 2005 at 8:32 am
I like the "archive table" approach a lot. I had considered doing something similar to provide an audit trail for some entities (more like a "shadow table"). The archive table seems a bit more straightforward as it can be indexed on the same key as the primary table. Thanks for the suggestion!
June 28, 2005 at 10:16 am
How can you determine that IF his blood type IS NULL THEN NOT DELETING him is CORRECT?
Because if you treat NULL as unknown then the correct answer is not to delete the row since you do not know at this time that Mr. Smith's blood type is NOT 'O'. You may still be waiting for test results to be entered.
This was just an attempt to show an example of how you can make nulls work for you and putting in artificial data can work against you. Everyone has their own philosophy in this area and that's ok. I was just trying to show an example the might help someone trying to decide how to handle this issue. It certainly wasn't meant as a personal attack.
Secondly, What is to stop someone from DELETE FROM Suspect_List WHERE Blood_Type IS NULL?
Nothing. But then what's to stop someone from deleting any or all rows? Not quite the issue of discussion.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply