MySQL Junction Table Foreign Keys

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?

SO isn't really appropriate for opinionated design discussions. — Barmar, Sep 12 '14 at 21:46
Thanks Barmar, I modified the question to be less opinionated. I'm really looking for best-practices in this type of situation, but I've got strong hunches. — Kyle Johnson, Sep 12 '14 at 21:51
Maybe you'll have better luck over at dba.stackexchange.com. — Barmar, Sep 12 '14 at 21:55
I knew stackoverflow felt a little wrong, I forget that there is a giant network of sites for these things. — Kyle Johnson, Sep 12 '14 at 22:22

score 0 · Answer 1 · answered Sep 12 '14 at 22:33

0

Here is how to think about the problem. Does the transaction have a separate relationship to company and customer? Or, is the transaction specific to the company/customer relationship forever?

I would guess the first is the case.

The specific situation to think about is: what happens if the relationship between a customer and a company changes after the transaction takes place? Does the transaction retain the original company or does it switch to the new company? Or is it invalidated?

If you reference the company_has_customer table, then the relationship might just disappear. In fact, you probably want the relationships to remain as they were when the transaction occurred, so go for two foreign key relationships.

answered Sep 12 '14 at 22:33

Gordon Linoff

1,242,037
58
646
786

The relationship between company and customer exists in many proposed tables. Essentially the data between customer and company will always belong to that company/customer combination. Enough so that it may even be simpler to add a FK to the customer table with the owning company ID. M2M just ensures some level of future options. – Kyle Johnson Sep 12 '14 at 22:44
@KyleJohnson . . . In that case, the relationship is to `customer` and you can look up the company from there. – Gordon Linoff Sep 12 '14 at 23:01
Yeah, it's just up in the air where the real boundary lies. I envision that some customers may belong to multiple companies, or that multiple companies may manage the same customers. It's hard to work in all the little details like that. – Kyle Johnson Sep 13 '14 at 01:14

MySQL Junction Table Foreign Keys

1 Answers1