Database Fields: nullable or not?

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.

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.

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.

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.

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.

@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.

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.

@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.

@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?

@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.

@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.

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.

Change all optional database fields to be nullable

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