View Single Post
  #2  
Old January 21st, 2010, 09:17 PM posted to microsoft.public.access.tablesdbdesign
Piet Linden[_2_]
external usenet poster
 
Posts: 280
Default Avoiding Redundant Records

On Jan 21, 12:27*pm, oldblindpew
wrote:
It is my understanding that surrogate keys are generally recommended to
ensure uniqueness of records. *Is it not true that using surrogate keys
implies taking extra precautions to prevent duplicate records? *I mean, with
surrogate keys there is nothing to prevent the proliferation of multiple
records all containing the same data, but each having a unique key.

I would appreciate your help with this in the following context:

AGREEMENTS
AgrmtID (PK)
InsuredID
Agrmt fields…

CERTS
CertID (PK)
AgrmtID
ProducerID
Cert fields…

POLICIES
PolicyID (PK)
InsuredID
PolicyTypeCode
ProviderID
Policy fields…

CERTSPOLICIES
CertsPoliciesID (PK)
CertID
PolicyID

Note: *Any fieldname ending in “ID” is a surrogate key.

An Agreement can have zero or more Certs; a Cert pertains to only one
Agreement, so this is a one-to-many relationship. *Each Cert can have one or
more Policies; the same Policy can be on different Certs, so this is a
many-to-many relationship, hence these two tables are joined by the
CertsPolicies table.

We don’t want the same Policy to appear more than once on the same Cert.. *I
believe this can be accomplished fairly easily by setting up CertID and
PolicyID as a multi-field unique index in the junction table.

We also have to ensure that the user doesn’t inadvertently relate any one
Policy twice to the same Agreement through the use of a second Cert. *In
other words, we do not want to see the same Policy on two different Certs for
the same Agreement. *How would this be accomplished?

A fundamental assumption is that no Insured will ever have more than one
Policy of a given type. *How would I guarantee that not more than one Policy
of a given type (PolicyType Code) ever appears on any Cert? *How would I
guarantee the same thing for any two Certs assigned to the same Insured?

Thanks,
OldBlindPew


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?