A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Avoiding Redundant Records



 
 
Thread Tools Display Modes
  #11  
Old January 27th, 2010, 01:58 AM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Avoiding Redundant Records

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



  #12  
Old January 27th, 2010, 05:05 PM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default 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  
Old January 27th, 2010, 08:58 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default 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  
Old January 28th, 2010, 07:14 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:10 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.