How to insert values into a highly normalized database without excessive duplicate checking?

Question

The situation:

The lead for our project has decided to use a highly normalized database as our database design. Meaning that literally every field of a big table is an ID instead of the actual value. His intention is to have no duplicates of any kind, even in places where duplicates don't hurt (peoples first names, that kinda stuff).

This does lead to a problem though: When inserting new data, we need to check every single subtable to see if the value exists (first query), then insert it if it doesn't (second query) or else retrieve the ID, do this for literally every column in the main table (so 30 times or something), and then we can create the object we actually wanted to get. (That's ~60 database hits for creating an object!).

We work in spring, so we use jdbcTemplate to actually build a database connection, and every query is expensive. When we're inserting thousands of new records, or updating them, this actually slows down the database heavily.

This entire process feels completely dirty and wrong to me, and thus I wanted to ask: Is there a better way? Is it possible to have an subquery insert an value if it doesn't exist, or not if it does, and return the actual key in both cases, which gets used immediatly to set the ID in the main table? Is there an elegant solution to cut down on the number of queries without introducing too much complicated SQL (for team members sakes)?

Answer 1

A little thought...

Formally you have something similar to this simplified structure:

CREATE TABLE slave1 (id PK, value UNIQUE);
CREATE TABLE slave2 (id PK, value UNIQUE);
CREATE TABLE main (id PK, id_slave1 FK, id_slave2 FK);

When you need to insert 2 records (id_1, val_1_1, val_1_2) and (id_2, val_2_1, val_2_2), you execute:

CREATE TEMPORARY TABLE temp (val_slave1, val_slave2) [ENGINE=Memory];

INSERT INTO temp (val_slave1, val_slave2)
VALUES (val_1_1, val_1_2),
       (val_2_1, val_2_2);

INSERT IGNORE INTO slave1 (value)
SELECT DISTINCT val_slave1
FROM temp;

INSERT IGNORE INTO slave2 (value)
SELECT DISTINCT val_slave2
FROM temp;

INSERT INTO main (id_slave1, id_slave2)
SELECT slave1.id, slave2.id
FROM temp
JOIN slave1 ON temp.val_slave1 = slave1.value
JOIN slave2 ON temp.val_slave2 = slave2.value;

The engine of temp may be Memory when the amount of inserted values is low, and InnoDB or something else if inserted data array is huge.

INSERT IGNORE works fast enough over UNIQUE indexed field. It guarantees that no duplicates in slave tables, and that the values which must be inserted will exist in slaves while insert into the main table.

And final query must be fast too - especially when temporary table fields are indexed too.

If you need to insert one record only, then you can, of course, do not use table temp... but I think uniformity is more safe then a little simplification.

Of course, this may be optimized. For example, all inserts may be joined into one stored procedure, and you do not need in "60 database hits", one CALL is enough. Finally you must execute only 3 queries independent by the records count for to insert. And only one of them (inserting into temptable) may be huge (or even it can be divided to a lot of inserts).

Answer 2

If you can batch the data, I can give you a technique that involves 2 queries per batch -- one to insert any new names for the batch, one to find all the ids. And it does not waste ids, like REPLACE, INSERT IGNORE, IODKU, etc do.

1. Build a temp table with all the denormalized data. It has vacant columns for the ids.
2. Run the 2 queries for each column that needs normalizing. (2*30 queries in your case).
3. INSERT .. SELECT .. to move the data to the real table(s).
4. Truncate or drop the temp table.

See Bulk Normalization

That was designed (and perfected years ago) when I needed to shovel large amounts of data (possibly coming from many clients) into tables and some (not total) normalization was required.

At first I had INSERT IGNORE, but quickly realized that I was likely to run out of auto_increment ids. I was not willing to use 8-byte BIGINTs. After all, one purpose of normalization it to save space.