What would be the best way forward with respect to data normalization? The system will basically have three types of "people":
These entities have some common properties, for example: Contacts, Address, document number, name, user at long last.
What would be the best way forward from the point of view of normalizing these tables?
It always depends. But I would say that the most certain is a Pessoa entity that can PessoaFisica and PessoaJuridica find it interesting and have other objects with roles that that person performs in your organization. Almost always having only the repeating roles of my person data in those entities is wrong by keeping different objects in the system for the same real object.
The person is a clear and obvious object of any model. Paper data is not always so obvious, we have to think of it as special documents because they do not even exist as real objects.
Then you should have tables of Cliente , Colaborador , Fornecedor , etc. even because one person may have more than one role.
Conceptually, it is most correct to have everything separated. If for reasons of performance do not want to have separated until could have the papers next to the object of the person, but this is not universally more interesting not even looking at performance. But each time this is less necessary because of technologies that behave closer to how memory is. Especially on disk it may have useful to table a single table, but it is not easy to manage it.
In memory you would usually have to keep these objects separate and there is not much of an advantage to put it all together.
But what's best is the right concept, with it you can do whatever you need in an organized and easy way, when the concept is wrong all maintenance becomes complicated or almost impossible, except for changing everything completely. / p>
What is almost always wrong is having the tables of the roles and not having the person related, ie repeat the same person in all tables, as almost all systems do, a penalty.
In your case the best solution would be the normalization, with tables interconnecting the columns in common, mainly aiming at maintenance. Imagine in a case where a change in the format of the address data, a column addition in relation to the person record, etc., would have to be made, and this should be done in the best and fastest possible way. In a non-normalized case there would be three tables to be changed and then revised and, worst case, corrected if something went wrong, and so on. Not to mention scalability, which in the case of a heavily populated database, management and maintenance is much simpler and safer with a standardized database.
At a well-applied level of normalization, there would be a table for person data, such as documentation number, name, and etc, and this table would be referenced by agency, employee, and customer tables, considering that the same person could collaborate and client at the same time, avoiding excessive redundancy of data.The utility of having employee data, for example, in a separate table is the advantage that this can provide a filtering and a greater range of detail of the data, such as start and end dates of person-collaborator bond.
