There are two options for organizing the database structure. The first option is to create one table with 4 columns (id, type, key, value). The second option for each type is to create your own table, where the key will be the column of the table.

The problem is that in the first version of the records in the table may be over 100,000 (although it is more flexible), but in the second case there are about 100 tables (although this is not a problem, the table is generated automatically)

More visual scheme:

Option 1:

ID | type | key | value 1 'cat1' 'key1' 'value1' 1 'cat1' 'key2' 'value2' 1 'cat1' 'key3' 'value3' 2 'cat1' 'key1' 'value4' 2 'cat1' 'key2' 'value5' 2 'cat1' 'key3' 'value6' 3 'cat2' 'keyn' 'valuen' ....

Option 2

 Таблица cat1 id | key1 | key2 | key3 1 value1 | value2 | value3 2 value3 | value4 | value5

The question is which option is more optimal. Are there any global performance differences? What can be used to improve performance? I will be glad to any advice

  • 100 thousand records in the table should not be a problem, but 100 fields and 100 external tables for things of the same type can hardly be called a sound solution. With 100 fields you have there in addition, every time 90 of them will be null. - teran
  • It is considered (not always and not all, but as a rule) that the client application MUST NOT not only execute - even have permission to execute the DDL. You obviously do not have an exception case. So only one table. - Akina

1 answer 1

I do not understand what the problem is?

types

 id | type | value 1 cat1 | value1 2 cat2 | value2

keys

 id | key | value 1 key1 | value1 2 key2 | value2

types_keys

 id | type_id | key_id | value 1 1 | 1 | value1 2 1 | 2 | value2

animals

 id | value 1 | value1 2 | value2

animals_types_keys

 id | animal_id | type_key_id 1 1 | 1 2 1 | 2

It seems that this version of the database structure allows you to dynamically work with your data.

Although, it is possible to make instead of 2 types_keys and animals_types_keys tables one table animals_types_keys :

 id | animal_id | type_id | key_id | value 1 1 | 1 | 1 | value1 2 1 | 1 | 2 | value2

It seems that this is actually your table, only reduced to normal form.