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 |
#2
|
|||
|
|||
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 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? |
#3
|
|||
|
|||
Avoiding Redundant Records
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? . |
#4
|
|||
|
|||
Avoiding Redundant Records
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" wrote in message ... 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? . |
#5
|
|||
|
|||
Avoiding Redundant Records
I think I see your point, although at first reading I was a bit dumbfounded.
Conversation via email can be so difficult. At first it sounded like you were surprised I was actually trying to design my application around business rules! Further, that it was going to be up to my users, not my application, to enforce our business rules. Finally, it sounded like you were saying that if any Access features proved helpful in this task, it would be purely accidental! I believe you were actually saying that, right offhand, there is nothing I can do to the structure of my tables or their relationships to prevent unwanted records of the sorts I described. Rather, these illegal operations must be prevented by traps in my code or by using data validation rules. A perhaps easier example would be in retail sales. Let's say we offered a product for sale with the condition: limit one per customer. This would mean that for any instance of this product in the OrdersProducts join table, the Quantity would have to be limited to 1 each. Also, we would have to prohibit multiple separate instances of the same product on the same order. Further, we would have to prevent multiple orders for the same product from the same customer. These kinds of constraints would not be enforced through table structure, except to the extent of making sure we placed a field to our Products table for flagging such products. Regards, OldBlindPew "Jeff Boyce" wrote: 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" wrote in message ... 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? . . |
#6
|
|||
|
|||
Avoiding Redundant Records
Sorry if I gave you a start, there. Yes, Access (and many other tools,
including Excel, paper/pencil, etc.) can be used to handle business rules .... BUT! ... you have to do most of the work the handle the rules, using the features/functions of your tool. Your example (retail sales, limit: one per customer) is excellent. While there may be nothing built in to prevent many of those situations, you can certainly add in "traps" (?validation checks) to accomplish that. Or, if you're lucky, using something like a unique index (again, a feature of your tool) might help you reinforce the business rule. You still have to set the unique index, though! 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" wrote in message ... I think I see your point, although at first reading I was a bit dumbfounded. Conversation via email can be so difficult. At first it sounded like you were surprised I was actually trying to design my application around business rules! Further, that it was going to be up to my users, not my application, to enforce our business rules. Finally, it sounded like you were saying that if any Access features proved helpful in this task, it would be purely accidental! I believe you were actually saying that, right offhand, there is nothing I can do to the structure of my tables or their relationships to prevent unwanted records of the sorts I described. Rather, these illegal operations must be prevented by traps in my code or by using data validation rules. A perhaps easier example would be in retail sales. Let's say we offered a product for sale with the condition: limit one per customer. This would mean that for any instance of this product in the OrdersProducts join table, the Quantity would have to be limited to 1 each. Also, we would have to prohibit multiple separate instances of the same product on the same order. Further, we would have to prevent multiple orders for the same product from the same customer. These kinds of constraints would not be enforced through table structure, except to the extent of making sure we placed a field to our Products table for flagging such products. Regards, OldBlindPew "Jeff Boyce" wrote: 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" wrote in message ... 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? . . |
#7
|
|||
|
|||
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. |
#9
|
|||
|
|||
Avoiding Redundant Records
Thanks for your reply, Steve.
All of your perceptions are correct. I assume by "standardized" you mean that each of these entities has a standard set of fields. Everything is pretty well standardized until you get down to the level of Coverage Details, which I had not mentioned until my previous reply. At this level, coverage details differ by policy type, and are more subject to change over time. The Normalized approach would be to make a master list (table) of CoverageDetails, (or CoverageItems?), with a many-to-many relationship between Policies and CoverageDetails. It is more usual to say a Cert is "issued" (vs. "established") by a Producer. Your solution is more like what I expected to receive from the outset: some sort of multiplicity of join tables. I have asked before about the wisdom of combining or splitting Firms by type. Presently, all Firms are in a single table, with several Type fields to indicate what types of work the firm does. This seems to be the preferred approach. In your model, is there any reason why Insureds, Producers, and Providers couldn't all be in the same table of Firms? BTW, does anyone know why it is that if I search this forum for OldBlindPew, I only get some of my threads? Thanks again, OBP "Steve" wrote: It is not clear to me if Agreements, Certs and Policies are standardized in and of themselves. Presumably they are. It appears that an Agreement can have one or more Certs and a Cert can have one or more Policies. It appears that a Cert is (established ??) by someone identified by ProducerID and it appears that a Policy is provided by someone identified by ProviderID. Finally it appears that an Agreement is executed with someone identified by InsuredID. If all the above is true, consider this table structu TblProducerID ProducerID Producer fields ... TblProvider ProviderID Provider fields ... TblInsured InsuredID Insured fields .... TblAgreement AgreementID Agreement fields ... TblCert CertID Cert fields ... TblPolicy PolicyID Policy fields ... TblCertPolicy CertPolicyID CertID PolicyID TblAgreementCertPolicy AgreementCertPolicyID AgreementID CertPolicyID TblAgreementCertPolicyToInsured AgreementCertPolicyToInsured AgreementCertPolicyID InsuredID IssueDate etc This table structure gives you a record of a specific Agreement containing a specific set of certs where each Cert contains a specific set of policies issued to a specific Insured identified by InsuredID. Steve "oldblindpew" wrote in message ... 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 . |
#10
|
|||
|
|||
Avoiding Redundant Records
The Normalized approach would be to make a master list (table) of
CoverageDetails, (or CoverageItems?), with a many-to-many relationship between Policies and CoverageDetails. Yes! Your tables would look like: TblPolicy PolicyID Policy fields ... TblCoverageDetail CoverageDetailID CoverageDetail fields .... TblPolicyCoverageDetail PolicyCoverageDetailID PolicyID CoverageDetailID When coverage details of a policy change, you need to add the new details to TblCoverageDetail. This changes a policy so you also need to add a new record(s) to TblPolicyCoverageDetail. Using the tables I previously suggested, you can get the coverage details of an agreement in a query that includes TblPolicyCoverageDetail. In your model, is there any reason why Insureds, Producers, and Providers couldn't all be in the same table of Firms? If ALL (not most!!!) are firms with the same firm fields; yes, you can combine them into a TblFirm. Steve "oldblindpew" wrote in message ... Thanks for your reply, Steve. All of your perceptions are correct. I assume by "standardized" you mean that each of these entities has a standard set of fields. Everything is pretty well standardized until you get down to the level of Coverage Details, which I had not mentioned until my previous reply. At this level, coverage details differ by policy type, and are more subject to change over time. The Normalized approach would be to make a master list (table) of CoverageDetails, (or CoverageItems?), with a many-to-many relationship between Policies and CoverageDetails. It is more usual to say a Cert is "issued" (vs. "established") by a Producer. Your solution is more like what I expected to receive from the outset: some sort of multiplicity of join tables. I have asked before about the wisdom of combining or splitting Firms by type. Presently, all Firms are in a single table, with several Type fields to indicate what types of work the firm does. This seems to be the preferred approach. In your model, is there any reason why Insureds, Producers, and Providers couldn't all be in the same table of Firms? BTW, does anyone know why it is that if I search this forum for OldBlindPew, I only get some of my threads? Thanks again, OBP "Steve" wrote: It is not clear to me if Agreements, Certs and Policies are standardized in and of themselves. Presumably they are. It appears that an Agreement can have one or more Certs and a Cert can have one or more Policies. It appears that a Cert is (established ??) by someone identified by ProducerID and it appears that a Policy is provided by someone identified by ProviderID. Finally it appears that an Agreement is executed with someone identified by InsuredID. If all the above is true, consider this table structu TblProducerID ProducerID Producer fields ... TblProvider ProviderID Provider fields ... TblInsured InsuredID Insured fields .... TblAgreement AgreementID Agreement fields ... TblCert CertID Cert fields ... TblPolicy PolicyID Policy fields ... TblCertPolicy CertPolicyID CertID PolicyID TblAgreementCertPolicy AgreementCertPolicyID AgreementID CertPolicyID TblAgreementCertPolicyToInsured AgreementCertPolicyToInsured AgreementCertPolicyID InsuredID IssueDate etc This table structure gives you a record of a specific Agreement containing a specific set of certs where each Cert contains a specific set of policies issued to a specific Insured identified by InsuredID. Steve "oldblindpew" wrote in message ... 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 | |
|
|