Return to Answer

UPDATE: A quick edit to further explain my answer about Normalization as people seem to be passionate this isn't accurate. At the root of things, this approach is creating a separate table with a one-to-one relationship and overloading the meaning of NULL to mean whatever our most common value is, which would be 0 in our example scenario. When I say overloading the meaning of NULL, we are implying that the absence of a record means said record has a default value. Again, this design approach isn't different than any other one-to-one or one-to-many relationship between two tables, but in this case we're just implying that the absence of a record means something. Again, this isn't a revolutionary design approach, it's just a way to overload functionality which at the end of the day is just another flavor of normalizing the data.

In the best case scenario, using your parameters (e.g. 100M record table where column A is an INT), we'll assume the Primary Key is also a simple INT, we get a new table that is about 10% the size of the data footprint if kept as a column on the main table:

However, what if our key is a composite key that is a combination of an INT and DATETIME columns, we see the new table has doubled in size to about about 20% the size of the data footprint if kept as a column on the main table:

In the best case scenario, using your parameters (e.g. 100M record table where column A is an INT), we'll assume the Primary Key is also a simple INT, we get a new table that is about 10% the size of the data footprint if compared to keeping this as a column on the main table:

However, what if our key is a composite key that is a combination of an INT and DATETIME columns (4 byte and 8 byte sizes, respectively), we see the new table has doubled in size to about about 20% the size of the data footprint if compared to keeping this as a column on the main table:

If you've got excessive compute available and are limited on disk, this approach may be viable, but more often than not, compute is much pricier than storage, especially after factoring in licensing as most products are licensed based off of core count and not size. MySQL Community Edition is free but the other editions are not, and what about the CPUs on your server (or cloud service provider)? Often storage is a fraction of the cost of storage, so unless you're limited on the storage side the cost/benefit ratio doesn't work well after you factor in the additional man-hours to write the code and the potential cost to handle the extra compute needed for the additional operations.

Yes, if this design is utilized whenever possible, at minimal the cost of the extra compute needed to handle the join and the extra man hours required to deal with the extra code logic (e.g. extra joins, additional deletes, etc.)

If you've got excessive compute available and are limited on disk, this approach may be viable, but more often than not, compute is much pricier than storage, especially after factoring in licensing as most products are licensed based off of core count and not size. MySQL Community Edition is free but the other editions are not, and what about the CPUs on your server (or cloud service provider)? Often storage is a fraction of the cost of CPU, so unless you're limited on the storage side the cost/benefit ratio doesn't work well after you factor in the additional man-hours to write the code and the potential cost to handle the extra compute needed for the additional operations.

Yes, if this design is utilized whenever possible, at minimum, the cost of the extra compute needed to handle the join and the extra man hours required to deal with the extra code logic (e.g. extra joins, additional deletes, etc.) will be a downside.