If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Avoiding Redundant Records
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 |
Thread Tools | |
Display Modes | |
|
|