Create a new table for optional values

Question

Do you have a problem creating a new table for fields that are not mandatory ? For example in the address table (cod_addressee, path, zip code, number, complement) where add-on is an optional field, and when a user enters data the table does not get this null field, and when the user inserts an add-on it is used in a new table. I’m not sure but null or empty fields take up space, and I want to avoid that space being occupied by these 'blank' fields. The first image shows the address 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 populated if the user informs the complement.

Answer 1

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, may take up even more space than if you just left empty fields inside the table, not to mention that it will decrease performance because the bank will need to consult two tables instead of only one.

Leaving the fields with NULL will enable Mysql to interpret it better and reduce space usage:

Translating an excerpt from 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. In addition, a NULL SQL value reserves zero bytes in the data part of the record, if stored in a column of variable size. In a fixed column size, it reserves the fixed column size on the part record data. Reserve fixed space for NULL values allows an update of the NULL column to a non-null value be done on the spot without causing fragmentation of the index page.