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

Question

I’m creating a data model where a client entity is also a person, legal or physical. However, in the state in which it is found it will not be possible to implement it physically, because I am not able to create the logical model that correctly describes a way to have an entity inherit attributes from two different entities. Behold.

First, I made the conceptual representation of the situation described in the following way:

The duplicity of some attributes can be seen as a conceptual model. Unfortunately in the bibliography that I’m using for data modeling there is no situation-example where an entity has more than one "is-one" relationship, so I don’t know if this conceptual model is correct, this may be the cause of the error in the logical model, described below:

Client table and its respective join table.

Physical and legal persons tables, respectively:

The problem is that to implement this model, I don’t know what I should do so that a client is just a type of person, physical or legal. My teacher suggested that I create an entity "Pessoa", instead of two (legal and physical), but I found the same problem, because an entity person should have the attributes CPF and CNPJ, at the same time.

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

Thank you for your attention.

Answer 1

Your problem is similar to the one I answered in that question.

I honestly did not understand this junction table. CPF/CNPJ data should clearly belong to the people tables and only. Except for some misconception of what you want with this, this table makes no sense.

The client himself will have a way to connect with the people table. You will have a 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 connection can be done 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 the two types of person and play at the customer table. The way it’s done, it doesn’t even make sense to have a client table. You are not transposing the conceptual model, you, for some reason that I do not know, decided to take the data that were client in the conceptual model and threw to people.

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

Answer 2

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

• Person, whose identity would be his national register, 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 (populated in the database for example with F or J). The few exclusive fields of individual or legal person may not justify two different tables.

• Client, that does not need a hierarchical relationship with a person, but just reference a person. To identify then whether a client is a natural or legal person you turn to the entity Person to which it 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: Cliente Pessoa Física and Legal Person Client. But:

Note that it is a rare domain where Cliente Pessoa Física and Legal Person Client are distinct entities. Generally we only need to differentiate them through their attributes to make some decisions during the sale process.

And it’s also rare for a domain where there really is an entity Person. It usually only appears in code to facilitate reuse and database design.

Real domains, usually instead of having entity Person, actually have entities Client, Official, Supplier... 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.

Completion

Most problems when 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 table "Person(s)" and ready.

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

When the domain requires distinct entities according to the type of person, usually what it requires are not "people" of different types but, for example, "clients" of different types. In these (rare) cases, "Person" remains there the same for physical and legal and the client is specialized: Cliente Pessoa Física and Legal Person Client.

Suggestion

When modeling, either by exercise or by professional need, first try to define the problem well, in a business language, and then model guided by the problem and not by the way you would like to implement.

Answer 3

You can do the following:

Cliente(..., FK_PessoaFisica, FK_PessoaJuridica )

If the Cliente for a Pessoa Física... he will have assigned Fk_person, and null Fk_person...

If the Cliente for a Pessoa Jurídica... it will have Fk_person assigned, and Fk_person null...