Lets say I have 3 tables, Employees, Rates and Work
Employees has a PK on the Employee Code.
Rates has a Foreign Key to the Employees table for the Employee Code. It's PK is on the Employee Code and the Rate (since there are multiple rates per employee).
Finally the third table holds all the Work the Employees do as well as the Rate they're doing it at.
Should this table have a Foreign Key to both the Employee and Rates table or only the Rates table since it already has a FK to the Employee table?
Could you also please go into a little more detail as to why you say either yes or no (a simple link to a page explaining what is best would be fine too).
I'm just not sure if you're overdoing it if you're adding 2 FK's or if that's the right way to ensure your data integrity.