How to represent "is-one" relationships in the logical model?

4

I'm creating a data model where a client entity is also a person, of the legal or physical type. However, in the state it is in, it will not be possible to implement it physically, since I can not create the logical model that correctly describes a way to make one entity inherit attributes from two different ones. See.

First, I made the conceptual representation of the situation described as follows:

Notetheduplicityofsomeattributessincethisisaconceptualmodel.UnfortunatelyinthebibliographyIamusingfordatamodelingthereisnoexamplesituationwhereanentityhasmorethanone"is-one" relationship, so I do not know if this conceptual model is correct, and this may be the cause of the error in the logical model, described below:

Customers table and their respective join table.

Individualandlegaltables,respectively:

Theproblemisthattoimplementthistemplate,IdonotknowwhatIshoulddosothatacustomerisjustakindofperson,physicalorlegal.MyteachersuggestedthatIcreatea"Person" entity, rather than two (legal and physical), but I encountered the same problem, since a person entity should have the CPF and CNPJ attributes at the same time.

Note: I will implement DB in SQL Server 2014 and the application will be scheduled in VB.NET.

Thank you in advance for your attention.

    
asked by anonymous 20.08.2015 / 16:28

3 answers

1

If you need to model your customers by identifying whether they are a legal or natural person, you could have just two entities:

  • Person , whose identity would be your national registry, a field called for example CpfCnpj (yes, these documents have the same character, it is natural to use the same field). This entity would have an attribute to identify whether it is a natural or legal person (filled in the database for example with F or J ). The few exclusive fields of individual or legal entity may not justify two distinct tables.

  • Client , which does not need hierarchical relationship with person but just referencing a person. To identify then whether a customer is an individual or a legal entity, you refer to the Person entity to which he is related.

If in the domain the types of client (physical / legal) really characterize different types of entities, then you would have here two classes: Client Individual and Client Corporate . More:

  

Please note that a domain where Individuals and Legal Entity are rare are separate entities. Usually we just need to differentiate them through their attributes to make some decisions during the sales process.

And it's also rare for a domain where a Person entity really exists. Usually it only appears in the code to facilitate reuse and design of the database.

  

Real domains, rather than having a Person entity, actually have Client , Employee / em> ... Some of these entities are the same person in real life but for the system they remain distinct entities because they have different purposes in the domain.

Conclusion

Most problems in modeling people appear during exercises. This modeling seems an interesting problem but in practice it is not. Look at the systems out there: they have a "Person (s)" table and that's it.

Even well-modeled systems generally do not need to distinguish physical and legal persons as separate entities but only as an attribute that characterizes them as being of one type or another; not only because it is simpler to model but because that is the real domain of the problem.

When the domain requires different entities depending on the type of person, generally what it requires are not "people" of different types but rather, "clients" of different types. In these cases (rare), "Person" remains there being the same for both physical and legal and the client is specialized in: Customer Individual and Client Corporate . p>

Suggestion

When modeling, whether by exercise or by professional need, first try to define the problem well in a business language, and then model problem-oriented rather than how you would like to implement it.

    
20.08.2015 / 19:20
3

Your problem is similar to the one I replied on this question . I'll still take the time to improve the answer:)

I honestly did not understand this join table. The CPF / CNPJ data should clearly belong to the tables of people and only. Except some misunderstanding of mine of what you want with this, this table does not make sense.

The customer will have a way to connect with the people table. You will have one column for the foreign key for the individual and another for the legal entity. Obviously only one of them will be filled in at a time. The link can be made with a natural column, such as CPF / CNPJ or a substitute (an ID), which I prefer in most cases.

Maybe you can change some of the columns that are common for both types of people and play in the customer table. The way it's done does not even make sense to have the customer table. You are not transposing the conceptual model, you, for some reason that I do not know, decided to take the data that was in the client in the conceptual model and played it for the people.

The conceptual model is already weird because it has nome in the three entities. renda_mensal in this way, it is hardly common for all types of clients.

    
20.08.2015 / 16:41
0

You can do the following:

Cliente(..., FK_PessoaFisica, FK_PessoaJuridica )

If Cliente is a Pessoa Física ... it will have FK_PessoaFisica assigned, and FK_PessoaJuridica null ...

If the Cliente is a Pessoa Jurídica ... it will have FK_Johnical Person assigned, and FK_Pessoa NullPage ...

    
20.08.2015 / 16:39