Dealing with null values versus empty strings in a database when only empty strings are coming from the client via HTTP POST

Question

My MySQL database has carefully defined fields. Some fields, if there is a chance that they can be unknown, allow a NULL value.

I'm writing a web-based CMS to handle the data in said database. Obviously, the post arrays from the HTML forms never contain null values, only empty strings.

I don't want to confuse the users of my CMS by adding a "NULL checkbox" or anything like that but I can't tell from the post arrays whether a field should be null or empty.

Should I convert all empty strings to NULL values on save for fields that allow null values?

What are some good practices for this type of conundrum?

Answer 1

That is a sticky question. Of course there is no way for a user to know whether they want to insert an empty string or nothing at all. So the answer is really up to you. Do you want to allow users to enter empty strings? Does it have any meaning for it to do so? If an empty string means something that a NULL string doesn't and that is well defined, go ahead and allow both.

If there is no distinction between them, pick one and stick with it. I personally don't see why you would need to keep empty strings like that, it just makes things far more confusing later. Just stick with NULL to represent an absence of data.

Make your decision now, document it and stick by it.

Answer 2

Do you really want to deal with actual proper NULL, with its tricky three-value logic?

In my experience you rarely actually want the explicit uncertainty that comes with NULL. Storing an empty string for values the user doesn't care to fill in is usually more practical. Then you can go ahead and do searches like WHERE t.field<>'x' or WHERE t0.field=t1.field without having to worry about what that does to your boolean logic when one or both are null.

If you've already got a working database that relies on the uncertain nature of null, and that's an intrinsic part of your requirements, then fine, stick with null (and in that case you'll probably have to convert empty user input in a nullable field to null just because no end-user is ever going to be able to understand the conceptual difference between nothing and null).

But personally I only still use nulls for optional foreign key references (when not using a separate join table).