View Single Post
  #5  
Old January 22nd, 2010, 01:55 AM posted to microsoft.public.access.tablesdbdesign
Piet Linden[_2_]
external usenet poster
 
Posts: 280
Default Avoiding Redundant Records

On Jan 21, 4:53*pm, oldblindpew
wrote:
CertsPoliciesID is autonumber and therefore the unique primary key for the
junction table. *A unique index on the combination of CertID and PolicyID
would prevent redundant Cert/Policy pairs.

But I am also concerned with redundant Agreement/Policy pairs. *It is
acceptable for an Agreement to have more than one Cert, but not that the same
Policy should appear on more than one of their Certs. *Enforcing Cert/Policy
uniqueness alone doesn't prevent this, and the uniqueness of the
CertsPoliciesID key adds nothing. *

Similarly, I am concerned to prevent improper combinations resulting from
policy types. *No Insured party is going to carry two General Liability
Policies. *If we try to attribute two different GL policies to the same
Insured, either by assigning the two policies to the same Cert, or by
assigning them to two different Certs that are in turn tied to the same
Agreement, something is wrong.

Thanks,
oldblindpew

"Piet Linden" wrote:
You could create a unique index on the combination of (CertID,
PolicyID) in the CertsPolicies table. *Nothing wrong with that. *Then
if your CertsPoliciesID is an autonumber and set to be unique, you
should have everything, right?
.


Another way of doing the validation is in the BeforeInsert event of
the form. You could do the checks there and if no rules are violated,
allow the insert. Other than that, I'm out of ideas.