Database Fields: nullable or not?

Published 1 year ago by intrepidws

Since Laravel 5.4 introduced the ConvertEmptyStringsToNull middleware, I've run into some troubles when doing a mass update on a table with several optional fields. Since empty strings are now converted into NULL, I'm getting database integrity constraints whenever an optional field isn't filled in.

One option would be to pull out all NULL values from my $request->all(), before processing, but that wouldn't really accomplish what I need either. That would mean that a field that previously had a value (and was being updated to NOT have a value) would retain the original value.

There are two obvious solutions, as I see it:

  1. Change all optional database fields to be nullable
  2. Turn off ConvertEmptyStringsToNull

Assuming that I don't want to do #2, this leaves me with changing all of my optional database fields to be nullable.

My question: Is this a bad practice?

One concern that I have is the following:

Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column.

Cronix
Cronix
1 year ago (647,500 XP)

That was one of the first things I did was comment out that ConvertEmptyStringsToNull middleware. That's a very opinionated middleware that they should NOT have had enabled by default, imo. One of the reasons being what you linked to.

intrepidws

The trim strings middleware seemed reasonable to me but the ConvertEmptyStringsToNull middleware definitely seems too opinionated.

I'm open to arguments though, if anyone wants to post any.

jekinney
jekinney
1 year ago (213,695 XP)

Back in older db versions null values took up to much space, but recent updates eliminated that. Now null values take up 1 byte.

So many feel now that it's faster and easier to not store empty strings but null values. Plus checking is faster (talking 1-2 milliseconds though) for null versus empty.

Also output null doesn't output anything but output empty string outputs empty string.

TaylorOtwell

You could just turn it off. I agree it is a slightly opinionated middleware and depends on your application's needs. That's why it's in the app-level HTTP kernel for easy disabling.

jlrdw
jlrdw
1 year ago (237,650 XP)

You can still store null if needed. Somestimes it's good to have a null, other times a 0 (zero) here is a query fragment

$sql = $sql . " (SELECT (Sum(IFNULL(deposit, 0)) - Sum(IFNULL(widthdraw, 0))) FROM checks";

There are times when a query doesn't give expected results if a null is there, so in the above, if nothing is entered, I changed the table to store a 0.00.

However is a date field sometimes if a date is blank I like a null, as I don't like

0000-00-00 

being stored. Really, some common sense.

jlrdw
jlrdw
1 year ago (237,650 XP)

@TaylorOtwell only 10,640 experience points, I am shocked, I figured you would start with like a billion or something. Thanks for what you do.

intrepidws

Back in older db versions null values took up to much space, but recent updates eliminated that. Now null values take up 1 byte.

@jekinney, so is the MySQL 5.7 documentation inaccurate?

I agree it is a slightly opinionated middleware and depends on your application's needs.

@TaylorOtwell I actually really liked the idea behind the ConvertEmptyStringsToNull middleware, in theory. But in practice, it felt weird today changing 12 database columns to be nullable.

jekinney
jekinney
1 year ago (213,695 XP)

@intrepidws What are you talking about?

https://dev.mysql.com/doc/refman/5.7/en/data-size.html

Table Columns

Use the most efficient (smallest) data types possible. MySQL has many specialized types that save disk space and memory. For example, use the smaller integer types if possible to get smaller tables. MEDIUMINT is often a better choice than INT because a MEDIUMINT column uses 25% less space.

Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column.

Notice the ONE BIT per column? Also look at old docs, null values for a 120 varchar too up the 120 in space for null values, then it was updated to only a few bits in the db file but reserved full space for memory. Now it's one bit that's it.

intrepidws

@jekinney You're right - it's only one bit. I'm not at all concerned about the space aspect of what the docs say. I'm more focused on this part:

Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL.

So let's leave the one bit usage out of it, and focus on the speed portion of the above statement. Do you see that as a non-issue?

jlrdw
jlrdw
1 year ago (237,650 XP)

@intrepidws bottom line is who gives a blank? Like I said there are times I want a null, times I don't, so wouldn't it be up to the developer? Each use case is different.

intrepidws

@jlrdw Sure, there are times you would and times you won't. Up to the developer. Again, sure. That's not really my question/point here though.

I'm trying to understand if having a database full of optional nullable fields comes with any negative performance that I might want to keep in mind when making that decision.

jlrdw
jlrdw
1 year ago (237,650 XP)

Yes and no, it's two sided, on one had a possible performance hit in a query. Some complex queries and grouping mixed with nulls don't get along. That's why if I know I will use a complex query I have a 0.00 instead of null. When dealing with numbers I have just found I don't like null. But date field null is what I want. Varchar I could care less.

Edit as @jekinney said performance loss if any is now negligible.

ryanmortier

Change all optional database fields to be nullable

This is what you should really have done though. Why store empty strings in the database?

Please sign in or create an account to participate in this conversation.