2
What would be the best way forward regarding data normalization? The system will have basically three types of "people":
These entities have some common properties, for example: Contacts, Address, document number, name, user finally.
What would be the best way forward from the standpoint of normalization of these tables?
3
It always depends. But I would say that the most certain is an entity of Pessoa that may even PessoaFisica and PessoaJuridica find it interesting and have other objects with roles that this person exercises in your organization. Almost always having only the roles with repetition of my data of the person in those entities is wrong for keeping different objects in the system for the same real object.
The person is a clear and obvious object of any model. Paper data isn’t always so obvious, you have to think of it as special documents, because they don’t even exist as real objects.
Then there should be tables Cliente, Colaborador, Fornecedor, etc. because the same person may have more than one role.
Conceptually it’s right to have everything separate. If for reasons of performance do not want to have separated even could have the roles next to the object of the person, but this is not universally more interesting even looking only performance. But this is increasingly less necessary because of technologies that behave closer to what memory is like. Mainly on disk can have useful mater a table only, but it is not easy to manage it.
In memory usually would have to keep these objects separate and there does not have much advantage join everything.
But what matters most is the correct 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 by changing everything completely.
What is almost always wrong is having the tables of the papers and not having the person related, ie repeat the same person in all tables, as almost all systems do, a pity.
Behold What is database normalization?.
I agree, that’s why I went to the community, and although I try to keep it as standardised as possible always, I don’t have as much experience with normalization, but I think it’s very important that it is cohesive and simple to maintain. So the idea of using a table person with the basic data (id, name, address -separated in another table, of course- etc) and extend this table to their specific roles (agency, suppliers, customers, collaborators etc), basically the concept of partitioning (as suggested by the comments), would be the correct way to follow in this case?
Correct I can’t say, modeling is using experience and deeply grasp the specific problem. If it were formula does not need so much programmer, all problems would already have a solution. I would do so in almost all cases.
I had already thought about doing this way too, and so far the suggestions of all colleagues indicate this way, I believe it is best too.
Is it advisable to use this same question to show the modelling that I arrived at, to ask the opinion of colleagues??
If you have something different to ask now precious be another question. And asking for opinion is not something that is accepted on the site. It has to be something objective.
Okay, so I’m done here.. rs Thanks for your help, buddy!
1
In their case the best solution would be in fact the normalization, with tables interconnecting the columns in common, mainly aiming at maintenance. Imagine in a case where there would have to be a change in the format of the address data, a column addition in relation to the person’s registration and so on, and this should be done as quickly and as quickly as possible. In a non-standard case it would be three tables to be changed and then revised and, in the worst case, corrected if something went wrong, and so on. Not to mention scalability, which in the case of a very 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 client tables, whereas the same person could be a collaborator and client at the same time, avoiding the exaggerated data redundancy.
The utility of having the collaborator data, for example, in a separate table is the advantage that this can provide a filtering and a wider range of data detailing, such as start and end dates of the person-collaborator link.
My doubt was precisely in relation to his last paragraph, really it made more sense to me so, the address and the user, for example, I already do separate, including being the address divided into two parts one with a generic register of patios (where a cep does not appear more than once, unless it is a single cep for the city) and a second table where I store the specificity of the address (number and complement) for the register. But I’m really inclined to register people the way you mentioned. That is, a table Person, with basic data, and to extend
This is not really a rule, but keep in mind that on a normalized basis, maintenance and management get better and more objectives, after all, rather than correcting or changing the size of an address field in three tables, for example, it will be in just one.
Yes, I agree with you, and that’s exactly what I’m thinking, this system is not customer, it’s mine, I’m wanting to make it to be a shelf system, and preferably with maintenance as easy as possible, rs
Browser other questions tagged database modeling normalization
You are not signed in. Login or sign up in order to post.
Search for partitioning information. You can have a person entity with all common data and global relationships and a derived entity for each type of person with the particular data of each type of person as well as the particular relationships of each type.
– anonimo
Great, I’ll look it up. It’s the form the colleague below suggested as well and the one I was most inclined to use. Thank you very much!
– Rodrigo Teixeira Andreotti