"Inheritance" table is bad practice in this case?

Navigation
3

In my database, I need to store employees, customers, and suppliers. Since all of these share data in common, but are not all data, I have decided to create a pessoas table 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, such as funcionarios : 

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

The relationship between funcionarios and pessoas must be one-to-one (or none), since a person record can only have an employee, customer, or vendor record.

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

I do not know if I made the right decision here. Is this a bad practice? If so, are there other alternatives? Should I repeat all fields from pessoas to funcionarios , clientes and fornecedores and delete pessoas ?

I must have expressed myself badly because this kind of relationship does not look like one to one, but one to one or zero. Am I right?

Is the pessoas.tipo column really necessary?

    
asked by anonymous 20.10.2017 / 16:05

2 answers

6

I can not say that it is wrong, but I do not like this kind of separation in most cases. I think it is valid if the registration is too large and the parts are usually accessed independently most of the time. It works as an optimization. Not that it is necessary and useful in most cases.

If the person can only play a role in the organization, then determine that it is that role, ie, why have a person's register? There is no repetition at all because you are ensuring that she has only one role. If there is no repetition, there is no reason to talk about normalization.

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

Separation of roles and person

But it is common for a person to exercise more than one role, it begins to be interesting to have this separation. So the person can link to multiple roles ( tipo ) and not just one, as modeled in the question.

If you adopt this separate model, it may be interesting to also have a way of directly linking to the specific roles registers. But it's just optimization. If it is guaranteed that the paper register will have the same id of the person, then there is no need because you know what id there. But I find this a bit risky. You need to know how to do it and you need to know that it will not change for a day that the person has two separate accounts on some table of papers.

In theory, you can even delete tipo because you can figure it out, but it will require more code and execution effort. I would not rule it out. Unless you keep a binding code for the specific tables, because if it is filled out for that role you know it is of that type.

I answered some questions about this:

20.10.2017 / 16:25
5

That alone is not a bad practice.

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

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

I would just do something different: I would pass the type of person to the daughter table. After all, it goes that one day someone becomes a customer and a supplier at the same time ... In the way this person would need two entries in the parent table.

In the commentary Maniero drew attention to one important factor. As I said, normalization has its advantages, but I forgot to mention a few drawbacks. Among them, the spread of information between different tables. This can make queries more expensive.

Assuming you keep the standard template you propose in the question. If you want to search, for example, for an employee who, for example:

  • more at a specific address, and;
  • receive pay within a certain range ...

You will have to search the two tables. A percentage of% between tables can end up being more expensive in terms of performance than searching in a single table. If search 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.

    
20.10.2017 / 16:24
Java conversion to Kotlin How to choose a convention for names of variables and functions?