Reasons against having "concrete" values in own table with only an ID column and the value itself, and composing bigger objects of IDs
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.