nULL OR nOT?
-
I've come across a practice in more than one company where one or more develpers insit on not using null values in the database. So, for example, if a customer address is unknown, the AddressId on the Customer table will have a value of (for example) zero - and the Address table will be pre-populated with a record with an Id of zero and (usually) empty string or zero values in the other columns. I've also come across the situation where, rather than having a null value in a 'sold price' column, the value of zero represents an unsold item. Putting aside my feelings (that these are weak-minded imbeciles who shouldn't be allowed near a database) do any of you subscribe to this theory? What's wrong with a null value which tells you explicitly "The customer has no address" or "This item has no price" The argument I get is that "the customer does have an address, but we just don't know what it is yet." I put it down to FUD factors - but am willing to change my view if anyone can give a sound reason for any of this garbage.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
Is it possible they have simply read or heard that nulls are bad and so eradicate them with defaut values without understanding the database principles behind the statement? (I regularly use nulls in database design, normalising every last one away is rarely a worth it). Greg
-
d@nish wrote:
So, when exactly should I drink coffee?
At 8:50 - just before your scrum meeting. (I generally drink more tea than coffee just to reduce my caffeine and calorie intake - I drink earl gray tea, black nothing added) You do have a scrum meeting with yourself, don't you?
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
Darjeeling, black, then attend to correspondence. The coffee comes mid morning when the code starts happening. Lucky enough to have an espresso machine at the office, so it's usually a cappuccino for me :cool:
-- For a moment, nothing happened. Then, after a second or so, nothing continued to happen --
-
I've come across a practice in more than one company where one or more develpers insit on not using null values in the database. So, for example, if a customer address is unknown, the AddressId on the Customer table will have a value of (for example) zero - and the Address table will be pre-populated with a record with an Id of zero and (usually) empty string or zero values in the other columns. I've also come across the situation where, rather than having a null value in a 'sold price' column, the value of zero represents an unsold item. Putting aside my feelings (that these are weak-minded imbeciles who shouldn't be allowed near a database) do any of you subscribe to this theory? What's wrong with a null value which tells you explicitly "The customer has no address" or "This item has no price" The argument I get is that "the customer does have an address, but we just don't know what it is yet." I put it down to FUD factors - but am willing to change my view if anyone can give a sound reason for any of this garbage.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
Yes, your example is FUD tactic for wrong practice. NULL is standard, my counter questions for those programmers is: "How do you handle three state logic and do you enforce "standards of yours" across all of your projects?"
Mislim, dakle jeo sam.
-
I've come across a practice in more than one company where one or more develpers insit on not using null values in the database. So, for example, if a customer address is unknown, the AddressId on the Customer table will have a value of (for example) zero - and the Address table will be pre-populated with a record with an Id of zero and (usually) empty string or zero values in the other columns. I've also come across the situation where, rather than having a null value in a 'sold price' column, the value of zero represents an unsold item. Putting aside my feelings (that these are weak-minded imbeciles who shouldn't be allowed near a database) do any of you subscribe to this theory? What's wrong with a null value which tells you explicitly "The customer has no address" or "This item has no price" The argument I get is that "the customer does have an address, but we just don't know what it is yet." I put it down to FUD factors - but am willing to change my view if anyone can give a sound reason for any of this garbage.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
The main reason for doing this, especially on FK's, is that you don't have to worry about what kind of join you're doing. If you have nulls, you need to usually use an outer join rather than a full join or an inner join. Otherwise, it's sort of a semantic difference to use 0 instead of a null, but that can also be really dangerous. Null means something which is different from zero, or any other value.
_Maxxx_ wrote:
but am willing to change my view if anyone can give a sound reason for any of this garbage.
My reason regarding the joins should be moot - any decent SQL generator should know to use the correct join when dealing with nullable FK's. On the other hand, people are lazy. Not a good enough reason to adopt their policies, IMO. Marc
Testers Wanted!
Latest Article: User Authentication on Ruby on Rails - the definitive how to
My Blog -
I've come across a practice in more than one company where one or more develpers insit on not using null values in the database. So, for example, if a customer address is unknown, the AddressId on the Customer table will have a value of (for example) zero - and the Address table will be pre-populated with a record with an Id of zero and (usually) empty string or zero values in the other columns. I've also come across the situation where, rather than having a null value in a 'sold price' column, the value of zero represents an unsold item. Putting aside my feelings (that these are weak-minded imbeciles who shouldn't be allowed near a database) do any of you subscribe to this theory? What's wrong with a null value which tells you explicitly "The customer has no address" or "This item has no price" The argument I get is that "the customer does have an address, but we just don't know what it is yet." I put it down to FUD factors - but am willing to change my view if anyone can give a sound reason for any of this garbage.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
NULL is not indexed use NULL in arithmetic, string, logical and others operations is incorrect its use is the result of a incorrect designing database.
-
I am against using nulls in the database because it means introducing 3 valued logic, which is known to be bad. The behavior of SELECT and conditions when nulls are involved is too complicated.
Rosenne wrote:
it means introducing 3 valued logic, which is known to be bad.
Known by whom? If there are three logical values then you need to use 3 valued logic!
Rosenne wrote:
The behavior of SELECT and conditions when nulls are involved is too complicated.
Because you don't understand it is not a good reason for not using it - it simply means you should not be designing databases.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
NULL is not indexed use NULL in arithmetic, string, logical and others operations is incorrect its use is the result of a incorrect designing database.
Igor Kovalev wrote:
NULL is not indexed
In which database? My understanding is that oracle while not actually indexing nulls, will still utilise an index on a nullable column. And I think SQL Server does index nulls.
Igor Kovalev wrote:
use NULL in arithmetic, string, logical and others operations is incorrect
Interesting statement? says who? Incorrect why, in what way?
Igor Kovalev wrote:
its use is the result of a incorrect designing database.
so. pray tell, why do databases even HAVE a NULL? If it's so incorrect to use it, surely databases sholdn't support it?
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
Igor Kovalev wrote:
NULL is not indexed
In which database? My understanding is that oracle while not actually indexing nulls, will still utilise an index on a nullable column. And I think SQL Server does index nulls.
Igor Kovalev wrote:
use NULL in arithmetic, string, logical and others operations is incorrect
Interesting statement? says who? Incorrect why, in what way?
Igor Kovalev wrote:
its use is the result of a incorrect designing database.
so. pray tell, why do databases even HAVE a NULL? If it's so incorrect to use it, surely databases sholdn't support it?
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
1)SELECT on the table that has a value of NULL will result in FULL SCAN 2)try null = null or null + 4 3)look for database normalization
-
_Maxxx_ wrote:
The argument I get is that "the customer does have an address, but we just don't know what it is yet."
Which is actually correct, and quite a nice way of stating a technical point in plain language. The empty string represents an unset address, whereas null indicates that there is no address. Unsurprisingly, most people don't think "does a field have a value or not?", they think "do we know the address?". Only developers are brought up to detach data from reality, and DBAs don't have to be developers, but they do have to know their data.
I wanna be a eunuchs developer! Pass me a bread knife!
There is something inherently wrong with a product that has not been assigned a price being defaulted to zero. I actually had a customer order his "free" device because it showed up in the catalog with a price of zero. If it were null, it would have missed the query. Some fields should allow null, some must allow null and yet others must not.
-
1)SELECT on the table that has a value of NULL will result in FULL SCAN 2)try null = null or null + 4 3)look for database normalization
1. Not true - check your facts. 2. Sorry - I see what you mean - yes, certainly it is incorrect to try to do arithmetic on 'nothing' so, let's say you have a numeric column on your table and, instead of using null to represent it not having a value, you use zero. Now, what is the average of that column? You can't tell which rows to count because you are not differentiating between rows who's value is zero and rows that do not have a value. 3. Mate, I've been doing database normalisation longer than you've been alive; if you have a point to make, make it! you said "its use is the result of a incorrect designing database." So you are saying you should not design a database to have null columns? Unless I am misunderstanding what you are saying, then you imply that the NULL 'value' is not necessary!
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
1. Not true - check your facts. 2. Sorry - I see what you mean - yes, certainly it is incorrect to try to do arithmetic on 'nothing' so, let's say you have a numeric column on your table and, instead of using null to represent it not having a value, you use zero. Now, what is the average of that column? You can't tell which rows to count because you are not differentiating between rows who's value is zero and rows that do not have a value. 3. Mate, I've been doing database normalisation longer than you've been alive; if you have a point to make, make it! you said "its use is the result of a incorrect designing database." So you are saying you should not design a database to have null columns? Unless I am misunderstanding what you are saying, then you imply that the NULL 'value' is not necessary!
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
I ve been doing database normalisation longer than you've been alive;" why you discuss about this issue with me? The use of NULL is true for Oratsle but not true for all DB. In another DB empty string is it not NULL
-
I ve been doing database normalisation longer than you've been alive;" why you discuss about this issue with me? The use of NULL is true for Oratsle but not true for all DB. In another DB empty string is it not NULL
Igor Kovalev wrote:
why you discuss about this issue with me?
Because you are stating as facts things that are not true, and you are responding to my initial question about the use of nulls
Igor Kovalev wrote:
The use of NULL is true for Oratsle but not true for all DB.
Oracle I assume you mean? And you mean that Nulls are not indexed in Oracle? Yes, I understand that that is true. I also understand it is not true for all databases.
Igor Kovalev wrote:
In another DB empty string is it not NULL
sorry, I don't understand? Appreciate English probably is your 2nd language; I don't believe in any database an empty string is null. Null has a special meaning - i.e. 'there is no value'
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
Igor Kovalev wrote:
why you discuss about this issue with me?
Because you are stating as facts things that are not true, and you are responding to my initial question about the use of nulls
Igor Kovalev wrote:
The use of NULL is true for Oratsle but not true for all DB.
Oracle I assume you mean? And you mean that Nulls are not indexed in Oracle? Yes, I understand that that is true. I also understand it is not true for all databases.
Igor Kovalev wrote:
In another DB empty string is it not NULL
sorry, I don't understand? Appreciate English probably is your 2nd language; I don't believe in any database an empty string is null. Null has a special meaning - i.e. 'there is no value'
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
yes it's my secondary language.
I am sorry for the misuse. about equals empty string and null...
https://db.apache.org/torque/torque-3.3/version-specific/database-howtos/oracle-howto.html
you'll be surprised -
I've come across a practice in more than one company where one or more develpers insit on not using null values in the database. So, for example, if a customer address is unknown, the AddressId on the Customer table will have a value of (for example) zero - and the Address table will be pre-populated with a record with an Id of zero and (usually) empty string or zero values in the other columns. I've also come across the situation where, rather than having a null value in a 'sold price' column, the value of zero represents an unsold item. Putting aside my feelings (that these are weak-minded imbeciles who shouldn't be allowed near a database) do any of you subscribe to this theory? What's wrong with a null value which tells you explicitly "The customer has no address" or "This item has no price" The argument I get is that "the customer does have an address, but we just don't know what it is yet." I put it down to FUD factors - but am willing to change my view if anyone can give a sound reason for any of this garbage.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
RIGHT, sounds like some very old DBA's whose time is before Null became popular/sensible. An address, espicially if it is the first record, should not allow null; a person can have more than one address, so let the second/third,etc. allow nulls. Yes, I am having my tea. The front end issues can be resolved by checking for nulls in the stored proc CRUD's and sending error messages out instead of garbage; avoiding lot's of metadata on what the default values should be; today a zero, tomorrow an empty string. I use Null in all of my query's/procs, it is a must for any DBA or Programmer; consistancy that everyone can agree on. As long as your using it and checking for it, apps won't crash because of unkowns yet to be know and the database won't fill up with garbage, espicially ID's. I struggle all the time with poor app/database design, sigh, guess thats why we get the big bucks. Hang in there and don't let the bastards make you use MS Access, lol. ;)
-
I've come across a practice in more than one company where one or more develpers insit on not using null values in the database. So, for example, if a customer address is unknown, the AddressId on the Customer table will have a value of (for example) zero - and the Address table will be pre-populated with a record with an Id of zero and (usually) empty string or zero values in the other columns. I've also come across the situation where, rather than having a null value in a 'sold price' column, the value of zero represents an unsold item. Putting aside my feelings (that these are weak-minded imbeciles who shouldn't be allowed near a database) do any of you subscribe to this theory? What's wrong with a null value which tells you explicitly "The customer has no address" or "This item has no price" The argument I get is that "the customer does have an address, but we just don't know what it is yet." I put it down to FUD factors - but am willing to change my view if anyone can give a sound reason for any of this garbage.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
There's a ton of DB research on the subject. Read up on CJ Date and the third manifesto as they're what most of the "nulls are bad" papers are based off of. It has been a while since my database theory courses but the best reason I remember is dealing with joins. Since joining on a null value gets dropped from the resulting set, you may miss data. For instance you may think that you have 200 records in the database because some complicated join returned just 200 items but you're joining on a column with 100 nulls, thus you miss 100 records. Hence the idea that people should try to avoid nulls. That said if you reorganize the database to avoid that, you almost always have to do left outer joins or unions or some other crazy approach to get all the data anyway which reintroduces the nulls... So instead your DB people went the default value approach. It's actually clever as long as it's well documented that they did that.
-
I've come across a practice in more than one company where one or more develpers insit on not using null values in the database. So, for example, if a customer address is unknown, the AddressId on the Customer table will have a value of (for example) zero - and the Address table will be pre-populated with a record with an Id of zero and (usually) empty string or zero values in the other columns. I've also come across the situation where, rather than having a null value in a 'sold price' column, the value of zero represents an unsold item. Putting aside my feelings (that these are weak-minded imbeciles who shouldn't be allowed near a database) do any of you subscribe to this theory? What's wrong with a null value which tells you explicitly "The customer has no address" or "This item has no price" The argument I get is that "the customer does have an address, but we just don't know what it is yet." I put it down to FUD factors - but am willing to change my view if anyone can give a sound reason for any of this garbage.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
I guess it depends on the application. I've run into more than my share of field errors where "invalid use of null" came up so I came to hate it. To me, an unitialized string is "", int is 0 etc. Having said that I can see uses for null, but having to test for it all the time can be a pain. If your GUI is written well, though, it should ensure that all appropriate initializations are made when creating a new record. I guess the fact that I've had to fix all these bad GUI's has driven my distaste of null. If the GUI is coded properly, null can be useful as a data validation.
-
I've come across a practice in more than one company where one or more develpers insit on not using null values in the database. So, for example, if a customer address is unknown, the AddressId on the Customer table will have a value of (for example) zero - and the Address table will be pre-populated with a record with an Id of zero and (usually) empty string or zero values in the other columns. I've also come across the situation where, rather than having a null value in a 'sold price' column, the value of zero represents an unsold item. Putting aside my feelings (that these are weak-minded imbeciles who shouldn't be allowed near a database) do any of you subscribe to this theory? What's wrong with a null value which tells you explicitly "The customer has no address" or "This item has no price" The argument I get is that "the customer does have an address, but we just don't know what it is yet." I put it down to FUD factors - but am willing to change my view if anyone can give a sound reason for any of this garbage.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
Two situations: 1. DB only solutions, with no/little code interfacing the DB...Nulls are ok. 2. Code driven DBs, *never* allow nulls. Use a default value for every datatype that represents null. Why? Programmer hours are EXPENSIVE. Servers are CHEAP. If your programmers are constantly typing (and supporting bugs caused by) NOT NULL...you are throwing money down the drain. Same goes for null objects, never allow a null object to exist at runtime. Never allow nulls for code driven DBs.
Robert
-
I've come across a practice in more than one company where one or more develpers insit on not using null values in the database. So, for example, if a customer address is unknown, the AddressId on the Customer table will have a value of (for example) zero - and the Address table will be pre-populated with a record with an Id of zero and (usually) empty string or zero values in the other columns. I've also come across the situation where, rather than having a null value in a 'sold price' column, the value of zero represents an unsold item. Putting aside my feelings (that these are weak-minded imbeciles who shouldn't be allowed near a database) do any of you subscribe to this theory? What's wrong with a null value which tells you explicitly "The customer has no address" or "This item has no price" The argument I get is that "the customer does have an address, but we just don't know what it is yet." I put it down to FUD factors - but am willing to change my view if anyone can give a sound reason for any of this garbage.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
I see it just to avoid the handling of null in the code... it some approach it's dummy safe.... It helps to avoid the need to handle the nulls, when like you are doing XMLs or Serialization that can have issues with null values. Don't know the source of it, but when I'm doing a C# Serializatoin to XML, then I read that XML in Reporting Services, whenever the Serialization generates the fields for values, the Reporting Services just becomes crazy, don't know how to handle the data and then I see weird data on the Reports. Then I end adding IF, or HasValue to simple data generation. If you think outside all machines 16GB ram and 8 cores, any extra process you can remove will help in performance, then its good to not use Nulls and use String.Empty or Default values in this type of cases. Plus, when you do LinQ, the handling of Nulls makes the queries a little hard to read and write. But don't be confused, both approach if they fit your problem are fine. I don't see here big impact in DB storage, not sure about performance, but I'm sure that if you use NULLs, you must know how to work with NULLS (IS NULL not the same as = NULL)
-
I've come across a practice in more than one company where one or more develpers insit on not using null values in the database. So, for example, if a customer address is unknown, the AddressId on the Customer table will have a value of (for example) zero - and the Address table will be pre-populated with a record with an Id of zero and (usually) empty string or zero values in the other columns. I've also come across the situation where, rather than having a null value in a 'sold price' column, the value of zero represents an unsold item. Putting aside my feelings (that these are weak-minded imbeciles who shouldn't be allowed near a database) do any of you subscribe to this theory? What's wrong with a null value which tells you explicitly "The customer has no address" or "This item has no price" The argument I get is that "the customer does have an address, but we just don't know what it is yet." I put it down to FUD factors - but am willing to change my view if anyone can give a sound reason for any of this garbage.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
Where possible, don't have nullable database fields. Empty strings or 0's are preferred. We often write classes in .Net to back database objects and having to deal with DBNull.Value is an extra pain. Usually we make the types Nullable which ends up poisoning the whole code base, or define them as Object which is stupid in a strongly typed language. Only where it makes sense, like if it's a foreign key and some objects don't have it, then make it NULL. "
I've also come across the situation where, rather than having a null value in a 'sold price' column, the value of zero represents an unsold item.
" Yeah, that's dumb. Use NULL for the special meaning of "no sale". NULL values are good for sentinel values. "
So, for example, if a customer address is unknown, the AddressId on the Customer table will have a value of (for example) zero - and the Address table will be pre-populated with a record with an Id of zero and (usually) empty string or zero values in the other columns.
" Also dumb, that is going to come back and bite you in the ass eventually. But I'm sure you already anticipate that :)
-
As I think the main reason to avoid nulls is this... I'm quite alien dbs, but I do serialize data in XML and other formats. I use an MVVM approach (your article is great, thanks!) and the operations that perform on the selected object may not check for null values(stupid, viewmodels and moron designers!) and hence poof!, a crash. So we are adviced not to use nulls, rather a default value of the model. Most common approaches include string.Empty, File.Unknown, Address.Unknown (static, custom instances)
Amitosh Swain wrote:
the operations that perform on the selected object may not check for null values(stupid, viewmodels and moron designers!) and hence poof!, a crash.
If you attempt to fix this problem by removing nulls you'll find you still have a problem with your program; it'll be displaying and\or using incorrect data. Removing nulls won't fix the root cause of your problems, it will just change its nature, and since the nature of problems using nulls is a crash or exception, bugs which are very easy to trace and fix, i don't know why you'd want to change it.