How does the number of tables affect performance?

Question

PostgreSQL

Suppose such a label is designed.
In fact, some links, such as season, gender, measure, can not be made links and placed in the same table, these data will be repeated in one column, but they do not have additional values.
It's bad if they have some fields, but let's assume that this will not happen.

I was told "nothing depends on the number of tables, it's just more convenient to work", plus the id is stored in the table, will the indexes be faster
? from RAM and read from disk. How does it work and is it really not worth it to create a table for 1:N once again?

Answer 1

in your proposals, one does not contradict the other
tables, you can create a lot (there is a limit on the database, I don’t remember how much - enough for any foreseeable database) and this does not affect in any way (well, performance changes by 0.0000001%)
but WHAT IS a table, and HOW JOIN works - you need to understand the
table - this is a regular file in which the data is entered by a tree (too lazy to look for which one - consider it the most optimal)
next to it are index files and quickly extracting ID by ID quickly gets a line from the tree (line \u003d entry in the table) - everything!
when a JOIN occurs - the DBMS performs XOR in pairs over IDs (well, to be exact - over field values) from different tables - it is at this moment that the processor is loaded and a properly designed database works faster - less memory is always used - less data in the table - less occupied memory, everyone is happy,
but (!)
JOINs are executed sequentially (another point of optimization in what order)
and the database is forced to store the results of the previous (its / their) JOIN s somewhere ,
and since subsequent JOINs are performed ON OTHER FIELDS
, the database stores in memory not only ID, but also the data of these fields (well, or in general of all records, here who implemented how)
and here it is: the more JOINs, the more tables (partially) are unloaded into memory
total: there is no silver bullet - you need to be able to handle the DBMS