Is it worth putting two foreign keys in a table?

having the tables:

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.

See:

• What are the advantages and disadvantages of using database indexes?
• Unnecessary benchmarks are a problem?
• Indexes in MySQL queries

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'