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')
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.
-
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 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.
-
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
-
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.
You shouldn't be telling me this... tell them to the moron coders those who write the viewmodel. I don't see any reason not to use a null.
-
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.
Here, Here. You should always allow nulls unless you logical reasons backed up by specifications and have default values backed up by processes to ensure nothing goes wrong. The example about the sell price defaulting to zero is a great example of where it will bite you in the butt. I can understand you ask for a full name and no middle name was given so in a database containing a first, middle and last name there is a blank middle name. I see someone saying you shouldn't allow nulls in an address field because the person in the database has an address but it isn't known yet. But what if you have a job seeker database and have homeless people sign up? Also if you suspect the person has an address but don't know it then it is an UNKNOWN address which a null value represents which code can identify and prompt someone to get the information. So unless you know everything you are always going to have some sort of null value in a database somewhere and instead of trying to pretend you know everything (and cause more hassles for everyone else) leave it null. One database I know of uses 2019 as a default date for some records so if I want to look for data entry errors in the date field I have to start filtering out other records because they have not been completed yet.
-
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')
NULLs in databases is like gotos in many programming languages and friends in C++. The feature is there but many try not to use it because they consider it bad practice. I use them because I feel that they were put in for a reason: to make life easy or to get round a problem which would take a whole load of restructuring to fix. In the maintenance world, when programs aren't written to be maintained, this isn't always a good idea. Someone has gone through the trouble of putting them in and the compiler/database writer has gone through the trouble of coding them in so why not use them? It is like jaywalking instead of using the zebra/pelican/puffin/toucan/pegasus crossing that the local council has put in. Hmmm, I wonder how many people in the UK know about toucan and pegasus crossings.
-
NULLs in databases is like gotos in many programming languages and friends in C++. The feature is there but many try not to use it because they consider it bad practice. I use them because I feel that they were put in for a reason: to make life easy or to get round a problem which would take a whole load of restructuring to fix. In the maintenance world, when programs aren't written to be maintained, this isn't always a good idea. Someone has gone through the trouble of putting them in and the compiler/database writer has gone through the trouble of coding them in so why not use them? It is like jaywalking instead of using the zebra/pelican/puffin/toucan/pegasus crossing that the local council has put in. Hmmm, I wonder how many people in the UK know about toucan and pegasus crossings.
Member 4608898 wrote:
NULLs in databases is like gotos in many programming languages
I couldn't disagree more!
Member 4608898 wrote:
I use them
If you are telling me you use "GOTO" then I would never employ you as a programmer. Just because a feature is there doen't mean it NEDS to be used - it just means it is available for use should the design require it.
Member 4608898 wrote:
It is like jaywalking instead of using the zebra/pelican/puffin/toucan/pegasus crossing
well - I see what you are getting at - but it's only useful if yo actually need to cross the road in the first place
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')
Could be the flip side.. with Oracle DB an empty string ('') is treated as null. It makes it hard to have null for truly unknown values and '' for a known but empty value. It is kind of like have a paper form with a line not filled in (was it left blank on purpose as the answer or was it just unavailable).
-
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')
If you find developers that insist on not using NULLs in a DB don't do anything to discourage them from that mindset as far too many developers use NULLs lie the government wastes money, too often and without any thought as to what impact they might have. Q:What’s wrong with null? A: Nothing, so long as you use it properly. A NULL is an unknown value and not necessarily the absence of a value. This often is confusing to the traditional programmer accustom to working with languages with defaults or implied values. Within a RDBMS such as SQL Server, you use a NULL when a column/field of a table may or may not contain a value either because the value is unknown at the time the row is created (INSERTED into the table) or because it’s a field that may or may not ever have a value. Date/Time data types in SQL are a perfect example of why NULLS are even allowed. Not every Date/Time is known at the time of creation of the row of data. In the case of a DB that tracks apartment residences lease history, we may not know the future tenants Move In date when we add the record to the table. What then do specify for the column/field that stores the Move In date? The answer is NULL and that’s because we don’t yet know the move in date. Its not that there is no move in date, we simply don’t yet know the move in date. Q:What about non-date data types such as numeric values? What’s wrong with using a NULL there? A: Using the same Tenants database example…. When a record is inserted for a new tenant and a null is placed in the filed/column that stores the rent amount are we telling the DB that the rent is unknown or that there is no rent at all? A value of 0 would more accurately describe ‘No rent’ but many may view the use of NULL to imply no rent. It’s the ‘implied’ part that makes usage of NULLS so problematic for anyone not within the DBA/DB Architect community. BOTTOM LINE: NULL is meant to represent ‘UNKNOWN’ in a RDBMS and if you stick to using NULLs this way you’ll have far less difficulty with NULLS in your DB/App.