Create a new table for optional values

Question

Create a new table for optional values

Navigation

#1 by (1 votes)

1

Having trouble creating a new table for fields that are not required? For example in the address table (cod_endereco, pasadouro, cep, number, complement) where complement is an optional field, and when a user inserts data the table does not have that field null, and when the user inserts a complement it is used in a new table. I'm not sure, but empty or empty fields take up space, and I want to prevent space being occupied by those 'blank' fields. The first image shows the address table (not null, not null, cep not null, number not null, null complement), the second is already with two tables where the complement table will only be filled if the user informs the add-in. / p>

mysql null relacionamento tabela

asked by anonymous 06.07.2018 / 04:26

1 answer

Mobile menu: Click to open and then close Transform object into array while maintaining index

score 1 · Accepted Answer

Mysql is "smart" enough to take up little space for null fields. Therefore, creating an auxiliary table only to register optional fields, besides being laborious, can take up even more space than if you just leave the empty fields inside the table, not to mention that performance will still decrease because the bank will need to consult two tables instead of just one.

Dropping fields with NULL will make MySQL better able to interpret and reduce space usage:

Translating a snippet from link :

A SQL NULL value reserves one or two bytes in the log directory. Additionally, a SQL NULL value reserves zero bytes in the data portion of the register, if stored in a column of variable size. In a column, it reserves the fixed size of the column in the part of the registry. Reservation of the fixed space for NULL values allows an update of the NULL column to a non-NULL value is done in place without causing index page fragmentation.