I have several tables:
company_has_customer is a many-to-many junction table using the PKs of
customer as its PK.
In almost all situations throughout the database, both the
customer are used to identify a record (e.g. in the
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 (
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