nULL OR nOT?
-
I can't think of any good reason for doing this. May be because it's early morning (0633 hrs here). Let me make some tea (yes, tea and not coffee) and rethink.
"Bastards encourage idiots to use Oracle Forms, Web Forms, Access and a number of other dinky web publishing tolls.", Mycroft Holmes[^]
d@nish wrote:
Let me make some tea
Oh, tea is a must first thing in the morning. coffee is for waking you up when you start work:)
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
Mhm... depends (the typical answer of an engineer :))... in my main application, there are null values, due the fact, that I can query especially these dataset by searching for null values. I normally try to avoid null values to prevent any exceptions, but IMHO the rigorous obedience "null XOR not null" isn't suitable for all solutions...
_Maxxx_ wrote:
The argument I get is that "the customer does have an address, but we just don't know what it is yet."
Blocking -> "So what's the default value in case there isn't an address, and is it consistently used?" end of my two cents...
(yes|no|maybe)*
s_mon wrote:
So what's the default value in case there isn't an address, and is it consistently used?
Whatever it is, it is essentially a 'magic number' - and SQL provides one especially for the job. The only real exception I can think of that I use is when creating an object with a child collection - I don't have a collection property as Null, but as an empty collection - but that makes sense to me - this customer has no invoices rather than I don't know if this customer has any invoices or not. We had a real issue recently where the "null" record on the database managed to be edited by the user - and suddenly all the customers with no address all had the same address. Thank god we didn't do a mail out to everyone. Oh! wait!
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
d@nish wrote:
Let me make some tea
Oh, tea is a must first thing in the morning. coffee is for waking you up when you start work:)
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
Now that's confusing. I work from home and do not really follow any of the unsung laws (have a separate office, get away from disturbing things). It's OK for me as there is no one to disturb through the day. So, when exactly should I drink coffee? P.S. I like tea a lot more than coffee.
"Bastards encourage idiots to use Oracle Forms, Web Forms, Access and a number of other dinky web publishing tolls.", Mycroft Holmes[^]
-
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 am very restrictive allowing null values in database columns, but I don't try to replace the null values by some other kind of convention once I have loaded data rows into data objects. Not having to check for null in the program code is a small benefit which I gladly take, but that is not the main reason for this. I have seen databases where every column allowed nulls, including those which served as primary key without such a constraint being put on them. The importer, if it had a bad day, liked to insert rows into the database which consisted mostly of nulls. All this made the database one big source of useless data and errors. Validating a data row in which each and every value can be null already is a pain if it's done in one method and a code horror if it's done all over the application. Allowing nulls sparingly in the database helped a lot in getting this application in better shape: - Validation of data objects became easier (obviously). - The importer could not import junk anymore. Logging and solving the errors in the importer then led to a more useful database and the application had to deal with less error scenarios. - While they are no replacement for proper validation, constraints and restrictive use of nulls in the database provide another safeguard and the resulting errors give me the information I need to keep even bad applications running.
Sent from my BatComputer via HAL 9000 and M5
-
Now that's confusing. I work from home and do not really follow any of the unsung laws (have a separate office, get away from disturbing things). It's OK for me as there is no one to disturb through the day. So, when exactly should I drink coffee? P.S. I like tea a lot more than coffee.
"Bastards encourage idiots to use Oracle Forms, Web Forms, Access and a number of other dinky web publishing tolls.", Mycroft Holmes[^]
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')
-
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')
I do that before I call it a day. I decide what needs to be done tomorrow and based on that amount of sleep, time to get up and other things depend in next day.
"Bastards encourage idiots to use Oracle Forms, Web Forms, Access and a number of other dinky web publishing tolls.", Mycroft Holmes[^]
-
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 correct answer is that, usually, it doesn't really matter that much. For the properties of some entities it is necessary to distinguish between 'unset' and the default value. This can be achieved with a nullable data type/field or by using some sentinel value. The selection of that sentinel value is easy to select in some cases and difficult in others. In some systems using a well chosen sentinel value that is within the range of the data type used can save a lot of space because it doesnt require another bool to indicate null/non null. In others, and I would argue that almost any database would fall into this category, using an existing facility (dot net's Nullable<> or a nullable database field) is preferable as you can reasonably expect others familiar with the product / technology to instantly understand what it is and why it's there.
-
I am very restrictive allowing null values in database columns, but I don't try to replace the null values by some other kind of convention once I have loaded data rows into data objects. Not having to check for null in the program code is a small benefit which I gladly take, but that is not the main reason for this. I have seen databases where every column allowed nulls, including those which served as primary key without such a constraint being put on them. The importer, if it had a bad day, liked to insert rows into the database which consisted mostly of nulls. All this made the database one big source of useless data and errors. Validating a data row in which each and every value can be null already is a pain if it's done in one method and a code horror if it's done all over the application. Allowing nulls sparingly in the database helped a lot in getting this application in better shape: - Validation of data objects became easier (obviously). - The importer could not import junk anymore. Logging and solving the errors in the importer then led to a more useful database and the application had to deal with less error scenarios. - While they are no replacement for proper validation, constraints and restrictive use of nulls in the database provide another safeguard and the resulting errors give me the information I need to keep even bad applications running.
Sent from my BatComputer via HAL 9000 and M5
See, this is defensive programming. While I know it is necessary at times, that's no reason for it not to annoy the pants off me. My POV is that I should design my database, objects etc. "correctly" and trust developers to use them correctly - and if they don't, to shoot them in the kneecaps. From what you said, your Db had columns that should not have null values, but were set up to allow null values, and then had software inserting rows with null values in those columns. This sounds "bad" to me. One thing puzzles me - you said that validation was a problem if columns can be null - not sure why that would be? Do you mean within the application you're having to test for !=null a lot?
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
-
The correct answer is that, usually, it doesn't really matter that much. For the properties of some entities it is necessary to distinguish between 'unset' and the default value. This can be achieved with a nullable data type/field or by using some sentinel value. The selection of that sentinel value is easy to select in some cases and difficult in others. In some systems using a well chosen sentinel value that is within the range of the data type used can save a lot of space because it doesnt require another bool to indicate null/non null. In others, and I would argue that almost any database would fall into this category, using an existing facility (dot net's Nullable<> or a nullable database field) is preferable as you can reasonably expect others familiar with the product / technology to instantly understand what it is and why it's there.
_Josh_ wrote:
In some systems
You're right - it hadn't occurred to me, but I bet some of the 'don't use null' brigade are doing it because they aren't used to having nullable types in .Net and so have had to work around it in the past.
_Josh_ wrote:
The selection of that sentinel value is easy to select in some cases and difficult in others.
My point would be, though, why choose some sentinel value when the database provides one specifically for this purpose?
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')
null isn't taken up in the index (apparently it is know in Oracle 11g), so if there are many null values for a certain column, there's no use for an index. That put aside I wouldn't stop from using null. It has a meaning that says more then emtpy strings or unkown files.
-
_Josh_ wrote:
In some systems
You're right - it hadn't occurred to me, but I bet some of the 'don't use null' brigade are doing it because they aren't used to having nullable types in .Net and so have had to work around it in the past.
_Josh_ wrote:
The selection of that sentinel value is easy to select in some cases and difficult in others.
My point would be, though, why choose some sentinel value when the database provides one specifically for this purpose?
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
_Maxxx_ wrote:
My point would be, though, why choose some sentinel value when the database provides one specifically for this purpose?
Because the DB is (logically) storing two values, a bool indicating if the field is set and the actual value. I would imagine that this is highly optimised in your DB (in fact I bet IT uses sentinel values when possible) so not a problem. However, if you're trying to send 1.5 million messages / second between two processes you don't want to send a bool along with every value.
-
s_mon wrote:
So what's the default value in case there isn't an address, and is it consistently used?
Whatever it is, it is essentially a 'magic number' - and SQL provides one especially for the job. The only real exception I can think of that I use is when creating an object with a child collection - I don't have a collection property as Null, but as an empty collection - but that makes sense to me - this customer has no invoices rather than I don't know if this customer has any invoices or not. We had a real issue recently where the "null" record on the database managed to be edited by the user - and suddenly all the customers with no address all had the same address. Thank god we didn't do a mail out to everyone. Oh! wait!
MVVM # - I did it My Way ___________________________________________ Man, you're a god. - walterhevedeich 26/05/2011 .\\axxx (That's an 'M')
_Maxxx_ wrote:
We had a real issue recently where the "null" record on the database managed to be edited by the user - and suddenly all the customers with no address all had the same address.
Thank god we didn't do a mail out to everyone. Oh! wait!Let me guess "We haven't thought of that..." :rolleyes:
(yes|no|maybe)*
-
Mhm... depends (the typical answer of an engineer :))... in my main application, there are null values, due the fact, that I can query especially these dataset by searching for null values. I normally try to avoid null values to prevent any exceptions, but IMHO the rigorous obedience "null XOR not null" isn't suitable for all solutions...
_Maxxx_ wrote:
The argument I get is that "the customer does have an address, but we just don't know what it is yet."
Blocking -> "So what's the default value in case there isn't an address, and is it consistently used?" end of my two cents...
(yes|no|maybe)*
s_mon wrote:
So what's the default value in case there isn't an address, and is it consistently used?
Mornington Crescent! I win.
I wanna be a eunuchs developer! Pass me a bread knife!
-
s_mon wrote:
So what's the default value in case there isn't an address, and is it consistently used?
Mornington Crescent! I win.
I wanna be a eunuchs developer! Pass me a bread knife!
-
_Maxxx_ wrote:
My point would be, though, why choose some sentinel value when the database provides one specifically for this purpose?
Because the DB is (logically) storing two values, a bool indicating if the field is set and the actual value. I would imagine that this is highly optimised in your DB (in fact I bet IT uses sentinel values when possible) so not a problem. However, if you're trying to send 1.5 million messages / second between two processes you don't want to send a bool along with every value.
_Josh_ wrote:
Because the DB is (logically) storing two values
Well, I suppose you could say that it is logically but that's just describing what it achieves. In practice there is a specific property of a column (NULL) that describes its contents as having no value. I neither know, nor care, how it handles this internally. All I need to know is that, if a column is NULL, then that represents the fact that we do not know the value of that column. You seem to be saying that using a sentinal value may be preferable because the database might have to use some sort of boolean behind the scenes so that it knows the value is null? But surely better to let the Db do that ( after all, it is optimised specifically for that functionality) than it is for your application to continually test for some magic number. i.e. isn't
Select columns from table where column1 is not null
just plain better than
Select columns from table join specialValuesTable on table.column1 = specialValuesTable.Id
where specialValuesTable.RepresentsANullValue = 1Or even
Select columns from table where column1 <> -1
? And I agree - sending an additional bool is always an overhead, although depending on the method of communication, you may have to use some sentinel value - which may end up being larger than the overhead of sending a bit to represent null!
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')
_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!
-
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')
Silly people said:
the customer does have an address, but we just don't know what it is yet.
That is the worst reason ever, unless you really want to distinguish between "customers with an address that you don't know" and "customers without any address". If you don't (and you probably don't), then (apart from any technical reasons there may be) there's absolutely no difference between using a real null or a "pseudo null" like an empty string, because you're using both to mean "there's no thing here". Any additional meanings such as "customer is purely virtual and has no location in the universe" are purely imagined.
-
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)
-
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 null value is there for a good reason: to indicate that the record has no known value for that field. Using something which is a valid value for the column type in question is doing exactly the same thing as using a null, except that (i) it's confusing to someone who doesn't know that '', -1 etc is a 'fake null', and (ii) it can screw up aggregation (e.g. if you use 0 instead of NULL in a numeric column and then do an average over it). If it's valid for a piece of information to be unknown, it should be represented by a null value. There are people who are scared of nulls in the code-side development, as well. For a primitive type it might be fair enough for data size and convenience reasons (boxing primitive types into Integer or equivalent in Java, or using Nullable<int> in .Net, does have a cost, albeit small). But returning string.Empty when you mean 'unknown' is incorrect (that would mean it does have a value, and that value is empty), and results in hackery every bit as nasty as null checking when you want to check for it. The one place I do avoid nulls is with floating point types, where I slightly abuse NaN to mean 'missing value'. It has all the right semantics in arithmetic operations, it's native in the type and it's easy to check for in aggregations.
_Maxxx_ wrote:
The argument I get is that "the customer does have an address, but we just don't know what it is yet."
That's where a null is appropriate. When it's 'the customer has no address' then 'no address', which could reasonably be represented by empty if you want to maintain a difference between 'unknown' and 'none', should be represented by an actual value which means 'none'. Missing information is what null is for.
-
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')
Once again, this is all a failure of most databases to implement the relational model correctly. Codd's original model had two null values: A-Marks and I-Marks. An A-Mark (Missing and Applicable) indicates a value is unknown for the current object, but applicable to that object (e.g. age for a person). An I-Mark (Missing and In-applicable) indicates a value is not applicable to the current object, (e.g. commission for someone who does no sales activity). Missing Values in RDB[^]
"If you don't fail at least 90 percent of the time, you're not aiming high enough." Alan Kay.