MySQL Junction Table as Foreign Key?

Question

I have several tables: company, customer, company_has_customer, and transaction.

company_has_customer is a many-to-many junction table using the PKs of company and customer as its PK.

In almost all situations throughout the database, both the company and customer are used to identify a record (e.g. in the transaction table).

My question is whether I should create the foreign key in the transaction table to point to company_has_customer or to the two tables (company, customer) individually?

I believe it would be best for referential integrity to send those FKs through company_has_customer to ensure that transaction records are only inserted if there is a relationship between the company and the customer. Are there any standards or conventions to support my hunch?

My second question is doing the lookup from transaction to the customer table if the FK is actually going through the company_has_customer table. There would still be an index, but should I always have to join through the company_has_customer table?

Answer 1

It depends on what you want to enforce. If a transaction can take place between a customer and a company that is not in company_has_customer the individual foreign keys are preferable. If a transaction only can take place between a customer and a company that is in company_has_customer it is better to have a foreign key to that table.

Answer 2

I would suggest to use transaction table as warehouse table.because sometime two many references also cause low performance in getting data. but if you are not in a production db situation which populate db with bulk data in seconds you can use those references and customers pk is important in transaction table. your second part of question is look up from transaction to customer have nothing to do with company_has_customer this table is used when you want to know the customer belongs to which company.