I'm trying to learn about proper database management and am having a hard time with designing a database that doesn't rely on NULLs. If you think I'm crazy for embarking on this, it is because of SQL and Relational Theory: How to Write Accurate SQL Code (ch.4, p.79), where the author makes the case that simply because having NULLs produces unexpected results in certain situations, they should be avoided.
The alternative to NULL-able fields, as I understand, is to create 1::0-1 relationships for each field. This seems like it would make querying information very difficult. Is this the correct approach? Are NULLs, in fact, much safer than the author leads me to believe?
Would using a
VIEW to represent the many 1::0-1 relationships be applicable and any more beneficial than simply having the NULLs in table in the first place?
My scenario is a
user table with optional fields that may, or may not, be foreign key relationships to other tables. Obviously, making those fields NOT NULL means they must be required, but I can't put
0 in there since it breaks the foreign key. Other situations occur when trying to put
0000-00-00 for, say, a
birthdate field, since MySQL doesn't allow me to do that.
Thus, I'm back to wondering if eliminating NULLs are, indeed, a best practice and provide the greatest flexibility and performance than the alternative.