Return to Revisions

This approach is basically trading storage for compute. While you are reducing the amount of space on disk, you are doing so at the cost of the additional join; more specifically the compute power to make that join happen. Also, the space savings is purely dependent upon the size of the datatype of the column in question as well as the key for which you'll be using to join the tables. The more complex your key, the more compute required and the less space saved.

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:

• Column A consumes ~381MB (i.e. 100,000,000 * 4 Bytes)
• New Table T2 consumes ~38.1MB (i.e. 5,000,000 * 4 Bytes for the data and 5,000,000 * 4 Bytes for the key on which we'll join)
• Space savings of 90%

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:

• Column A consumes ~381MB (i.e. 100,000,000 * 4 Bytes)
• New Table T2 consumes ~76.3MB (i.e. 5,000,000 * 4 Bytes for the data and 5,000,000 * 12 Bytes for the key on which we'll join)
• Space savings of 80%

Still not bad you say, but would you just leave it at the one column or would you extend this pattern to other fields? Because we're talking design paradigm, I'd expect to see this extended to more tables/fields/etc.

So the pro is a savings in space, but what are the cons?

Obviously the excessive amount of joins you now have to manage within your SQL statements as well as the compute that will be required, but deletes now also require extra statements (since we now have multiple tables to deal with and don't forget the extra compute this requires as well). Let's not forget what could happen if that 95% data distribution changed, as data tends to do over time.

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.

So, to answer your questions:

• Is this a common approach?

No, not in my experience.

• Is there a name for this pattern?

This is database normalization in it's most extreme. Most RDBMS systems are designed with 3NF/BCNF in mind, so likely that level of normalization is where your system is most cost effective.

• Are there any downsides except that I have to handle two tables now in my application code?

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.

That being said, it may make perfect sense to do this for one or two columns within a database where the disk consumption is excessive. The design of your database, the type of data it stores, and the system it's supporting all play into when this makes sense or not. However in those cases, I'd say you should look into table or page compression first as data has a funny way of changing such that it finds ways to break even the most well of intentioned design patterns.