The model represents sales management, through the tables Personas
, Funcionarios
, Clientes
, Sucursal
, Venta
and other tables. But my doubt is associated with the ones I just mentioned. (attached image)
The restrictions are as follows:
- Well, an official and client are clearly people. (An official is ONE person and a client is ONE person).
- A branch office has many officers, but an officer can only work in one office.
- An official can generate many sales, and a sale can be associated with a seller.
The model must also answer a couple of queries. Among them is the following (and the first one I tried that didn't work). How many people are officers of a branch?
Having commented on all of the above, I deduce that I have a problem and doubts regarding the inheritance relationship that I tried to establish between the tables Personas
, Funcionarios
and Clientes
.
Well, the first thing to notice is that I leave the officials table with its unique id (rut, since it is nothing more than the unique identification number of each person in my country, therefore pk), with the cod_person and cod_sucursal that can be repeated. Analyzing how the table looks Funcionarios
, I think it translates as follows: "A person can have several (and different) jobs associated with him as an official in more than one branch." Which is a mistake, that's not what my model is intended to represent. (plus you didn't even manage to answer the previous query).
So here is my question. How do I solve it? What does an inheritance relationship really look like in this case? How do I get all the restrictions I mentioned at the beginning to be respected?
Well, you really have nothing to solve, your modeling of reality is not wrong. On the other hand, you do not need to materialize the specialization you are referring to in independent tables, since the cardinality you have defined allows you to replace them with the fields that you already have in
Ventas
and the fieldcod_sucursal
thatFuncionarios
you could perfectly propagate toPersonas
. It's not that it's the best site, because it's not an attribute that it shares withClientes
, but leaving it atNULL
ya would allow you to discriminate it fromFuncionarios
.This inheritance or specialization is done when they
Funcionarios
haveClientes
specific attributes that should be recorded in different tables. But note that itrut
is even common to both, andcod_sucursal
anyone can understand that it remains inPersonas
without remaining empty representing a waste of resources.I propose, therefore, to eliminate the tables
Clientes
andFuncionarios
, creating the fieldsrut
andcod_sucursal
inPersonas
. Thus taking advantage of cardinality to reduce the complexity of your data model. I insist that as you have it, it is not bad, you simply have more tables because you do not exploit the cardinality that you have defined. In order for cardinalities to be respected, all you have to do is define indexesUNIQUE()
. In my proposal you do not need them because those restrictions are implicit in the design.The query you raise: How many people are employees of a branch? , you solve it with a simple:
In your model it would suffice to:
To find out how many employees each branch has, you would do: