Hello friends, I'm still here trying to advance in this database and now I find myself with a new concern, because it turns out that I decided to expand my purchase database with an account, well, I didn't look for something similar on the net but I can't find anything to help me solve this problem, first of all I will explain the tables that I have added. Within my store database, a client can have an account , but there are at least two types of clients in my country, these are, ( Individuals and legal persons ). Well, a natural person is anyone and an institution or something like that are legal entities.
Taking into account the brief introduction, I will explain what the situation is.
I want to register an account in the name of a College, which in this case is ( Legal ), but every legal client has a legal representative, that is why I related Legal to the natural_person table , which contains the attributes of a person and thus avoid having to add the attributes names, surnames etc of the legal representative. So far at least for me it seems to be fine, but now the big problem is to make the invoice in the name of the institution!. According to my model it is not possible because I only have the client related to the invoice and the invoice is supposed to go out in the name of the institution and not of the legal representative, I am in doubt as to whether to add the attributes (Name , Ruc_o_cedula) so that at the time of making the invoice obtain the data of the institution through the legal representative and insert it in the invoice table, I don't know if this is correct.
Well friends, that's the problem, to all this I want to add that before asking the question in the forum I went to a former IT teacher from the school where I studied to see what he thought about it, he suggested that instead of segmenting so much the client entity will simply load all the attributes something like this...
then in that table, if it is a natural client, I register it normally, but if it is a legal one, in the name attribute , enter the name of the institution, in the ruc_o_cedula , enter the RUC, etc. etc. leaving attributes blank such as surnames... ... that was the solution that he proposed to me, but I think it is wrong because I would already be violating the normalization rules if I leave attributes blank ... I hope you have patience and can help me. Greetings.
Let's clarify the concepts:
Every INVOICE is issued in the name of a CUSTOMER. The CLIENT can be a NATURAL PERSON or a LEGAL PERSON. In addition, every LEGAL PERSON has an associated LEGAL REPRESENTATIVE, who is a NATURAL PERSON.
In an object design, the most natural thing seems to be to create an abstraction that encompasses both types of person: let's say PERSONA (abstract), of which NATURAL PERSON and LEGAL PERSON are descendants.
Now, there are (at least) three ways to implement inheritance in relational databases, and each has its drawbacks.
The simplest thing in this case (and in most cases) seems to me to be the " a single table for the entire hierarchy" strategy , which would be equivalent to having a single table
persona
with all the necessary fields for both specific types, plus a discriminating column (tipo_persona
) that tells me if it is a LEGAL PERSON or a NATURAL PERSON. It's not quite elegant/efficient/normalized (there are fields that will be blank, I must allow NULLs), but the advantage is simplicity in queries and modifications.It is not very different from what was proposed to you in that forum, but it seems preferable to me that this abstraction is NOT implemented in the table
cliente
, because the concept of legal and natural persons is broader, and can potentially be needed for other "persons" that are not are customers (example: suppliers). In the tablecliente
I would only have a reference topersona
. In addition, the fieldpersona.representante_legal
(only used if ittipo
corresponds to a LEGAL PERSON) would refer to another record of the same tablepersona
(but this time it should correspond to a NATURAL PERSON).If you feel comfortable, you can define a view (let's say:
cliente_con_nombre
) that has the logic to extract the name to use for printing invoices.In case you are interested (and to learn), I explain the other alternative implementations:
One table for each concrete class I have two tables for the two types of person (as in your original design), and in the table
cliente
I have two referencescliente.id_persona_natural
andcliente.id_persona_juridica
(one and only one will be NULL). Disadvantage: it is not quite normalized, the query is a bit more complicated, and I have to repeat everything if I have to reference "person" (generic) in another table.One table per concrete class, with discriminator (variant of the previous one). Instead of two references I have only one
cliente.id_persona
plus a discriminating columncliente.tipo_persona
that tells me if it is legal or natural. Disadvantages: the reference can point to different tables, which is unorthodox, and prevents me from using referential integrity; queries are even more complicatedOne table for each class, including abstract . I add a table
persona
that apart from the PK only has common fields (none in this case?); the tablespersona_juridica
andpersona_natural
add a column that references thepersona
"parent". Each recordpersona
has one and only one record (legal or natural) pointing to it. The tablecliente
only has a reference topersona.id
. Disadvantage: although it is the most standardized, it is also the most complicated to make queries and modifications.I add that these alternatives (and especially the last) are more attractive when the number of concrete classes is large, or may increase in the future. This doesn't seem to be the case here, which would be a reason to prefer the "all in one table" solution.
I would tell you that the main problem that is holding you up is the design of the table
cliente
, in particular its primary keyidpersona
. This design prevents you from defining that a client can be a legal person . In fact, it can be seen in your model that there is no line between tablecliente
and tablejuridico
. That is not right.What you need to do is assign a different primary key to the table
cliente
that simply serves to identify a record in the tablecliente
. And then apart, you define 2 columns in the same table,idpersona
andidjuridico
with foreign keys to their respective tables (persona_natural
andjuridico
). The idea is that for each record in the tablecliente
, you assign a value to one of the 2 fields (idpersona
oridjuridico
) to establish what type of customer it is.Example:
If necessary, you can define a trigger to ensure that you can only have one value either in
idpersona
oridjuridico
at a time. Normally, this is done with acheck constraint
, but they don't work with MySQL.Since there is some complexity in doing a query to the table
cliente
having to conditionally join with the other tables, it is worth hiding that complexity inside a view. Something like this:Another point, is that table
cuenta
should have a fieldidcliente
instead ofcliente_idpersona
(same for tablefactura
). Furthermore, I don't think the tablecuenta
should have a fieldtipo_cliente
. If anything, maybe you can move it to the tablecliente
, and it can serve as a discriminator, but it is not strictly necessary, especially if you use the view that I proposed above that already includes a discriminator.Here is a test script to demonstrate how you could put the above points into practice: