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: Steve on 26 Jan 2010 19:58 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 structure: 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 santus(a)penn.com "oldblindpew" <oldblindpew(a)discussions.microsoft.com> wrote in message news:B7EBDB74-78E6-4E35-93C9-A2361F82C5AD(a)microsoft.com... > 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: oldblindpew on 27 Jan 2010 11:05 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 structure: > 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 > santus(a)penn.com > > > > > > > "oldblindpew" <oldblindpew(a)discussions.microsoft.com> wrote in message > news:B7EBDB74-78E6-4E35-93C9-A2361F82C5AD(a)microsoft.com... > > 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: Steve on 27 Jan 2010 14:58 <<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 santus(a)penn.com "oldblindpew" <oldblindpew(a)discussions.microsoft.com> wrote in message news:7DDD7C16-C534-417A-9FB0-CC5C84E8F49C(a)microsoft.com... > 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 structure: >> 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 >> santus(a)penn.com >> >> >> >> >> >> >> "oldblindpew" <oldblindpew(a)discussions.microsoft.com> wrote in message >> news:B7EBDB74-78E6-4E35-93C9-A2361F82C5AD(a)microsoft.com... >> > 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: BruceM via AccessMonster.com on 28 Jan 2010 13:14
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/Forums.aspx/access-tablesdbdesign/201001/1 |