Reasons against having “concrete” values in own table with only an ID column and the value itself, and composing bigger objects of IDs

Question

This is a follow up question to How to insert values into a highly normalized database without excessive duplicate checking?

The current situation: We have an "massive" object that has around 30 columns worth of details (while it could be slightly improved, a lot of it sadly does really belong together, and can't easily be semantically separated). Every column simply contains an Integer, an ID key. Now we have 30 more tables, each only have 2 columns: "id" and "value", where ID is an auto generated key, and value is the actual concrete value.

Whenever we want to get back data from our massive objects we need to do 30 joins from these "value" tables onto the main table.

Reasons given were "that a lot of data is going to be duplicated over the next 2-3 years" (it'd be a lucky stroke if one value gets used more than thrice, and at most 20 times imho, but that's besides the point), and any future changes to those values would be much easier then. Also saving storage space (which might be true, but for perhaps at most 200k records over 2 years nothing that should be noteworthy).

Now I know intuitively a few reasons why this design is bad. But I'm still a novice myself, and I would like proper detailed reasons that I could actually present to change a mind, and perhaps learn something new myself.

Friendly reminder that the database commands we're talking about here are mostly very basic Insert, Select and Update (and very rarely joins) methods. No one in our department is experienced enough in SQL (or willing enough to learn except me) to go above those commands, and almost all logic is done server-side after getting data from the database.

Answer 1

OK, I won't rant about what is 'wrong' in your schema. I will provide an efficient way to batch the normalization for a batch insert.

I will point out two common flaws when "over-normalizing":

• Don't do it if the id is bigger, on average, than the data. Note: INT is 4 bytes; BIGINT is 8.
• Don't normalize "continuous" values -- anything numeric or date-related. It makes it very costly to do a range test.

OTOH, 100K rows/year is rather small. If they come in evenly it is a rate of about 5 inserts (plus 30 normalizations) per hour.

If they come in batches, see http://mysql.rjweb.org/doc.php/staging_table#normalization

One thing of note: Things like INSERT IGNORE will "burn" ids. That is, an AUTO_INCREMENT id will be bumped even if it is not needed. The code in that link avoids such.

Answer 2

There are a couple of scenarios where I'd consider replacing a "real" value with a surrogate & look-up.

First and most common is when I want to control the possible values in a column. I can define a table to hold the acceptable list, give it a surrogate key and reference that key in my main table. This is the classic foreign key situation.

Sometimes we need to store previous values to prove what was current when an event happened (audit) or so we can reproduce a past state of the database (temporal/ time-travel). The easiest implementation is to copy the whole of the old row to an archive table. This wastes space if the row is wide and only one attribute has changed. A reasonable first optimization would be to have an archive table for just that attribute and a foreign key into the main table. However, I think I'd still like to have the current value held in the main table.

If the value is wide, has low cardinality and the main table has many rows we would use a lot of space storing these values repeatedly. The values could be moved to their own table and referenced by a surrogate key. This is called dictionary compression. There are many performance advantages to having narrow rows when using a row store (as opposed to a column store) storage engine.

If a subset of columns are returned by most of the queries and the other columns are used only rarely there can be performance advantages to moving the rarely-used columns to a separate table. This keeps the often-used table narrow which has a performance advantage for row stores. This is called vertical partitioning. I talk about it a little more here.

Having a key value change can be a real pain to propagate through the database. If you sign-up a customer as "Acme Inc.", that string propagates through the order, invoicing, CRM, contacts, promotions, support etc. sub-systems as the key, and then they re-brand as "Super Awesome Inc." you'll be in a world of pain updating all those tables. Protecting your system from such is a good use from surrogate keys. I'd measure likelihood, impact and notice period, however. ISO country codes are unlikely to alter once allocated. Peoples' surnames change routinely.

That's why I would split. So if none of those applied I'd keep the table together (30 columns is not a particularly wide table) and hold the "real" value until such time as performance testing or changed rules tell me I have an actual problem. Then I'd fix that problem.