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 |
#12
|
|||
|
|||
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 . |
#13
|
|||
|
|||
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 . |
#14
|
|||
|
|||
Avoiding Redundant Records
I will reply in this part of the thread, as it is the most recent. It's
somewhat difficult to say whether firms should be in one table or split into several. As an example, Customers and Vendors are both companies with addresses, etc., but are likely to contain substantially different types of data, so it makes sense in most cases that they be in the same table. On the other hand, supply vendors and service vendors are both vendors, even though you may need certain fields for one and not the other (shipping information may not apply to a service vendor, for instance). I part company here with Steve's insistence that *all* of the fields need to be the same, but I think it is true they should be substantially similar. I do not really understand the concepts of Producer, Provider, and Insured as they apply to your situation. If the Insured is the customer and the other two are suppliers, the tables should be split up to that extent, I would think. In any case, if you are using a combo or list box you probably want to limit the list only to those who could by Providers or Producers or Insureds. Regarding the forums search, if you are using the Microsoft interface it can be clunky. A Google Groups search is more likely to return the information. oldblindpew wrote: 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 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 [quoted text clipped - 113 lines] . -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201001/1 |
|
Thread Tools | |
Display Modes | |
|
|