Is "inheritance" table a bad practice in this case?

Question

In my database I need to store employees, customers and suppliers. Since all these share data in common, but not all the data, I decided to create a table pessoas with the columns in common:

id // chave primária
tipo // tipo F para funcionário, C para cliente e F para fornecedor
nome
data_nascimento
endereco
inativo

And to store the specific data of each type I made other tables, as the funcionarios:

id_pessoa // único, não nulo e chave estrangeira em pessoa(id)
data_admissao
data_demissao
salario

The relationship between funcionarios and pessoas should be from one to one (or none), since a person record may only have an employee, customer or supplier record.

What I tried to address was an "inheritance". funcionarios inherits all the columns of pessoas.

I don’t know if I made the right decision here. Is this a bad practice? If it is, are there any other alternatives? I should repeat all the fields of pessoas in funcionarios, clientes and fornecedores and eliminate pessoas?

I must have expressed myself badly because this type of relationship does not seem one for one and one for one or zero. I’m right?

The column pessoas.tipo is really necessary?

Answer 1

I can’t say you’re wrong, but I don’t like that kind of separation in most cases. I think it is valid if the register is too large and the parts are usually accessed independently most of the time. There it works as an optimization. Not that it is necessary and useful in most cases.

If the person can only exercise one paper in the organization, then determine that it is that role, that is, why have a person’s record? There is no repetition because you are ensuring that it has only one role. If there is no repetition, there is no reason to talk about normalization (read the link because it’s a little more complicated than this).

Most systems do not conceptualize right and use the approach of having only roles and not people. If a person is an employee and customer at the same time will have two records of the same person.

Separation of roles and person

But it is common for a person to play more than one role, and then it becomes interesting to have that separation. So one can link to several roles (tipo) and not just one, as it is modeled on the question.

If you adopt the separate model, it may also be interesting to have a way to link directly to the specific forms of papers. But it’s just optimization. If it’s guaranteed that the paper registration will have the same id of the person, there does not even need because you know what is the id there. but I think it’s a little risky. You need to know how to do it and you need to know that you won’t change to one day having two separate accounts on some table of papers.

In theory it can even eliminate the tipo because you can figure it out, but it will require more effort of code and execution. I just wouldn’t rule it out. Unless you keep a link code for the specific tables, because if it’s filled for that role you know it’s that kind.

I answered some questions about this:

• It can be considered a bad modeling practice to store all people (customers, employees, suppliers) in the same table?
• What to do if client can be both PF and PJ?
• Modeling launches using customers and suppliers
• Modeling for comprehensive entity registration

Answer 2

This alone is not a bad practice.

In the world of relational databases that interact with object-oriented systems (I believe this is your case), the biggest design challenge for the base is how to assemble a schema that makes sense and is easy to maintain. The discussion about whether it is better to repeat information or not at the bank, and how much to repeat, is about a subject called normalization.

In your case, you normalized the table. Normalization is usually seen as good practice, as long as it is not exaggerated or unnecessary.

I would only do something different: I would pass the kind of person to the daughter table. After all, one day someone becomes a customer and supplier at the same time... The way this person would need two entries in the mother table.

In the comments Maniero called attention to an important factor. As I said, normalization has its advantages, but I forgot to mention some disadvantages. Among them, the spread of information between different tables. This can make queries more expensive.

Suppose you keep the standard model you propose in the question. If you want to search, for example, by an employee who, for example:

• live at a specific address, and;
• receive salary within a certain range...

You will have to search both tables. A JOIN between tables can end up being more expensive in terms of performance than searching in a single table. If research performance is the most important factor in database design - and for large systems, it usually is - then it is worth denormalizing to speed up queries.