martin-1049293 (7/14/2011)
Great explanation - however in this case I think you would agree it would be prudent to strre the Rate_Applying and Years_Applying in the Current Assignment table to prevent them being implicitly changed retrospectively when they were changed in the PayGradeDetails table. This appears to be a violation but is essential to retain correct data over time.
I think I would keep the rate history for each person in the PayGradeDetails table, and assignment history in a third table, if there was any question of trying to reconstruct historical charges. But the ability to do historical reconstructions is non-trivial, and doing it safely requires extra normalisation.
Of course concern for time-dependent data is what led Chris Date to invent his version of 6NF, and maybe one day there will be an SQL Server Central article on that (if there is it won't be written by me - I have no practical experience of using 6NF, so it's just theory to me, and I don't believe people should write articles on parts of normalisation that they have no practical knowledge of).
Tom