NULL vs. Empty String
-
quote:
uff, and I thought while reading Patricks' post, the programmers of our human resources system ?!? are completely wrong using a termination date of 12/31/9999.
It's not a question of "right" or "wrong," just diferent opinions and, perhaps, styles. There's even a semantic argument to be made that a termination date is indeed "unknown" until the employee is terminated. I avoid using "flag" values, but a date of 99991231 provides a much better representation of infinity than one has with an int data type, and it certainly is a better representation of infinity than is NULL (which begs the question of how to represent division by zero
). To some extent, the choice may be driven by the way the data will be used. E.g., if users will need to know all employees who were employed for an entire year, this:
SELECT LastName, FirstName, SSNo
FROM Employees
WHERE HireDate < DATEADD(yy,@yr-1900,1)
AND TerminationDate > DATEADD(yy,@yr-1899,0)is easier to do than:
SELECT LastName, FirstName, SSNo
FROM Employees
WHERE HireDate < DATEADD(yy,@yr-1900,1)
AND (TerminationDate > DATEADD(yy,@yr-1899,0)
OR TerminationDate IS NULL)as users will not need to use ternary logic. And a query like:
SELECT LastName, FirstName, SSNo
FROM Employees
WHERE TerminationCode = 0or even:
SELECT LastName, FirstName, SSNo
FROM Employees e JOIN TerminationCodes t ON e.TerminationCode = t.TerminationCode
WHERE t.Description = 'Active'is more straightforward (to me, anyway) than:
SELECT LastName, FirstName, SSNo
FROM Employees
WHERE TerminationDate IS NULLas the latter requires "special knowledge" that TerminationDate of NULL means the employee has not been terminated. And, as I mentioned, you may need to represent the situation where someone has been terminated but the date is still being negotiated. In this case, if I was using the flag value of 99991231, I would probably still make the column not null, but I see using nulls as an exceptional need, not the norm, and in this my opinion differs from James'.
quote:
Now that Jonathan mentioned the Californian Governor Election I may ask the question if it advisable to keep calm with critic of the government in California?Do you have to fear hearing 'Hasta la vista, baby' and getting terminated
Well, Bustamente is still alive...
--Jonathan
Edited by - Jonathan on 10/22/2003 06:15:08 AM
--Jonathan -
I must confess I do really appreciate your 'nit-picking' style !!!
And please, don't get me wrong on this!
Looking forward to many clarifying ?!? posts!
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
grmph, haven't calculated the page break!
Anyway, the above post goes into Jonathan's direction.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
If you are using smalldatetime you could only use 20790606. What are the chances that they will be using this database in 2079?
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
-
quote:
If you are using smalldatetime you could only use 20790606. What are the chances that they will be using this database in 2079?Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
With some of the folks in my company, pretty good. And quite possibly that date is already in use.
-
quote:
If you are using smalldatetime you could only use 20790606. What are the chances that they will be using this database in 2079?remember that managers are to buy a program. And only these special kind of human beings have the entrepreneurial ability to see the whole picture. Everyone, by definition, else, lacks this.
So why should a manager buy something which he is going to replace tomorrow (speaking in his time dimensions)?
"It ain't what you know that kills; it's what you know that ain't so
that kills!"
...aargh too much contact with this species...
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
If you are using smalldatetime you could only use 20790606. What are the chances that they will be using this database in 2079?Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
The major reason why I don't use smalldatetime. Also, my grandmother was born in the nineteenth century (yes, I'm old
).Using smalldatetime means you have created the possibility of another Y2K-type problem, although it will certainly be someone else's problem.
I plan to do a lot of redesign once the "date only, no time" datatype is available with the next release; just think: no time component to worry about, no 2079 issue, and fewer bytes.
--Jonathan
--Jonathan -
quote:
I guess I disagree with James ("Antares686") on this; I am loathe to use nullable columns and create them only when I need the "unknown" logic. I certainly won't allow NULL to be used when the value is instead empty. E.g. I might make a City column in an address table nullable and not allow blanks, but I would make the Name column not null and not allow blanks. I might make the PhoneNumber column nullable and allow blanks (not everyone has a phone). That way one could query for entities with no phones or for entities where we don't know their phone numbers. A problem with allowing both blanks and nulls, as I wrote in another thread, is making clear the distinction in the data-entry front-end.--Jonathan
But that also probably comes froma choice of datatypes and normalization.
For example you mention phones. Fortunately I deal with US number only so I stick with numeric(10) keep it in a closed datatype so I guarantee numeric's only.
Also, I tend to take items that would have heavy amoounts of nulls and split tables there. I have in fact one situation where of 10 million records only 2 million had data in a field, so I add and delete data from another related table.
If you create a situation where such as blank can be in the column then I will go with blank as default unless it is a non-fixed length datatype such as VARCHAR. The I will null the column myself to conserve space (watse not want not kinda guy).
I also prefer the 3 logic method with even CHARs thou as blank means nothing and NULL will always be unknown. If a person removed the data unless we choose otherwise then the data is no longer valid and for instances where maybe an address has changed there is a new address unknown box to NULL those as well.
But all in all it boilds down to choice and logic and best way to determine data. Maybe I should have said make sure whether or not a NULL is appropriate for your situation.
-
quote:
quote:
uff, and I thought while reading Patricks' post, the programmers of our human resources system ?!? are completely wrong using a termination date of 12/31/9999.as the latter requires "special knowledge" that TerminationDate of NULL means the employee has not been terminated. And, as I mentioned, you may need to represent the situation where someone has been terminated but the date is still being negotiated. In this case, if I was using the flag value of 99991231, I would probably still make the column not null, but I see using nulls as an exceptional need, not the norm, and in this my opinion differs from James'.
--Jonathan
And just to add my spin for variance of opinion I would make it null for the reason it creates a new logic need in your code and something that better be well documented for anyone who has to work with in the future.
If you run
select EmployeeID from tbl_Employees where term_date > GETDATE()
to find people with termination dates in the future.
May be contractors for the company with a contract expire date are given a termination date so automatic removal of security/server access occur.
If you of course you don't know 99991231 is a non-termination then you get everyone who isn't terminated.
So a programmer hooking need to know to add
AND term_date != 99991231
this is likely to be forgotten and could (depending on the situation) cause undesired effects.
As for employees being terminated but no date available I would add a bit field to signify that type of situation as logicall a programmer coming behind me without documentation should be able to figure it out as long as I us a good naming convetion.
But if I did 99991231 there the likelyhood of someone realizing what I did goes way down unless this is an enforced standard in databases. But then I had to code out those as well makin it harder for me.
-
That's what I love about this forum. Thanks. I took the first solution, NULL for term_date, but this discussion has opened another variation which has its own possibilities and problems.
That's all I ask.
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
-
RE: Term_Date = 20790606 or 99991231.
Just because we (presumably) won't be around on either of those dates doesn't mean they're safe to use. Somebody may be. Come that date, I would not care to be the person having to explain to the boss why everyone was terminated today.
Forgive me, but this sounds a lot like "let's use only 2 digits for the year because it's expedient (read cheap in that case)". No, for me, that column is NULLable, and NULL in this case, as in many, means "Not Applicable".
P
-
Doing time waiting for the weekend I found this source to the ultimate computer language
http://www.baetzler.de/humor/null.html
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
). To some extent, the choice may be driven by the way the data will be used. E.g., if users will need to know all employees who were employed for an entire year, this:
).Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply