Consider for example in the database:
CREATE TABLE users (
id INTEGER IDENTITY PRIMARY KEY,
last_name VARCHAR (50) DEFAULT '' NOT NULL,
first_name VARCHAR (50) DEFAULT '' NOT NULL,
email VARCHAR(50) DEFAULT '' NOT NULL)
And consider a program that interacts with this database using Hibernate as ORM.
In my opinion this would mean:
Is this good practice? Please give examples of the pros and cons.
As PeterMmm writes, "Null" has a special meaning, and is generally supported by special operators in your database engine.
"Good practice" is one of those phrases that means whatever you want it to mean, but unpacking the arguments a little:
Maintainability: Bad idea. Most developers are used to the way NULL is implemented in database systems, and will need special training in your design.
Fault tolerance: Bad idea. If a column is not allowed to be null, that usually means something in your application design. If a user record MUST have an email address to be valid, you can now no longer validate this at the database level - so your database could gradually fill up with bogus data, and you'd only find out when you try to send an email to ''. "Null pointer" exceptions are feature, not a bug!
Performance: according to http://www.sql-server-performance.com/2007/datatypes/, NULL is slower.
That depends on your use case. null is a special value and has a special semantic. You may default all to empty string but it could be very diferent to have last_name an empty string or a null (last name not known or not applicable)(bad example, i know).
Well, the con would be in that you wouldn't be able to differentiate between a "I didn't enter anything for this field" and the empty string. It would be nice in that you'd never have to worry about null strings. Unless your driver introduces them. (I think some older ones just turn empty strings into a java null.)
In my experience it is not a good practice. assign the null where it needs
Quoting Django ORM docs:
Avoid using null on string-based fields such as CharField and TextField because empty string values will always be stored as empty strings, not as NULL. If a string-based field has null=True, that means it has two possible values for “no data”: NULL, and the empty string. In most cases, it’s redundant to have two possible values for “no data;” the Django convention is to use the empty string, not NULL.
So, yes, the most widely used Python web framework considers the design you proposed best practice and provides this behaviour by default. Java is also moving away from nulls nowadays with Java 8 Optional, perhaps it is time to reconsider our DB designs in this light.
