I have a problem when making some queries to my database. I have the following tables:
The table cliente
as the one of empleado
I have as primary key the one idpersona
of the table persona
. I have separated the attributes in a table persona(nombre, cedula, direccion)
because they are attributes that the client has as well as the employee. But it seems that is not the right way to do it. I'm going to put here a couple of queries that I try to do:
Get
inventa
,fecha
,importe
, andnombre
from the buyer of a purchase made in February. (this query is no problem.)SELECT v.idventa, v.fecha, v.importe, p.nombre as comprador FROM ventas as v join cliente as c on v.cliente_persona_idpersona = c.persona_idpersona join persona as p on p.idpersona = c.persona_idpersona WHERE v.fecha = 'febrero'
How to add to that previous query the name of the person who made the sale, if both the seller and the client are linked to the same table
persona
, which has the attributenombre
.According to my model it does not allow the same employee to make a purchase. In other words, if there is only one person in charge of making sales in the store, and suddenly he needs to buy something as he registers it, if
ventas
I cannot put the same seller id as a buyer in my table, should I separate a tablecliente
with all his attributes and a tableempleado
with its attributes even if they are equal to those ofcliente
?
In other words, how can I make a seller able to buy as well and register it in the database?
Adding to Marcos Gallardo's excellent answer, if you don't need to return any of the specific fields from the tables
empleado
andcliente
, then you don't need to include these tables in your query.In your case, you can simplify the query to:
If you need fields like
cliente.ultima_compra
or something like that in theSELECT
, then you do need to add the additional table to the query in order to access that field.Now, anticipating that there are going to be situations where you are going to need to include the tables
cliente
andempleado
the query, you have to admit that it can get very annoying having to do joins with so many tables. Also, although it is possible to join the tablepersona
with different aliases in the same query, it can get a bit confusing, and can easily lead to mistakes.One thing you can do, to simplify the use of your tables, is to create views that hide the fact that you share customer and employee data in the table
persona
. Doing this is not a violation of the normalization principles you have put into practice.Example:
That way, no matter what fields you need, you can use views for your queries, and it's less confusing that way. For example, your query using the views would be:
The solution is joining (
join
) the tableempleado
and again the tablepersona
(with another alias) but this one again indicating theid
employee's alias.For example, like this:
As @sstan says in his comment , your table schema apparently doesn't seem to have any limitations, so it should be trivial, that a seller is also a buyer.