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?
Best Answer
It depends on what you want to enforce. If a
transactioncan take place between acustomerand acompanythat is not incompany_has_customerthe individual foreign keys are preferable. If atransactiononly can take place between acustomerand acompanythat is incompany_has_customerit is better to have a foreign key to that table.