Is it worth putting two foreign keys on a table?

Asked

Viewed 220 times

3

having the tables:

  1. Companies
  2. Customers
    • empresa_id: foreign key
  3. Sales
    • cliente_id: foreign key

and the relationships are:

  • Companies and Clients => 1:N
  • Customers and Sales => 1:N

Considering that I will need to list all company sales, it is worth creating a second foreign key in the sales table so I can do:

SELECT * FROM `vendas` WHERE `empresa_id` = 'x'
mysql sql foreign-key
  • 3

    It depends on the necessity of the project. If you need to filter the sales by companies, you should put.

2 answers

3


The description should use yes, but not right. Need the performance? One thing I see a lot in reports is not need, but depends on the case. The more indexes you place 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 make a JOIN without it can make everything much slower.

It’s a matter of measurement. You do and measure and see whether or not it pays for your need.

Behold:

  • I haven’t had time to read the articles you gave me, but after your reply, I did some tests and opted for the use of the extra key, because the sales table will have an absurd amount of information and soon performance will be a priority.

-1

Considering there’s already a foreign key empresa_id on the table Clientes and a sale presupposes at all times a customer (through the field cliente_id, I suppose that’s NOT NULL), there will be no need to create a foreign key for the enterprise in the table Vendas. Indeed, duplication of information (as well as code) is never good practice.

As already mentioned, the consultation SQL 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'
  • 1

    In my view this "never a good practice" was out of context. If my query took 1 minute to finish with JOIN, but it would take 10 seconds if I owned the foreign key and I need it to be as soon as possible, it would not be a good practice i optimize with foreign key?

  • That’s what I was thinking @Andersoncarloswoss, because the sales table will receive ALL sales from ALL companies... will grow very fast and one hour performance will be a requirement.

Browser other questions tagged mysql sql foreign-key

You are not signed in. Login or sign up in order to post.