greg (8/19/2008)
Thanks Avamin and Michael Valentine Jones for the quick reply...both of your answers are immensely helpful...Just a few follow-on comments.
I initially had CompaniesID in the EarningsMiscWrk record, but then I received the following comment from the ASP.NET forum on a Dynamic Data issue:
"...While this may work, I'm not sure that this is necessarily the right answer for you. If I understand your schema correctly, what I find unusual about it is that it stores the Company_ID redundantly: the earning record has it, and then the earning record's paycode has it as well, opening the door for them to be different.
Instead, would it be conceivable to remove CompanyID from the Earning Record, and have it be implied by selected PayCode? Of course, that would mean that you only get one drop down for Company/PayCode, while you may prefer to first select the company and then select the valid pay codes within it?"
That's why I took it out; so it's not bad practice to reinstate CompaniesID back into the EarningsMiscWrk record...?
Also, is it preferable to create the unique constraint on the FK tables via composite primary key (if so, do I maintain the auto-incr Identity field as is..?), or in some other fashion (i.e., index)...?
It is not really redundant, since it is being used to identify the fact that it is for only one particular company and to prevent references to lookup items that are not for that company.
You should leave your primary keys the way they are, and just add the additional constraints with the ID and CompanyID. The constraints are required because a FK must reference a PK, unique constraint, or unique index. The FK reference is what will ensure references to rows with only the correct CompanyID.
I have used this design in a number of situations where it is necessary that FK reference only a particular set of rows.