Tell me how to work with the table "dictionary"

Question

MySQL

There is a table in MySQL containing, among other things, two dozen columns with string information:
table (bla bla bla, string1, string2,…, string20
) columns, which sadly affects the amount of stored data.
Naturally, a desire immediately arises to split such a table into two, in the first in the corresponding fields to store row identifiers from the second table:
table1 (bla bla bla, id1, id2, ..., id20) table2
(id, string)
structures are very cumbersome (with a bunch of JOINs).
Usually, a wrapper/layer above the database is involved in building such queries and this does not cause discomfort, but unfortunately in this project there is noodle code that you don’t want to touch, because. it is only necessary to solve the problem of ts. "compactization" of this table.
I am not strong in MySQL, but something tells me that there should be a more convenient syntax for such queries, or perhaps this task should be solved in some other way.
Please tell me the best way to do this. Of course, if there are no other options, then a bunch of JOINs will do, but I would like something more elegant.

Answer 1

Well, it seems to me that if the beauty of queries is important to you, then you should use VIEW .

Answer 2

Hm. Normalization of the subject area.
In short, you need to get rid of duplication of information by creating a new table that implements a many-to-many relationship, and the columns
[string1, string2, ..., string20], [id1, id2, ..., id20], will turn into records

Answer 3

I would replace this table with a one-to-many relationship
Add the "number" field and control the order through it
The structure is as follows:
main_table (id, bla bla bla)
and
detail_table (id, id_main_table, string, number)
Add a unique key to detail_table (id_main_table, number)
To support the old noodle code, you need to add VIEW TABLE1 based on
select m.*, d1.string string1, d2.string string2,… d20. string string20 from main_table m
left join detail_table d1 on d1.id = m.id_main_table and d1.number = 1
left join detail_table d2 on d2.id = m.id_main_table and d2.number = 2
…
left join detail_table d20 on d20.id = m.id_main_table and d2.number = 20