Introducing
On the contrary. The idea of separating the activities of the entities that have business relationship with the organization is old and was used because no one had thought about it properly. This was invented decades ago when computational resources were extremely scarce and there was no experience of what worked or not. Unfortunately there are those who keep doing this.
I’m not going to talk about good practices because they don’t usually help anyone in the actual practical case, understanding and solving the problem is what’s important. A manual telling you what to do or not to do does not solve the problem. Of course only experience will allow you to really understand the problem. But I see that these good practice manuals cause more harm than good because almost always people understand them as the right thing to do and not as general ideas that may or may not be applied in the specific case.
You may have a very specific need and what I’m about to present may not apply fully. But overall you should model the data as it really is. The problem is that it’s not always easy to see it that way. For example, if you think a document is exactly how tables should be modeled, it falls into the mistake that was made in the past which is to separate by activities.
In general the normalization correct is the easiest way to go. If it is done in the right way it is easier to maintain. Among other reasons, normalization was created to make it easier for the system to change without too much trauma. Not normalize should only be chosen in the rare cases where the performance proved bad and is affecting the functioning significantly. So don’t even think about making super tables with everything you can because in theory putting everything together can make access easier.
One of the key things is not to think so much about current requirements, but about requirements that might happen. I’m not saying to implement the hypothetical future requirements, I’m saying to facilitate their implementation. And this goes against the other needs of modeling.
Your specific model
You say that customer can only be a natural person. Are you sure? If a client appears and says that he can only work if he is treated as a legal person - even if people are actually treated - for tax or other reasons, he cannot be served because the system does not allow?
There’s one thing about your model that’s separating the specialized data into related tables. Treating all as entities is the right real-world modeling. Entities can be business relationships of different natures. Why can’t a customer be a supplier? In the old systems they could not, ended up having the same entity registered twice or more times if she had different business relations.
PJ X PF
Your modeling has data that is not normalized. Legal and physical persons have unrelated data and should not be in the same table (usually). So you "should" have a table of Pessoa or even Entidade with the general data. And related tables of PessoaFisica and PessoaJuridica with the specific data of each type of person. Actually maybe these two tables can be the basis and not have a table Pessoa that unifies these two types, after all their use is mutually exclusive. It is impossible for the same entity to have additional data in both. Perhaps it was the case to think about Pessoa As an abstract entity that has no physical table, I like it better that way. It doesn’t mean that a table can’t be used, but you have to think about it.
I’ve talked about it in another answer. Note that there I do not speak of relational databases. So speaking in abstract class there makes sense, here no, I’m just making an analogy. The table would have to be concrete and have a relation of composition and not of inheritance, which may not be ideal. And contrary to popular belief, no relational database mainstream possesses functional native heritage. The fact of one database having started an implementation of this resource 20 years ago and then abandoned does not make it able to handle inheritance appropriately.
Specialized data
So the biggest problem of the demonstrated example is the lack of normalization of the type of person. But there is something strange when you have repeated data on Pessoa and in Fornecedor. Even some who are only in Fornecedor They look weird because it’s data that’s not unique to a supplier. Related tables that store information specific to the type of business relationship should only have data that exists only in the relationship. If the data is useful to other business relationships, then they should be in a "higher table".
when the supplier is added to the equation the amount of specialized fields in the "Person" table increases greatly
There seems to be a wrong understanding. Adding vendor should not put specialized fields in the person table. But on the other hand there are no specialized fields in supplier, according to the example. I even understand that the model is not yet ready but in what has been demonstrated there is no need for a secondary table for customer and supplier. Of course you will have specific data, I just want to make it clear that the fields shown are not specialised, at least they should not be. It may be that someone said it is, but that’s another problem. One thing is the requirement that the user says, another thing is the real requirement that the user does not understand. With the advances of database technology I think until this can be done differently but needs care.
Professional
A minor doubt: I’m not quite sure what this is Profissional. Is he really someone who has a business relationship with the organization? Surely there can never be a change, even by law, that he can be a legal person? I ask because it is looking like a specialized type of supplier. It may not be. If it is I think it needs a proper treatment.
Cardinality
I do not know if I understood the drawing of this diagram but probably the relation between the tables should be 1:1 with each secondary table. It is not possible for a person to have two different vendor data, for example. I can even see a scenario where this is possible but it probably wouldn’t be the right way to implement it and it wouldn’t be easy to maintain it. Since need is rare, I think it’s best not to think about it.
Completion
What brings problem in the future is not understand correctly the model, and this is common, but has nothing to do, have to try to think of everything, ask for those who have more experience, try.
If in doubt, having everything together is always harder to solve after keeping everything separate. But what makes it even harder to solve is when information is missing. Imagine the bank having thousands of registered entities and to fix a problem you have to review all the registrations manually.
Does having more relationships complicate database manipulation? Yes, of course, but only in very simple systems that probably will not change is that simplification should be chosen.
If you decide not to follow any of this and use a "linguing" table, will you have many problems in the future? Probably not, changes are never easy, there are ways to facilitate, but they are always unpredictable in their entirety.
What causes the most problem is data redundancy. If one day you have to reconcile these data, it can be a huge storm and almost impossible. And in the diagram shown, this could be happening.
Separating the roles of people allows you to have in the future more easily besides customers, suppliers, professionals, also sellers, governments, banks, carriers, etc. And you will not worry if fields are being created to be left with nothing. The secret is to follow the normalization. Rarely should there be fields without data.
I’ll let someone with more experience answer, but in my opinion you’re on the right track - including in the detail of doing the
idof specialized tables also be a foreign key to people’s table. You are in fact implemented an inheritance betweenpessoaandcliente/profissional/fornecedor! What if a supplier can be a natural or a legal person, because it does not repeat the strategy? Create a table with only the fields common to each supplier, and another that representsfornecedor_pessoa_fisica, with foreign keys both forfornecedorhow muchpessoa.– mgibsonbr
I would like to offer an answer to your question. You can give a brief conceptual description of "Professional" in this modeling?
– Caffé
@Caffé This tool that I am creating is aimed at medical clinics. So "Professional" will store people who work in the company. Secretaries, doctors, technicians, warehouses and etc. I will also change the table name "Client" to "Patient" in order to be clearer. Coming home edit the post with these clarifications.
– adamasan