Re: avoiding use of Nulls (was: The = operator)
2009/3/14 mich...@j3ksolutions.com: [...] So instead of thinking that I am an idiot, try using your intelligence and try to understand what I am really talking about! ... instead, try to point us to a chapter of a book or take your time and write a short paper, 'cause my (stupid) intelligence suggests me that (maybe) you are the one that is not able to express his ideas *clearly* Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls (was: The = operator)
This is from an application design perspective, not while you a creating a
table or constructing an insert statement.
I do not think it is possible to design an application not caring about the
data model it relies on.
And I am sorry if people like me who write the code need to add 'IS NOT
NULL' to every conditional statement in our logic.
If you control your code, after retrieving the values from the database ,
you should have something like this:
if ($row['temperature'] != ) { this values are reallly good}
you just moved the NULL values handling from the database to the
application.
also everyone in the SQL world would really love to have always all the
information on everything,
but it seems that it is not always possible.
So instead of thinking that I am an idiot, try using your intelligence and
try to understand what I am really talking about!
Now, if you really want this conversation to be constructive, just reflect
on this statement:
Your fake value for you has the same use of NULL, you just do not like
the NULL handling syntax.
Trying to use my intelligence I can only cite you the Codd third rule:
*Rule 3:* *Systematic treatment of null values*:
The DBMS must allow each field to remain null (or empty). Specifically, it
must support a representation of missing information and inapplicable
information that is
systematichttp://en.wikipedia.org/w/index.php?title=Systematicaction=editredlink=1,
distinct from all regular values (for example, distinct from zero or any
other number, in the case of numeric values), and independent of data
typehttp://en.wikipedia.org/wiki/Data_type.
It is also implied that such representations must be manipulated by the DBMS
in a systematic way.
If you are wondering who Codd is I can anticipate you that he invented the
things we are talking about
(http://en.wikipedia.org/wiki/Edgar_F._Codd)
Cheers
Claudio Nanni
Re: avoiding use of Nulls (was: The = operator)
On Sun, 15 Mar 2009 20:19:51 +0100 Claudio Nanni said:
This is from an application design perspective, not while you a creating a
table or constructing an insert statement.
snip
If you control your code, after retrieving the values from the database ,
you should have something like this:
if ($row['temperature'] != ) { this values are reallly good}
you just moved the NULL values handling from the database to the
application.
more snipage
So instead of thinking that I am an idiot, try using your intelligence and
try to understand what I am really talking about!
Now, if you really want this conversation to be constructive, just reflect
on this statement:
Your fake value for you has the same use of NULL, you just do not like
the NULL handling syntax.
snip again
If you are wondering who Codd is I can anticipate you that he invented the
things we are talking about
(http://en.wikipedia.org/wiki/Edgar_F._Codd)
Cheers
Claudio Nanni
And Claudio for the WIN!
--
Don Read donr...@sbcglobal.net
It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fwd: avoiding use of Nulls
Think! I'm not talking about how you application is designed. If your applications allows an employee to be entered without a position in the company, it is a bad design. If a manager at my company hired someone, and did not know what work the new-hire would be doing, well I'd really need to ask why they were hire, right before I fired the manager for hiring someone for no apparent reason. No fake row needed, a dept is required! So far the weather guy has the best example of using nulls, and again, I'd receive a null from the remote weather bug, but I wouldn't put that garbage in my database! I would NOT use - for the missing dept, my application would flat-out require a department. If the data entry clerk wants to add some dummy department because they hire lots of people for no reason, then so be it. Better example anyone? And FAX numbers, what about home phone, not many of those lately either. Notice that phone numbers in general are an odd sort, especially because of the variant types around the world. So phone numbers should be strings, not numeric. Therefore I think blank spaces would be reality. Uh, whats there FAX number? It's blank. That means they don't have a FAX number. But if you decide to use Null, then in binary terms it is uninitialized garbage. It adds to your development time, your debugging time, and your processing time. Not to mention the possibility of inconsistent results when retrieving data. Same applies to middle names, you simply need to think about each field of data, and what it really means to the application. You guys who disagree on this: I know that this is what you were taught in school, and I know all the uses for null values. I just think the teaching is wrong. Sometimes teachers don't think, and most of the time students don't think. But when you become experienced, you should think. And finally: The words of Tony Hoare: The man who developed the most widely used sort algorithm around the world. Tony said: I call it my billion-dollar mistake. It was the invention of the null reference in 1965. At that time, I was designing the first comprehensive type system for references in an object oriented language (ALGOL W). My goal was to ensure that all use of references should be absolutely safe, with checking performed automatically by the compiler. But I couldn't resist the temptation to put in a null reference, simply because it was so easy to implement. This has led to innumerable errors, vulnerabilities, and system crashes, which have probably caused a billion dollars of pain and damage in the last forty years. It's unbelievable that nobody agrees with me, or even sees my point, Mike. Exactly the point. Michael, NULL *is* information. It means unknown and that is in itself useful information. A common example: A new employee is hired but which department she will work in is unknown. So the data entry person enters all the known information and leaves the rest until it has been clarified. The alternative is even worse than the so-called NULL problem and the alleged difficulty of querying against NULLable columns: in a case like that described above, the only way to handle it is to create a fake row in the foreign-key table, for Department Zero or somesuch. That immediately turns every query into a more complex beast that it would otherwise have been. Every single query must exclude this zeroth row; join a few tables all exhibiting this problem and things get really crazy. Not to mention the fact that these zeroth rows falsify reality and combine fiction with fact. Even worse, the -9 approach means that you can't impose a constraint on the column (such as must be a positive integer. And finally, I cannot believe that you really mean no NULLS ever. Surely you mean only FKs. Otherwise, how would you handle fax numbers for people with no fax, or middle names for people with none? Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls (was: The = operator)
snip I agree with most everything you said, as far as the use of a RDBMS, and especially: quoting you, theoretically you break up your data into tables so that there is never a non-applicable field for any given row. Here is where I disagree with you, and please DON'T take this as an insult (we're just haggling over good/bad practices ), No Insult or offense taken. Sorry for opening this can of worms ;) but I think it is lazy database design when you say: In the real world, this can lead to a lot of complexity, so many people usually cheat and have a few does not apply fields for some records. OK! Now here is my top 5 explanations why using Nulls is a bad idea: Explanation(5): The more you understand how the database is to be used, and the more complexity and thought you put into your database design, the less complex it will be to retrieve reliable information out of it. Furthermore, (and this is probably what makes me crazy when Nulls are evolved) after a ten year stretch of software development, where I and a team designed our own databases, I did a nine year stretch of statistical programming, using databases designed by other people, and Nulls in the data made the results unpredictable, and yeah, made me crazy! I had to write nightly processes to resolve inconsistencies in the data, if at least report inconsistencies. You know the old saying Garbage in = Garbage out, to me Nulls are garbage, and if there is a good reason for nulls to be a part of good clean data then someone please help me understand that. Explanation(4):I think that the brilliant computer scientists who developed the RDBMS database model, didn't want to support nulls initially, but they had to because null is a form of data, it means We don't know what this is, and so it must be stored knowing that Null should NOT be used as valid information. Explanation(3):I think it was back in the sixties at Berkley, when some smart guy on LSD found a use for the Null value, and now this use of Null is taught in Universities all over the world, and considered to be a blessed method to practice. Explanation(2): Using a Null column, and you have an index on that column, the index will not be used since NULL values do not get stored in an index and that is why the optimizer will need to go to the data pages in order to retrieve more information to suffice the requirements of the query. Explanation(1): I'm just in a argumentative mood today. I'm staying out of the debate on numbers 3,4 and 5, but I am watching with interest. Number 2 is very good to know and I will make note of that. as for number 1, well ...? Cheers and have a great weekend! Mike. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls (was: The = operator)
Hi everybody I want to join as well.
But hypothetically speaking, what value would you use if you didn't have a
I don't what this is value like null?
I ask this because I started programming when NULL was really zero, and
part of the ASCII collating sequence.
NULL was zero
I'd use -9., I'd never allow a i don't know what it is value
like Null in my database.
NULL is NO INFORMATION ON THIS VALUE, and having no information happens in
real life.
The message I do not have information on this value it is an important
information itself.
The question is: Will I allow a record to contain a 'non-informative' field
?
[a] Ok, go ahead.
[b] No, set the field as NOT NULL
If you choose [b] please assume your responsibilities.
Everytime you insert a record, either you have all the information at once
or use your fake,
fantasy value {0,-9.,-1,PIPPO}.
If I read your record about the weather in Amsterdam and I read a
temperature of -9.
I will definitely think your database is unreliable.
Cheers
Claudio
Re: avoiding use of Nulls (was: The = operator)
I certainly appreciate this discussion and the clear opinions of Mike. Somehow it turned my somewhat simple question into something much more interesting. I believe in referential integrity, I also believe that enforcing a fully saturated data model like Mike proposes, is a constraint on data modeling that is not acceptable for me. Orphans exist in my (data modeling) world, and arguing about the validity of that will probably just end up in discussing contrived examples. Anyway, thanks for sharing your opinions Mike. Oracle could have used you when the implemented the empty string... :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls (was: The = operator)
2009/3/13 mich...@j3ksolutions.com: I have a database logging weather data. When a station does not report a temperature, it is set to NULL. It would be a very bad idea to set it to 0 as this would ruin the whole statistics. NULL is a perfectly valid information in many cases. I'd use -9., I'd never allow a i don't know what it is value like Null in my database. what about a sensor that reports -9. when it is broken or the temperature is out of range for it? How could you express this information in this case? Just changing your fake-NULL value to -8. would be something *horrible*. I perfectly agree with Thomas, and I would expand his idea: - sensor working: integer value between -250~+32767 - sensor now working: integer value less than -250 - sensor unavailable: NULL I think that the advantages of this kind of architecture are easy to understand. More generally, NULL means (in this case): I cannot write a *value* because I have no informations, instead, -9. is a *value*! In a previous e-mail on this thread you wrote: NULL is garbage or something like this, but think: -9. is garbage. NULL is something we all agree, in general, it means I don't know. Going back to the fax-number example, you wrote (if I correctly remember) that you would use some blanks, instead ... well, I would use a single zero character, and another could use some other value it would be a disaster! How could you avoid this? Greetings, Mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls (was: The = operator)
Claudio Nanni wrote:
Hi everybody I want to join as well.
The question is: Will I allow a record to contain a 'non-informative'
field
?
No! thats not the question!
[a] Ok, go ahead.
[b] No, set the field as NOT NULL
If you choose [b] please assume your responsibilities.
Everytime you insert a record, either you have all the information at once
or use your fake,
fantasy value {0,-9.,-1,PIPPO}.
If I read your record about the weather in Amsterdam and I read a
temperature of -9.
I will definitely think your database is unreliable.
No I would not allow this - value to be viewed by the end user. And
you guys take a hypothetical example and think I want to use that
everywhere.
My point is about using null or not.
In a single data, obviously there will some data required, and some data
optional, and I am not talking about the required data, I am talking about
the optional data, and that null is the worst choice for an initial value.
This is from an application design perspective, not while you a creating a
table or constructing an insert statement.
So instead of thinking that I am an idiot, try using your intelligence and
try to understand what I am really talking about!
Mike.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fwd: avoiding use of Nulls
It's a good thing, then, that we've got your experience to rely on. Woe is us, for not having any, and not seeing how obviously right you are. You're entitled to your opionion on NULLs, but kindly stop spamming *my* mailbox with it. I was aware of your dislike for them after the first mail. On Sat, Mar 14, 2009 at 8:24 AM, mich...@j3ksolutions.com wrote: Think! I'm not talking about how you application is designed. If your applications allows an employee to be entered without a position in the company, it is a bad design. If a manager at my company hired someone, and did not know what work the new-hire would be doing, well I'd really need to ask why they were hire, right before I fired the manager for hiring someone for no apparent reason. No fake row needed, a dept is required! So far the weather guy has the best example of using nulls, and again, I'd receive a null from the remote weather bug, but I wouldn't put that garbage in my database! I would NOT use - for the missing dept, my application would flat-out require a department. If the data entry clerk wants to add some dummy department because they hire lots of people for no reason, then so be it. Better example anyone? And FAX numbers, what about home phone, not many of those lately either. Notice that phone numbers in general are an odd sort, especially because of the variant types around the world. So phone numbers should be strings, not numeric. Therefore I think blank spaces would be reality. Uh, whats there FAX number? It's blank. That means they don't have a FAX number. But if you decide to use Null, then in binary terms it is uninitialized garbage. It adds to your development time, your debugging time, and your processing time. Not to mention the possibility of inconsistent results when retrieving data. Same applies to middle names, you simply need to think about each field of data, and what it really means to the application. You guys who disagree on this: I know that this is what you were taught in school, and I know all the uses for null values. I just think the teaching is wrong. Sometimes teachers don't think, and most of the time students don't think. But when you become experienced, you should think. And finally: The words of Tony Hoare: The man who developed the most widely used sort algorithm around the world. Tony said: I call it my billion-dollar mistake. It was the invention of the null reference in 1965. At that time, I was designing the first comprehensive type system for references in an object oriented language (ALGOL W). My goal was to ensure that all use of references should be absolutely safe, with checking performed automatically by the compiler. But I couldn't resist the temptation to put in a null reference, simply because it was so easy to implement. This has led to innumerable errors, vulnerabilities, and system crashes, which have probably caused a billion dollars of pain and damage in the last forty years. It's unbelievable that nobody agrees with me, or even sees my point, Mike. Exactly the point. Michael, NULL *is* information. It means unknown and that is in itself useful information. A common example: A new employee is hired but which department she will work in is unknown. So the data entry person enters all the known information and leaves the rest until it has been clarified. The alternative is even worse than the so-called NULL problem and the alleged difficulty of querying against NULLable columns: in a case like that described above, the only way to handle it is to create a fake row in the foreign-key table, for Department Zero or somesuch. That immediately turns every query into a more complex beast that it would otherwise have been. Every single query must exclude this zeroth row; join a few tables all exhibiting this problem and things get really crazy. Not to mention the fact that these zeroth rows falsify reality and combine fiction with fact. Even worse, the -9 approach means that you can't impose a constraint on the column (such as must be a positive integer. And finally, I cannot believe that you really mean no NULLS ever. Surely you mean only FKs. Otherwise, how would you handle fax numbers for people with no fax, or middle names for people with none? Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fwd: avoiding use of Nulls
Arthur Fuller wrote: Exactly the point. Michael, NULL *is* information. It means unknown and that is in itself useful information. A common example: A new employee is hired but which department she will work in is unknown. So the data entry person enters all the known information and leaves the rest until it has been clarified. In this case, you could have a table to link the employee to department. If you didn't know what department an employee was going to be in, there would be no record in that table. However I agree with Mike, why are you hiring someone if you don't know what they are going to be doing. Also having the separate table to link employees and departments allows for a many to many relationship, so an employee can work in more than one department. I'll leave the discussion for how bad an idea that is for another list :) Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
avoiding use of Nulls (was: The = operator)
On Friday 13 March 2009 09:48:36 Michael wrote: I'm sorry for the rant, but nulls in databases make me see red! NULLS ARE GARBAGE! You are using a 'NULL-safe equal to operator', meaning, WHERE NOT group_id = 3, and oh, by the way, I DON'T WANT ANY NULLS TO QUALIFY AS 3. And yes this will be much slower, because now we can't use the index on group_id. Null is not an initial value, it is really saying that nothing has ever been put into this field, item, or what-ever. It is uninitialized and no one can say exactly what is in it, this is why it is marked as null. Using the null-safe equal to operator seems wrong, of course too me having any nulls in your data is wrong, or just plain dirty data, and I wouldn't put any faith into the results from a database that contains nulls, because the output is unpredictable if it is not very carefully coded. You (everyone in the SQL world) would be better off using the IS NULL and IS NOT NULL operators and the IFNULL() function to find all null values in their data, and initialize them to the appropriate initial value. This may require discussions with the application designers or project managers. Whom ever is responsible for these null values being your data all the frecking time? It is people that don't really understand what a null value is, and who also have the responsibility of designing a database application. So people like me who write the code need to add 'IS NOT NULL' to every conditional statement in our logic. What you want to do noted in your email below should be very simple, but only if you have CLEAN DATA. I would clean or what we call scrub your data first, then you can execute a simple and very fast select statement. Use a temporary table if you need to, just don't try to process dirty data, the end results will only make you look bad to anyone looking at the results. My $0.02, Mike. This is news to me, and I'd like to understand, so please don't take this as a flame or anything. I think I understand this part: When designing the database structure, you don't want just one table with a million columns, most of which are empty for any given row. What you do is break it up into several tables, with a relationship between the tables. Thus the term Relational database management system (RDBMS) theoretically you break up your data into tables so that there is never a non- applicable field for any given row. In the real world, this can lead to a lot of complexity, so many people usually cheat and have a few does not apply fields for some records. Now, what I've been doing is using NULL for does not apply as it seems to be more true to the data model than using some sort of fake data. I freely admit that Nulls increase the complexity of the code, but isn't it justified? I would be interested in your explanation. Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls (was: The = operator)
On Friday 13 March 2009 09:48:36 Michael wrote: I'm sorry for the rant, but nulls in databases make me see red! NULLS ARE GARBAGE! You are using a 'NULL-safe equal to operator', meaning, WHERE NOT group_id = 3, and oh, by the way, I DON'T WANT ANY NULLS TO QUALIFY AS 3. And yes this will be much slower, because now we can't use the index on group_id. Null is not an initial value, it is really saying that nothing has ever been put into this field, item, or what-ever. It is uninitialized and no one can say exactly what is in it, this is why it is marked as null. Using the null-safe equal to operator seems wrong, of course too me having any nulls in your data is wrong, or just plain dirty data, and I wouldn't put any faith into the results from a database that contains nulls, because the output is unpredictable if it is not very carefully coded. You (everyone in the SQL world) would be better off using the IS NULL and IS NOT NULL operators and the IFNULL() function to find all null values in their data, and initialize them to the appropriate initial value. This may require discussions with the application designers or project managers. Whom ever is responsible for these null values being your data all the frecking time? It is people that don't really understand what a null value is, and who also have the responsibility of designing a database application. So people like me who write the code need to add 'IS NOT NULL' to every conditional statement in our logic. What you want to do noted in your email below should be very simple, but only if you have CLEAN DATA. I would clean or what we call scrub your data first, then you can execute a simple and very fast select statement. Use a temporary table if you need to, just don't try to process dirty data, the end results will only make you look bad to anyone looking at the results. My $0.02, Mike. This is news to me, and I'd like to understand, so please don't take this as a flame or anything. I think I understand this part: When designing the database structure, you don't want just one table with a million columns, most of which are empty for any given row. What you do is break it up into several tables, with a relationship between the tables. Thus the term Relational database management system (RDBMS) theoretically you break up your data into tables so that there is never a non- applicable field for any given row. In the real world, this can lead to a lot of complexity, so many people usually cheat and have a few does not apply fields for some records. Now, what I've been doing is using NULL for does not apply as it seems to be more true to the data model than using some sort of fake data. I freely admit that Nulls increase the complexity of the code, but isn't it justified? I would be interested in your explanation. Ray I agree with most everything you said, as far as the use of a RDBMS, and especially: quoting you, theoretically you break up your data into tables so that there is never a non-applicable field for any given row. Here is where I disagree with you, and please DON'T take this as an insult (we're just haggling over good/bad practices ), but I think it is lazy database design when you say: In the real world, this can lead to a lot of complexity, so many people usually cheat and have a few does not apply fields for some records. OK! Now here is my top 5 explanations why using Nulls is a bad idea: Explanation(5): The more you understand how the database is to be used, and the more complexity and thought you put into your database design, the less complex it will be to retrieve reliable information out of it. Furthermore, (and this is probably what makes me crazy when Nulls are evolved) after a ten year stretch of software development, where I and a team designed our own databases, I did a nine year stretch of statistical programming, using databases designed by other people, and Nulls in the data made the results unpredictable, and yeah, made me crazy! I had to write nightly processes to resolve inconsistencies in the data, if at least report inconsistencies. You know the old saying Garbage in = Garbage out, to me Nulls are garbage, and if there is a good reason for nulls to be a part of good clean data then someone please help me understand that. Explanation(4):I think that the brilliant computer scientists who developed the RDBMS database model, didn't want to support nulls initially, but they had to because null is a form of data, it means We don't know what this is, and so it must be stored knowing that Null should NOT be used as valid information. Explanation(3):I think it was back in the sixties at Berkley, when some smart guy on LSD found a use for the Null value, and now this use of Null is taught in Universities all over the world, and considered to be a blessed method to practice. Explanation(2): Using a Null column, and you have an index on that
Re: avoiding use of Nulls (was: The = operator)
On Fri, 13 Mar 2009, mich...@j3ksolutions.com wrote: Explanation(5): The more you understand how the database is to be used, and the more complexity and thought you put into your database design, the less complex it will be to retrieve reliable information out of it. Furthermore, (and this is probably what makes me crazy when Nulls are evolved) after a ten year stretch of software development, where I and a team designed our own databases, I did a nine year stretch of statistical programming, using databases designed by other people, and Nulls in the data made the results unpredictable, and yeah, made me crazy! I had to write nightly processes to resolve inconsistencies in the data, if at least report inconsistencies. You know the old saying Garbage in = Garbage out, to me Nulls are garbage, and if there is a good reason for nulls to be a part of good clean data then someone please help me understand that. Hi I'm in a argumentative mood today too. :-) I have a database logging weather data. When a station does not report a temperature, it is set to NULL. It would be a very bad idea to set it to 0 as this would ruin the whole statistics. NULL is a perfectly valid information in many cases. Cheers Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls (was: The = operator)
On Fri, 13 Mar 2009, mich...@j3ksolutions.com wrote: Explanation(5): The more you understand how the database is to be used, and the more complexity and thought you put into your database design, the less complex it will be to retrieve reliable information out of it. Furthermore, (and this is probably what makes me crazy when Nulls are evolved) after a ten year stretch of software development, where I and a team designed our own databases, I did a nine year stretch of statistical programming, using databases designed by other people, and Nulls in the data made the results unpredictable, and yeah, made me crazy! I had to write nightly processes to resolve inconsistencies in the data, if at least report inconsistencies. You know the old saying Garbage in = Garbage out, to me Nulls are garbage, and if there is a good reason for nulls to be a part of good clean data then someone please help me understand that. Hi I'm in a argumentative mood today too. :-) I have a database logging weather data. When a station does not report a temperature, it is set to NULL. It would be a very bad idea to set it to 0 as this would ruin the whole statistics. NULL is a perfectly valid information in many cases. Cheers Thomas OK! I do understand, thank you. But hypothetically speaking, what value would you use if you didn't have a I don't what this is value like null? I ask this because I started programming when NULL was really zero, and part of the ASCII collating sequence. I'd use -9., I'd never allow a i don't know what it is value like Null in my database. Mike. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls
ggghh mich...@j3ksolutions.com wrote: On Fri, 13 Mar 2009, mich...@j3ksolutions.com wrote: Explanation(5): The more you understand how the database is to be used, and the more complexity and thought you put into your database design, the less complex it will be to retrieve reliable information out of it. Furthermore, (and this is probably what makes me crazy when Nulls are evolved) after a ten year stretch of software development, where I and a team designed our own databases, I did a nine year stretch of statistical programming, using databases designed by other people, and Nulls in the data made the results unpredictable, and yeah, made me crazy! I had to write nightly processes to resolve inconsistencies in the data, if at least report inconsistencies. You know the old saying Garbage in = Garbage out, to me Nulls are garbage, and if there is a good reason for nulls to be a part of good clean data then someone please help me understand that. Hi I'm in a argumentative mood today too. :-) I have a database logging weather data. When a station does not report a temperature, it is set to NULL. It would be a very bad idea to set it to 0 as this would ruin the whole statistics. NULL is a perfectly valid information in many cases. Cheers Thomas OK! I do understand, thank you. But hypothetically speaking, what value would you use if you didn't have a I don't what this is value like null? I ask this because I started programming when NULL was really zero, and part of the ASCII collating sequence. I'd use -9., I'd never allow a i don't know what it is value like Null in my database. Mike. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls
mich...@j3ksolutions.com wrote: On Fri, 13 Mar 2009, mich...@j3ksolutions.com wrote: Explanation(5): The more you understand how the database is to be used, and the more complexity and thought you put into your database design, the less complex it will be to retrieve reliable information out of it. Furthermore, (and this is probably what makes me crazy when Nulls are evolved) after a ten year stretch of software development, where I and a team designed our own databases, I did a nine year stretch of statistical programming, using databases designed by other people, and Nulls in the data made the results unpredictable, and yeah, made me crazy! I had to write nightly processes to resolve inconsistencies in the data, if at least report inconsistencies. You know the old saying Garbage in = Garbage out, to me Nulls are garbage, and if there is a good reason for nulls to be a part of good clean data then someone please help me understand that. Hi I'm in a argumentative mood today too. :-) I have a database logging weather data. When a station does not report a temperature, it is set to NULL. It would be a very bad idea to set it to 0 as this would ruin the whole statistics. NULL is a perfectly valid information in many cases. Cheers Thomas OK! I do understand, thank you. But hypothetically speaking, what value would you use if you didn't have a I don't what this is value like null? I ask this because I started programming when NULL was really zero, and part of the ASCII collating sequence. I'd use -9., I'd never allow a i don't know what it is value like Null in my database. Mike. Somewhere out there, Achilles is gaining on the turtle -- unheralded genius: A clean desk is the sign of a dull mind. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Fwd: avoiding use of Nulls
Exactly the point. Michael, NULL *is* information. It means unknown and that is in itself useful information. A common example: A new employee is hired but which department she will work in is unknown. So the data entry person enters all the known information and leaves the rest until it has been clarified. The alternative is even worse than the so-called NULL problem and the alleged difficulty of querying against NULLable columns: in a case like that described above, the only way to handle it is to create a fake row in the foreign-key table, for Department Zero or somesuch. That immediately turns every query into a more complex beast that it would otherwise have been. Every single query must exclude this zeroth row; join a few tables all exhibiting this problem and things get really crazy. Not to mention the fact that these zeroth rows falsify reality and combine fiction with fact. Even worse, the -9 approach means that you can't impose a constraint on the column (such as must be a positive integer. And finally, I cannot believe that you really mean no NULLS ever. Surely you mean only FKs. Otherwise, how would you handle fax numbers for people with no fax, or middle names for people with none? Arthur
Re: avoiding use of Nulls
On Fri, Mar 13, 2009 at 3:20 PM, Andy Wallace awall...@cisdata.net wrote: ggghh mich...@j3ksolutions.com wrote: On Fri, 13 Mar 2009, mich...@j3ksolutions.com wrote: Explanation(5): The more you understand how the database is to be used, and the more complexity and thought you put into your database design, the less complex it will be to retrieve reliable information out of it. Furthermore, (and this is probably what makes me crazy when Nulls are evolved) after a ten year stretch of software development, where I and a team designed our own databases, I did a nine year stretch of statistical programming, using databases designed by other people, and Nulls in the data made the results unpredictable, and yeah, made me crazy! I had to write nightly processes to resolve inconsistencies in the data, if at least report inconsistencies. You know the old saying Garbage in = Garbage out, to me Nulls are garbage, and if there is a good reason for nulls to be a part of good clean data then someone please help me understand that. Hi I'm in a argumentative mood today too. :-) I have a database logging weather data. When a station does not report a temperature, it is set to NULL. It would be a very bad idea to set it to 0 as this would ruin the whole statistics. NULL is a perfectly valid information in many cases. Cheers Thomas OK! I do understand, thank you. But hypothetically speaking, what value would you use if you didn't have a I don't what this is value like null? I ask this because I started programming when NULL was really zero, and part of the ASCII collating sequence. I'd use -9., I'd never allow a i don't know what it is value like Null in my database. Mike. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fuller.art...@gmail.com
