having the tables:
- company_id: foreign key
- client_id: foreign key
where the relations are:
- Companies and Clients = > 1: N
- Customers and Sales = > 1: N
Considering that I will need to list all the sales of the company, it is worth creating a second foreign key in the sales table so that I can do:
SELECT * FROM 'vendas' WHERE 'empresa_id' = 'x'
By the description should use yes, but it is not right. Do you need the performance? One thing I see a lot in reporting is not needing it, but it depends on the case. The more indexes it puts the slower it gets to update the tables and this can be critical in certain environments.
It is perfectly possible to do without having the foreign key, but making a
JOIN without it can make everything much slower.
It’s a matter of measurement. You do and measure and see if you compensate or not for your need.
Well, it depends a lot on the situation if you need more performance, it’s better not to do it, but it’s okay if you do not have to.
If it is not the case that you need performance, the best choice is to make maintenance easier, so it’s up to you.
This would be the other way and in my case I would do so, because I do not think this would make maintenance difficult:
SELECT * FROM 'vendas' INNER JOIN clientes ON vendas.clientes_id = clientes.id WHERE 'clientes.empresa_id' = 'x'
Given that a
empresa_id foreign key already exists in the
Clientes table, and a sale always presupposes a client (via the
cliente_id field, I assume it is
NOT NULL ), you do not need to create a foreign key for the company in the
Vendas table. In fact, duplication of information (as well as code) is never good practice.
As already mentioned, the
SQL query to filter by company is simple:
SELECT * FROM Vendas V INNER JOIN Clientes C ON V.cliente_id = C.id WHERE C.empresa_id = 'X'