nULL OR nOT?
-
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.
-
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')
Read Tony Hoare's "Null References: The Billion Dollar Mistake". He argues quite strongly against the NULL pointer, and although I don't always follow his ideas, I mostly agree.
-
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.
I think you're spot on. It's not a case of NULL or not; but when to use NULL. NULL is a database representation of does not have data. The best example I can think of (with one cup of coffee) is a column for income. NULL value means we don't know the income (yet); 0 means they have an income of zero. This would apply equally well to your price column. Allowing or disallowing a NULL is based on the business logic of the program. Can you enter a customer with no address? You allow NULL in those fields. However if your business logic REQUIRES an address (or e-mail...). Then NULL is not allowed; a value must be provided.
-
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 answer to that is to fix the VM code, though. A view model which crashes when given a null has a serious bug, imo.
If you don't use Nulls, the database will be a lot bigger. Moreover, if you have a number field and all numbers are allowed, what option do you have other than using Nulls if you don't have a value for that particular field? The only pain with Nulls is that any program reading the database has to be able to cope with Nulls and if it can't, it's badly written.
-
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 can't speak to your DBMS, but on MySQL a nullable column has an implicit default of NULL, and so are not required by INSERTs. If you want to make sure that the developer always sets that column, you cannot use NULL. If the column is NOT NULL and the developer INSERTs without the column, MySQL will complain that the column has no default value, but if they include the column with a fake-null sentinel value, MySQL will let that through. This means that NOT NULL requires the developer to intentionally set the value to "null", never by accident. I've had occasion to use this technique, but only with columns that really, really must be filled in and only rarely have the value of the fake null. Usually a null is and should be used, and this technique reserved for when it is absolutely necessary. As always, it's about knowing when to use a feature, not about outright outlawing said feature.
-
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')
Most code that I have seen requires extra statements for each and every column to check for and handle a null, or the program will crash with an unpleasant and unnecessary error. This is a big burden when you don't even need to know programmatically that no data was entered and you don't even need to display such information other than an empty text box to the user. In too many cases, frameworks won't even give you a way of doing this. In that case, I make sure that the framework only accesses the data through a database view in which nulls are automatically converted to empty strings or zeros, depending on the field. It makes the rest of the code so much simpler. Nulls are good in the rare instances when you really need to know whether or not the field has been entered or not. Otherwise, they are a big nuisance.
-
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 am genuinely horrifid by responses like this. What you are essentially saying is that you shouldn't use features which debs might make mistakes with - stick by that and you'll use no linq, no anonymous types etc etc. And yes, programmer hours are expensive - SOS design it right and program it right, don't design it wrong in case you make mistakes! Learn to program properly!
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
I am genuinely horrifid by responses like this. What you are essentially saying is that you shouldn't use features which debs might make mistakes with - stick by that and you'll use no linq, no anonymous types etc etc. And yes, programmer hours are expensive - SOS design it right and program it right, don't design it wrong in case you make mistakes! Learn to program properly!
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
Wow, that was an overemotional and unjustified response. This pattern has worked well for a decade, and I see no reason to change. My current architecture is based on EF 5, MVC 4, Windows Store apps and SQL 2012. I use 90% linq, 10% hand written SPs for the complicated quieries. We have about 2.5 million lines of code dependent on this architecture. We have never had a null reference error thrown in production, thanks to this technique. My developers never check for nulls, in either DB calls nor in object calls. Ever. Our reusable foundation handles all of this automatically. And can differentiate between a null default value, and a "Not Selected" value. In just one of our smaller projects, a programmer would have had to type != null about 100 times. What a waste of resources.
Robert
-
Most code that I have seen requires extra statements for each and every column to check for and handle a null, or the program will crash with an unpleasant and unnecessary error. This is a big burden when you don't even need to know programmatically that no data was entered and you don't even need to display such information other than an empty text box to the user. In too many cases, frameworks won't even give you a way of doing this. In that case, I make sure that the framework only accesses the data through a database view in which nulls are automatically converted to empty strings or zeros, depending on the field. It makes the rest of the code so much simpler. Nulls are good in the rare instances when you really need to know whether or not the field has been entered or not. Otherwise, they are a big nuisance.
I agree. I think this is where I perhaps haven't expressed myself well. I think you should never allow nulls in a column where there is no difference between, say, a null value and an empty string as far as the business logic goes. But where there is a difference, using 'special values' is the wrong approach. But where something does not have a value, giving it a value so that the program doesn't crash is putting the cart before the horse (or something like that) I mean, if a column can be null, that means a null value represents some case, and so the code needs to deal with that.
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
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 think a couple people have given reasoning behind never using a null. I will only say that they ignore one problem for another. Now I will say that I have also seen the opposite. Where a developer is too lazy to and allows nulls even where the value should never be null. I prefer to consider the issue on a case by case basis. As an example if I am tracking people than their birthdate can be null if (and only if), the business allows moving forward with nothing in this field. But say we track when they were added. This should never be 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')
For Date field, I use Null value if it is unknown. But for address field, I use empty string if it is unknown. It is because empty string address is almost equivalent to no address but if you put 1900-01-01 or 2999-01-01 as unknown date, it is not so good.
petersgyoung