Create a new table for optional values
Have a problem creating a new table for fields that are not required ? For example in the endereco table (cod_endereco, logradouro, cep, numero, complement) where complement and an optional field, and when a user enters data the table does not get this null field, and when the user inserts a complement it is used in a new table. I'm not sure but Null or empty fields take up space, and I want to avoid the space being occupied by these fields blank. The first image shows the endereco table(cod_endereco not null, logradouro not null, cep not null, numero not null, complement null), the second ja and with two tables where the complement table will only be filled if the user informs the complement.
1 answers
Mysql is "smart" enough to take up little space for null fields. Therefore, creating an auxiliary table only to register optional fields, in addition to being laborious, can take up even more space than if you just left the empty fields inside the table, not to mention that it will still decrease performance because the bank will need to consult two tables instead of just one.
Leaving the fields with NULL will make MySQL able to interpret it better and reduce usage of Space:
Translating an excerpt of https://dev.mysql.com/doc/refman/8.0/en/innodb-physical-record.html :
A null SQL value reserves one or two bytes in the registry directory. Also, a null SQL value reserves zero bytes in the data part of the record, if stored in a variable-size column. In one fixed size column, it reserves the fixed size of the column in the part registration data. The reservation of the fixed space for NULL values allows an update of the column from NULL to a non-NULL value be done on site without causing fragmentation of the index page.