What's wrong with Nulls?

What's wrong with Nulls?

I've been told that it's not good design to allow nulls in field definitions... I know it can cause errors... but can somebody please elaborate and explain under which circumstances I shouldn't allow nulls and why. What's the theory, what's best practice? I want to understand.

EDIT

My dilema is that I have to make db changes now, and the code will be implemented later... so I'm concerned about creating new fields NOT NULL and risking non-field-specific INSERT statements failing. This is the area where I'm not clear. I want to allow NULL because I think it's less risky, but others are telling me it's bad design. At this stage I'm more concerned about minimising risk when the code is implemented. I'd appreciate if anyone would help clarify my choices for me. I'm lacking conviction and I need to make a decision soon.

Answer by tokam for What's wrong with Nulls?

If you do not allow null values, you ensure that you only have complete saved data. Data with nulls makes working with the data difficult for you.

For example if you have books to display, it will lead to problems you have to handle if you allow null values on the title.

If you are new to databases, and if you are maybe doing webdevelopment, have a look at doctrine. It generates your innoDB mysql database (or other databases) from code for you.

Answer by user unknown for What's wrong with Nulls?

That's too simple. In many cases you don't know - for example - if a person has children. NumberOfChilds = 0 means: He has zero children, NULL means: you don't know.

But if you can make information mandatory in the whole process, it is good to enforce it on the deepest level.

Allowing NULL is better than having customers circumvent your logic, and marking a non-null-field with '-1 child', to indicate "don't know yet". Now you're lost if you take the sum. Or everybody invents his own secret code to patch your system.

Answer by Jacob for What's wrong with Nulls?

As so often happens in computer science, this is not as simple as "never use feature X".

The basic theory is that a SQL NULL is not supposed to be a value taken on by a field---instead, it indicates that a value is unknown. In other words, if you have a people table and the first person's name is NULL, this indicates that his name is unknown, not that he has no name.

The problem is that this violates the "law of the excluded middle", which states that a proposition is either true or false---once you introduce NULL, there is a third truth value of "unknown". This can lead to various tricky logic-related bugs.

In practice, it's not all that common to construct a schema that has to accommodate NULL values, but it certainly happens. So the short answer is that you should require that all your fields be NOT NULL unless you have a very good reason to do otherwise.

There's a fairly exhaustive discussion of this whole issue on the appropriate Wikipedia page.

Answer by Esteban Martinez for What's wrong with Nulls?

NULL is wrong only if it the consequence of a poor database design, or if its purpose is unclear or ambiguous. For example, suppose that your database contemplates addresses per user and you create multiple columns to hold them: address_1, address_2, ... . This is, of course, poor design: the correct way to do this would be to create a new table to link multiple addresses to the users. If your user has only one address, the rest of the columns will have to hold the NULL value, which is purely a consequence of the design error. Besides, this NULL values are ambiguous. Does the user have a single address? Does the user have two addresses, the second of which we don't know? Is the address information inapplicable?

But of course these questions have to be answered even if the database is correctly designed and normalized. That is when NULL comes into play: it represents missing and/or inapplicable information. If, for example, you knew that a user has a second address but you didn't know its ZIP code, then NULL would be applicable as a value in that field. The important thing is that you know what NULL means in your database, and that you are consistent. An ideally designed and populated database will not have NULLs because, in theory, the database admin will have all the relevant information, but in the real world that is hardly the case.

Answer by Erwin Smout for What's wrong with Nulls?

One, nulls lead you into the realm of some 3-valued logic. "Some" logic, because there can be many (one of the possible differences is how logical implication is defined exactly). Anyhow, whatever particular 3VL is used, it will always hold some nasty surprises and be a bit unintuitive at best, or be downright completely unintelligible at worst.

Lots of equivalences (/tautologies) that hold in 2VL, cannot be upheld, or only with great difficulty, in most 3VLs. Take logical implication : in 2VL, it is commonly known that 'p implies q' is equivalent to 'NOT(p) OR q'. You might try to figure out how some very basic tautologies from classical 2VL work out in 3VL.

e.g. p=>q <=> not(q)=>not(p)

or

p or not(p) <=> true

or

p and not(p) <=> false.

Second, there is the mostly adhoc way in which SQL deals with nulls in various circumstances. Add two numbers, one of which is null, and you get null for a result. Do the same using some form of SUM() (using the same arguments), and you get the number zero ! Insert a zero-length string in an Oracle DB and it will convert that to a null, but other DBMS might not do that (I don't know whether equality comparisons comparing some CHAR column to a zero-length string (not necessarily a literal, mind you) behave correctly.

Third, one must remember that nulls, which are essentially boolean flags indicating presence/absence, ADD complexity to query writing, but most of that complexity is swept under the carpet by the SQL language, because it offers lots of "default behaviours" (e.g.comparisons returning FALSE when the real result should be UNKNOWN), thus leaving the developer with the wrong impression that that additional complexity does not exist, and that the developer doesn't need to care about that additional complexity.

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72