I am being asked for implementation of a cross reference table to be used in a way that I have never seen before. Thought I would see what you all think of this and see if you have suggestions on a better implementation of such. Thanks for any replies.

Here is the situation. We have a policy header table, which contains a foreign key for insurance type. This table has a child table called policy coverage and it contains a coverage code as part of its primary key, which is a foreign key to a coverage code table. They are wanting an XREF tabe of insurance types and coverage codes. What they plan on doing is they get the insurance type entered into the policy header. Then they want to look at the XREF table to see what coverages apply for that insurance type. Then insert the appropriate coverage code into the policy coverage table. Essentially, implementing a wierd sort of programmatically/rdbms controlled RI, where 1 part of the key is carried in one table and the other carried in another table.

I have included a picture of the model in the attached BMP, policy coverage. As I said this implementation seems a bit hokey to me. I've got a couple of ideas, but the one that most strikes me is in the second BMP file, with corrected in the name.

Dave Nance

first diagram looks fine to me

what's hokey about it?

As I said the way half the key from the cross reference was in header table and the other half of the key from the cross reference is held in the code table. So maintaining data integrity is half RDBMS and the other half is programmatic. With the corrected version I am ensuring that the coverage code is actually linked with the proper insurance type, rather than ensuring that the coverage code is valid, but not neccessarily linked with the insurance type. By replacing the coverage code, with the surrogate key from the cross reference table, I would now ensure that the coverage is indeed linked to the insurance type.
Dave