i wonder if one should have NULL in the fields with no value, or should it be empty?
what is best?
thanks
+1
A:
There is a certain argument that you should never allow NULL in your data, if you are using it to indicate that you don't know what the value should be or that you just don't have that data yet then use an explicit value in the field to indicate those states. Similarly for 'empty' fields. That said, I think everyone does it or has done it and may do it again. NULL has odd comparative properties which is why it's always best, if you can, to avoid it and have explicit values for missing data states.
Lazarus
2010-04-28 11:07:29
so its better than leave fields blank if they are not set cause they are optional?
never_had_a_name
2010-04-28 11:28:57
It's better to have "None" in the field rather than an empty string or NULL as it's possible that in the future someone else looking at the data may have a different interpretation of what it means.
Lazarus
2010-04-28 11:59:45
but isn't it best practice to set NULL. i think i've read it somewhere
never_had_a_name
2010-04-28 12:18:46
coincidentally related to the current question from(i'll just paste the comment here): http://stackoverflow.com/questions/2540981/whats-the-purpose-of-the-empty-string **@Max Toro, an example. What did you eat for breakfast? Before the question, the answer is unknown. Null. If you were to answer and audibly say "nothing", then the data representation of that is the empty string. Before the question, the answer is "value is not known." After the question (and answer), the value is emtpy. – Anthony Pegram Mar 29 at 20:50**
Michael Buen
2010-04-29 01:17:05
I agree with Max in one aspect, NULL = unknown, the answer 'nothing' audible or otherwise should result in the response 'nothing' being recorded, an empty string would be no response at all and is almost as ambiguous as NULL. How can you be sure in the future that it was meant to be empty or if data has been lost? Data is something, not nothing. We all use empty strings and NULL but that doesn't make it the right answer, in fact, who knows what it means 6 months after you used it. I'd love to see an example where '' is better than 'None' or 'Nothing' or 'N/A'.
Lazarus
2010-04-30 13:37:25
+7
A:
NULL means that no data is set, while empty string could be some valid data.
Thus, using NULL helps you to differentiate these two cases.
Dmitry Yudakov
2010-04-28 11:10:08
cause some fields are optional, eg. they dont have to type in their cellphone numbers. its better with NULL in those fields then or should it be blank?
never_had_a_name
2010-04-28 11:29:59
if it's important to you if blank number is entered or no number entered at all (and if you able to differentiate it), use NULL. Otherwise blank value will do.
Dmitry Yudakov
2010-04-28 13:19:23
+2
A:
From a programming standpoint, I try to not allow null values for a few reasons. One of which is that code often has a bad reaction to unexpected NULL values. If a query filter ran faster checking null values I might consider using them but there is no evidence of this I have experienced. But I have experienced many a function which pooped out on doing some kind of comparison not testing for NULL before hand.
RandyMorris
2010-04-28 12:39:56
+1
A:
Avoid NULLs in base tables whenever three valued logic is likely to come back to bite you. That's easy to say, but lengthy to explain. Three valued logic can sometimes be successfully managed, but your intuition is likely to be based on two valued logic, and can be misleading.
If you avoid NULLS in base tables, but create views or queries with outer joins, be prepared to deal with NULLS. NULLS in fields that are never used in where clauses and never used "incorrectly" with aggregates (as in sum(FIELD)) are OK.
NULL fields are always empty, but empty doesn't always imply NULL. In particular, an empty or non existent field in a form can translate into a non NULL value in a table. Autonumber fields are an example.
Oracle made a mistake way back in the 1980s by using the same representation for the VARCHAR string of length zero (the empty string), and NULL. They've been about to fix it "real soon now" for a quarter of a century. Don't hold your breath.
Don't use NULLs to convey a meaningful message. This almost always confuses your colleagues, even when they deny it.
Walter Mitty
2010-04-28 13:58:57
A:
Nulls are necessary amd important tools in dataase design. If you don'tknow the value at the time the record is inseerted, null is entirely appropriate and the best practice. Making an unknon into a known value such as empty string is silly. This especially true when you get away from string data into dates or numeric data. 0 is not the same as null, some arbitrary date far in the past or future is not the same as null. For that matter empty strings means there is no value, null means we don't know what the value is. This is an important distinction.
It's not hard to handle nulls, any competent programmer should be able to do so.
HLGEM
2010-04-28 19:39:09