Avoiding Redundant Records
in [Tables]
|
Prev: como corregir el error:No se reconoce el formato de base de datos
Next: How to change the path to a split database file
From: oldblindpew on 21 Jan 2010 13:27 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
From: Piet Linden on 21 Jan 2010 15:17 On Jan 21, 12:27 pm, oldblindpew <oldblind...(a)discussions.microsoft.com> 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 dont 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 doesnt 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?
From: oldblindpew on 21 Jan 2010 17:53 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? > . >
From: Jeff Boyce on 21 Jan 2010 19:35 It sounds like you are describing the "business rules" of your operation. It wouldn't matter if you were using Access or Excel or paper and pencil, those rules would apply (e.g., no customer carries more than one GL policy). I'm not aware of any built-in business rule enforcer in MS Access. I believe you'll need to add the validation checks to enforce those rules. In some of your situations, using a unique index on multiple fields could be a way to use Access features to enforce your business rules ... but that's just plain lucky! You'll probably need to figure out some edits/validation tests for your form, to prevent the users from doing something your business doesn't permit. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "oldblindpew" <oldblindpew(a)discussions.microsoft.com> wrote in message news:CC037104-E3A4-4B28-BEFE-22E286558AA8(a)microsoft.com... > 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? >> . >>
From: Piet Linden on 21 Jan 2010 19:55
On Jan 21, 4:53 pm, oldblindpew <oldblind...(a)discussions.microsoft.com> 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. |