Store JSON data in Mysql

Question

I have a database in Mysql where, as the project is being developed, the number of fields of some tables has grown exponentially.

The People table, for example, has fields relating to personal documents, home address, work address, personal data, academic data, etc.

'empresa' => 
array (size=6)
  'nome' => string 'Empresa Legal' (length=13)
  'funcao' => string '' (length=0)
  'rendimento' => string '' (length=0)
  'endereco' => 
    array (size=7)
      'cep' => string '' (length=0)
      'logradouro' => string '' (length=0)
      'numero' => string '' (length=0)
      'complemento' => string '' (length=0)
      'bairro' => string '' (length=0)
      'cidade' => string '' (length=0)
      'estado' => string '' (length=0)
  'telefone' => 
    array (size=2)
      'fixo' => string '' (length=0)
      'movel' => string '' (length=0)
  'email' => string '' (length=0)

I had thought of grouping the data in Json and storing each group in a specific field but I’m not sure if this is good practice or if it affects the performance of the database.

My question is essentially about best practices regarding data storage, because I can handle JSON if problems.

If this is not the best way, I accept suggestions to structure this data.

Answer 1

Hello,

In this case, you would be fleeing the first normal way. Database normalization is essential to ensure integrity and performance.

Therefore it would not be recommended in this case that you are using Mysql. But for this type of situation there are the Nosql databases, which behave exactly this way, storing collections (as if it were JSON) that do not need to follow a framed scheme.

To read more about Database Normalization: Data Normalization

To read more about Nosql: Nosql Mongodb