A Question on Foreign Key Constraints

Cyber-Storm
Cyber-Storm used Ask the Experts™
on
Hi there,

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.

Many Thanks,

Storm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
The problem is that your schema allows anomalies. Regardless how many FK do you have, even if physical data integrity would be correct, logical data integrity could be broken - think about the situation if Rates.EmployeeCode has been updated

I personally would change design of the system to something like that:

create table dbo.Employee
(
	EmployeeCode int not null primary key
)
go

create table dbo.Rates
(
	RateId int not null primary key,
	Rate money not null
)

create table dbo.EmployeeRates
(
	EmployeeCode int not null,
	RateId int not null, 
	
	constraint PK_EmployeeRates
	primary key clustered(EmployeeCode, RateId),
	
	constraint FK_EmployeeRates_Employee
	foreign key(EmployeeCode)
	references dbo.Employee(EmployeeCode),
	
	constraint FK_EmployeeRates_Rates
	foreign key(RateId)
	references dbo.Rates(RateId)
)
go

create unique nonclustered index IDX_EmployeeRates_RateId_EmployeeCode
on dbo.EmployeeRates(RateId, EmployeeCode)
go

create table dbo.Work
(
	WorkId int not null primary key,
	EmployeeCode int not null,
	RateId int not null, 
	constraint FK_Work_Employee
	foreign key(EmployeeCode)
	references dbo.Employee(EmployeeCode),
	
	constraint FK_Work_Rates
	foreign key(RateId)
	references dbo.Rates(RateId)	
)
go

create index IDX_Work_EmployeeCode on dbo.Work(EmployeeCode)
create index IDX_Work_RateId on dbo.Work(RateId)
go

Open in new window

Author

Commented:
So would the EmployeeRates Table only be used when you're looking for an Employee at a given Rate?  I ask this since I don't see any linkage between the Work Table and the EmployeeRates Table.

In my scenario above, an After Update Trigger on the EmployeeRate.EmpCode field would prevent any Updates (or if you do need to update it, then simply use an Update Cascade) to ensure logical integrity right?

In doing further research last night on my question, I read up a bit more on Cascading, and there it said that you must only ever have 1 way to reach a table in the chain, that would mean, that I should then only have 1 FK going from the Work Table to the EmployeeRate table since that is the most logical Contraint and then drop the FK going to the Employee Table.  Did I understand this correctly? Here is a link to the page I read about it:
http://msdn.microsoft.com/en-us/library/aa933119(SQL.80).aspx
Commented:
Yes, EmployeeRates table gives you current rate assignment. Historical information (employee + rate links) will be in the Works table. I could be wrong - I'm not 100% sure that I understand the requirements. Problem I see with your design (again, if I understand requirements correctly) that you're mixing current (Rates) and historical (Work) information. What will happen if Rate row referenced by Work row becomes unavailable and should not be used anymore at the future? In case if you separate Rates/EmployeeRates tables, you'll have to delete one row from EmployeeRate table and leave everything else intact. all Historical references (from Work) would remain the same.

Another problem I see is that you have to maintain separate Rate rows for multiple employees even if rate is the same. So you can have: (EmpA, 10), (EmpB, 10), (EmpC,10). What if you need to modify the rate? You'll need to update all rows instead of one not even mention that you should handle Work table references.

You're right about cascading. On other hand it's not necessarily to  implement cascading actions on FK level. You can use triggers (not the best way), you can handle it within data access tier (think about SP: DeleteEmployee), etc.

My point is - you don't need to implement FK just because you can implement FK. Do design in the way that covers business requirements and benefits the system.

In general, foreign keys are good thing - those helps query optimizer - check, for example: http://aboutsqlserver.com/2011/02/24/views-in-microsoft-sql-server-part-1-joins-hell/ . On other hand FK introduce additional overhead on reference integrity check during data modifications. In most part of the cases it's not an issue but in some highly loaded OLTP systems it's good idea to avoid them. FK also cannot be used in some cases (table partition switch). Check this one too: http://aboutsqlserver.com/2011/01/20/referential-integrity-part-1-foreign-keys/


Author

Commented:
Thanks very much, after much reading, I think I'm understanding things a lot better now.

I guess what I'll most take away from this is "Just because you can create a FK, doesn't mean you should".  Valuable insight, thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial