Insurance Certificates Database

Things have slowed down in my department, so I'm back to working on my
Contracts Administration database. When I left off, I was mired in designing
tables for insurance certificates, unable to decide how far to go with
normalizing. Under-normalizing results in large spreadsheet-like tables,
which I am reliably informed are anathema to Access. On the other hand, I
believe it is possible to over-normalize almost anything until the data is so
scattered into many interrelated tables that performance is compromised, not
to mention the unguessed sorrows to be had in writing VBA procedures under
such conditions.

I believe the path forward must lie more in understanding our business
needs, what we want our application to be capable of doing, rather than
slavishly following the mantra of Normalize, Normalize, Normalize.

I have searched this site without much success, however, does anyone out
there have any experience designing an ADB for tracking insurance
certificates for compliance with subcontract requirements?

Thanks,
Pew

If you'll provide a description of your table structure and relationships,
folks here may be able to offer more specific suggestions.

.... and even if someone has a DB for "tracking insurance certificates for
compliance ...", you'll still need to deconstruct what they've done to make
sure their situation and yours are reasonably matched!

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
...
Things have slowed down in my department, so I'm back to working on my
Contracts Administration database. When I left off, I was mired in
designing
tables for insurance certificates, unable to decide how far to go with
normalizing. Under-normalizing results in large spreadsheet-like tables,
which I am reliably informed are anathema to Access. On the other hand, I
believe it is possible to over-normalize almost anything until the data is
so
scattered into many interrelated tables that performance is compromised,
not
to mention the unguessed sorrows to be had in writing VBA procedures under
such conditions.

I believe the path forward must lie more in understanding our business
needs, what we want our application to be capable of doing, rather than
slavishly following the mantra of Normalize, Normalize, Normalize.

I have searched this site without much success, however, does anyone out
there have any experience designing an ADB for tracking insurance
certificates for compliance with subcontract requirements?

Thanks,
Pew

Thanks for reply.

Insurance certificates generally appear as a fairly standardized form, known
as the Acord form. To the user, this certificate looks like one entity, with
all its fields belonging in one table, but I have been warned over and over
that Access cannot handle this kind of approach. (I still don't understand
why it is better to store a reference to a table containing a value, rather
than just storing the value itself. It seems we are using three fields and
two tables to do what could be done by one field in one table. And if the
same value appears in different records, so what? Computers are supposed to
have plenty of muscle for this sort of thing).

Anyway, each Firm for which we require insurance will have zero, one or more
Certificates, and each Certificate describes one or more Policies. Therefore
we need tables for Firms, Certificates, and Policies, with one-to-many
relationships.

FIRMS
PK IdFirm
Firm fields...

CERTS
PK IdCert
FK IdFirm
Cert fields...

POLICIES
PK IdPolicy
FK IdCert
Policy fields...

The Cert fields and Policy fields seem to fall into two categories. For
lack of better terms I will refer to them as the Definite and the Indefinite.

Definite Cert Fields:
FK ProducerCode (Id for the insurance agency that produces the certifcate)
Cert Date (date certificate was produced or printed)
FK CertHolderCode (Id for the firm holding the certificate, which should be
us)

Indefinite Cert Fields:
NOCDays (the number of days Notice of Cancellation)
NOCChange (do we also receive Notice of Material Change in coverage?)
NOCStrikeEndeav (are the words "Endeavor to" stricken from the NOC?)
NOCStrikeFailure (is the Failure clause stricken from the NOC?)
Future Fields... (unknown)

The Definite Cert fields unquestionably go in the Cert table, but the
Indefinite ones seem more subject to both repeating values, and to growth or
change in the list of fields, and therefore may belong in a separate
many-to-many table. On the other hand, the Acord form is pretty stable, and
in the unlikely event of change, how bad would it be to just add more fields
to the Certs table?

Another question right here. Notice the Certs table has foreign keys for
three different firms, namely the Insured Firm, the Certificate Producer, and
the Certificate Holder. Right now, I have all firms, regardless of type, in
a single table. How can I have multiple foreign keys back to one common
table? Won't this confuse the daylights out of Access? I notice in the
Northwind database there are separate tables for Suppliers and Customers. I
thought I was doing right to put all my firms in one table. Was this a
mistake?

Definite Policy Fields:
FK InsurerCode (Id for the insurance firm issuing the policy)
FK PolicyTypeCode (Id for Policy Type, ie GL, Auto, Excess, Workers Comp, etc)
Policy Number
PolicyDateEffective
PolicyDateExpires

Indefinite Policy Fields:
Type...
Basis...
Scope...
Limits...
Endorsements...

As was the case with Certs information, Indefinite Policy Fields are subject
to repeating values and to variable and indeterminate field lists. Different
types of policies have different lists of fields, so there has to be some way
to prevent inappropriate associations. For example, an Additional Insured
Endorsement might apply to a General Liability policy, but not to an Excess
Liability Policy. At this point, all our fields seem fixed, but who knows
what changes the future may bring? Again the question is whether to take a
chance a cram all presently known fields into the Policies table, or set up a
many-to-many relationship between the Policies table and a PolicyDetails
table.

Another, less normalized, approach would be to create separate tables for
each type of policy, and just put the appropriate fields in each table. This
would solve the problem of inappropriate associations between Policies and
Policy Details, but it would be less normalized and less flexible. For
example if the need arose to begin requiring a new type of policy for some or
all of our subcontractors, this would mean creating an entirely new table.

This why I asked about others with experience in this same area, because the
"right" structure depends on what you plan on doing with the data. I wanted
to compare notes with someone else to see how they approached the task.

Please note that capturing the information from the Acord form is barely one
third of the task. We also have to specify our insurance requirements in
detail for each subcontractor. Most insurance requirements will be the same
from one subcontract to the next, but any of them may differ, so each
subcontract must have its own set of required values. The subcontractor's
insurance certificates are then checked against those required values. If
the certificate is non-compliant, we want to report precisely which
parameters are at fault.

Thanks,
Pew

"Jeff Boyce" wrote:

If you'll provide a description of your table structure and relationships,
folks here may be able to offer more specific suggestions.

.... and even if someone has a DB for "tracking insurance certificates for
compliance ...", you'll still need to deconstruct what they've done to make
sure their situation and yours are reasonably matched!

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
...
Things have slowed down in my department, so I'm back to working on my
Contracts Administration database. When I left off, I was mired in
designing
tables for insurance certificates, unable to decide how far to go with
normalizing. Under-normalizing results in large spreadsheet-like tables,
which I am reliably informed are anathema to Access. On the other hand, I
believe it is possible to over-normalize almost anything until the data is
so
scattered into many interrelated tables that performance is compromised,
not
to mention the unguessed sorrows to be had in writing VBA procedures under
such conditions.

I believe the path forward must lie more in understanding our business
needs, what we want our application to be capable of doing, rather than
slavishly following the mantra of Normalize, Normalize, Normalize.

I have searched this site without much success, however, does anyone out
there have any experience designing an ADB for tracking insurance
certificates for compliance with subcontract requirements?

Thanks,
Pew


.

Wow!

Great explanation!

(it'll take me a while to digest it all and respond... please be patient)

Anyone else with experience/direction here, jump in!

Regards

Jeff Boyce
Microsoft Access MVP

"oldblindpew" wrote in message
...
Thanks for reply.

Insurance certificates generally appear as a fairly standardized form,
known
as the Acord form. To the user, this certificate looks like one entity,
with
all its fields belonging in one table, but I have been warned over and
over
that Access cannot handle this kind of approach. (I still don't
understand
why it is better to store a reference to a table containing a value,
rather
than just storing the value itself. It seems we are using three fields
and
two tables to do what could be done by one field in one table. And if the
same value appears in different records, so what? Computers are supposed
to
have plenty of muscle for this sort of thing).

Anyway, each Firm for which we require insurance will have zero, one or
more
Certificates, and each Certificate describes one or more Policies.
Therefore
we need tables for Firms, Certificates, and Policies, with one-to-many
relationships.

FIRMS
PK IdFirm
Firm fields...

CERTS
PK IdCert
FK IdFirm
Cert fields...

POLICIES
PK IdPolicy
FK IdCert
Policy fields...

The Cert fields and Policy fields seem to fall into two categories. For
lack of better terms I will refer to them as the Definite and the
Indefinite.

Definite Cert Fields:
FK ProducerCode (Id for the insurance agency that produces the certifcate)
Cert Date (date certificate was produced or printed)
FK CertHolderCode (Id for the firm holding the certificate, which should
be
us)

Indefinite Cert Fields:
NOCDays (the number of days Notice of Cancellation)
NOCChange (do we also receive Notice of Material Change in coverage?)
NOCStrikeEndeav (are the words "Endeavor to" stricken from the NOC?)
NOCStrikeFailure (is the Failure clause stricken from the NOC?)
Future Fields... (unknown)

The Definite Cert fields unquestionably go in the Cert table, but the
Indefinite ones seem more subject to both repeating values, and to growth
or
change in the list of fields, and therefore may belong in a separate
many-to-many table. On the other hand, the Acord form is pretty stable,
and
in the unlikely event of change, how bad would it be to just add more
fields
to the Certs table?

Another question right here. Notice the Certs table has foreign keys for
three different firms, namely the Insured Firm, the Certificate Producer,
and
the Certificate Holder. Right now, I have all firms, regardless of type,
in
a single table. How can I have multiple foreign keys back to one common
table? Won't this confuse the daylights out of Access? I notice in the
Northwind database there are separate tables for Suppliers and Customers.
I
thought I was doing right to put all my firms in one table. Was this a
mistake?

Definite Policy Fields:
FK InsurerCode (Id for the insurance firm issuing the policy)
FK PolicyTypeCode (Id for Policy Type, ie GL, Auto, Excess, Workers Comp,
etc)
Policy Number
PolicyDateEffective
PolicyDateExpires

Indefinite Policy Fields:
Type...
Basis...
Scope...
Limits...
Endorsements...

As was the case with Certs information, Indefinite Policy Fields are
subject
to repeating values and to variable and indeterminate field lists.
Different
types of policies have different lists of fields, so there has to be some
way
to prevent inappropriate associations. For example, an Additional Insured
Endorsement might apply to a General Liability policy, but not to an
Excess
Liability Policy. At this point, all our fields seem fixed, but who knows
what changes the future may bring? Again the question is whether to take
a
chance a cram all presently known fields into the Policies table, or set
up a
many-to-many relationship between the Policies table and a PolicyDetails
table.

Another, less normalized, approach would be to create separate tables for
each type of policy, and just put the appropriate fields in each table.
This
would solve the problem of inappropriate associations between Policies and
Policy Details, but it would be less normalized and less flexible. For
example if the need arose to begin requiring a new type of policy for some
or
all of our subcontractors, this would mean creating an entirely new table.

This why I asked about others with experience in this same area, because
the
"right" structure depends on what you plan on doing with the data. I
wanted
to compare notes with someone else to see how they approached the task.

Please note that capturing the information from the Acord form is barely
one
third of the task. We also have to specify our insurance requirements in
detail for each subcontractor. Most insurance requirements will be the
same
from one subcontract to the next, but any of them may differ, so each
subcontract must have its own set of required values. The
subcontractor's
insurance certificates are then checked against those required values. If
the certificate is non-compliant, we want to report precisely which
parameters are at fault.

Thanks,
Pew

"Jeff Boyce" wrote:

If you'll provide a description of your table structure and
relationships,
folks here may be able to offer more specific suggestions.

.... and even if someone has a DB for "tracking insurance certificates
for
compliance ...", you'll still need to deconstruct what they've done to
make
sure their situation and yours are reasonably matched!

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
...
Things have slowed down in my department, so I'm back to working on my
Contracts Administration database. When I left off, I was mired in
designing
tables for insurance certificates, unable to decide how far to go with
normalizing. Under-normalizing results in large spreadsheet-like
tables,
which I am reliably informed are anathema to Access. On the other
hand, I
believe it is possible to over-normalize almost anything until the data
is
so
scattered into many interrelated tables that performance is
compromised,
not
to mention the unguessed sorrows to be had in writing VBA procedures
under
such conditions.

I believe the path forward must lie more in understanding our business
needs, what we want our application to be capable of doing, rather than
slavishly following the mantra of Normalize, Normalize, Normalize.

I have searched this site without much success, however, does anyone
out
there have any experience designing an ADB for tracking insurance
certificates for compliance with subcontract requirements?

Thanks,
Pew


.

I'll start working my way through, adding in comments (see below)...

"oldblindpew" wrote in message
...
Thanks for reply.

Insurance certificates generally appear as a fairly standardized form,
known
as the Acord form. To the user, this certificate looks like one entity,
with
all its fields belonging in one table, but I have been warned over and
over
that Access cannot handle this kind of approach.

I suspect it isn't so much that Access cannot handle a "wide" table as it is
making proper use of the tool. Access is a relational database, and its
features/functions are optimized for well-normalized data. An analogy I'm
fond of using is that you absolutely can drive nails with a chainsaw, but
that doesn't mean it's the proper tool or a good idea!

(I still don't understand
why it is better to store a reference to a table containing a value,
rather
than just storing the value itself. It seems we are using three fields
and
two tables to do what could be done by one field in one table. And if the
same value appears in different records, so what? Computers are supposed
to
have plenty of muscle for this sort of thing).

Let's talk about person name ... one user enters "John Smith", another "John
J.J. Smith", another "J. Smith" and another "J.J.J. Smith" ... and they all
refer to the same human. And then John has his name legally changed to Jim.

If you "store the value itself" in a table, you have little chance of
connecting the dots and knowing those 4 (wait, 5!) people are all the same
person. If you store John once in a Person table, with a PersonID field,
then use the PersonID field in your 'other' table, you save the user data
entry time (just pick John from the combobox), reduce the risk of having 5
entries that are all the same person, and make updating John's name to Jim
quite simple ... go to the lookup table and change it once!


Anyway, each Firm for which we require insurance will have zero, one or
more
Certificates, and each Certificate describes one or more Policies.
Therefore
we need tables for Firms, Certificates, and Policies, with one-to-many
relationships.

FIRMS
PK IdFirm
Firm fields...

CERTS
PK IdCert
FK IdFirm
Cert fields...

POLICIES
PK IdPolicy
FK IdCert
Policy fields...

The Cert fields and Policy fields seem to fall into two categories. For
lack of better terms I will refer to them as the Definite and the
Indefinite.

Definite Cert Fields:
FK ProducerCode (Id for the insurance agency that produces the certifcate)
Cert Date (date certificate was produced or printed)
FK CertHolderCode (Id for the firm holding the certificate, which should
be
us)

Indefinite Cert Fields:
NOCDays (the number of days Notice of Cancellation)
NOCChange (do we also receive Notice of Material Change in coverage?)
NOCStrikeEndeav (are the words "Endeavor to" stricken from the NOC?)
NOCStrikeFailure (is the Failure clause stricken from the NOC?)
Future Fields... (unknown)

The Definite Cert fields unquestionably go in the Cert table, but the
Indefinite ones seem more subject to both repeating values, and to growth
or
change in the list of fields, and therefore may belong in a separate
many-to-many table. On the other hand, the Acord form is pretty stable,
and
in the unlikely event of change, how bad would it be to just add more
fields
to the Certs table?

Are you saying that Certificates have one-to-many "indefinite" fields? From
your above description, your Definite and Indefinite Cert fields appear to
be characterizing the Certificates (i.e., they are "attributes" of the
certificates). Why would you need to change the number of attributes? Yes,
you can always modify a table structure. Yes, spending the time before you
finalize the application cuts down on how much later changing you have to
do. In your situation, your "definite" fields for ProducerCode and
CertHolderCode seem like excellent candidates for lookup tables -- if you
add firms, you add them one place and do the lookup for these codes. But
the [CertDate] and the [NOCDays] and the others seem like
'fill-in-the-blank' type fields. It would be tough to try to generate the
list of all possible values, so why not just let folks fill in the
appropriate values (or check the checkbox or ...)?


Another question right here. Notice the Certs table has foreign keys for
three different firms, namely the Insured Firm, the Certificate Producer,
and
the Certificate Holder. Right now, I have all firms, regardless of type,
in
a single table. How can I have multiple foreign keys back to one common
table? Won't this confuse the daylights out of Access?

A human looking at that might get confused, but Access won't. If you tell
Access to use the row from [Firms] with ID = 17, it really won't matter
whether you tell it to do that for [InsuredFirm] or [Producer] or [Holder]
.... or for ALL THREE!

I notice in the
Northwind database there are separate tables for Suppliers and Customers.
I
thought I was doing right to put all my firms in one table. Was this a
mistake?

I'd keep them in one table. I suspect the reason for separate tables for
suppliers and customers is because they have too many un-shared attributes.
Yes, sure, they have names, addresses, etc. And if you wanted to get really
ana..., er, rigorous about normalizing, you COULD create a [Business] table
to hold all of them, and the common fields, then create "typeof" tables that
hold the data specific to each typeof ... but you have to look in multiple
places (or Access does) to pull it all together. Doable, but perhaps not
cost/time effective.


Definite Policy Fields:
FK InsurerCode (Id for the insurance firm issuing the policy)
FK PolicyTypeCode (Id for Policy Type, ie GL, Auto, Excess, Workers Comp,
etc)
Policy Number
PolicyDateEffective
PolicyDateExpires

Indefinite Policy Fields:
Type...
Basis...
Scope...
Limits...
Endorsements...

As was the case with Certs information, Indefinite Policy Fields are
subject
to repeating values and to variable and indeterminate field lists.
Different
types of policies have different lists of fields, so there has to be some
way
to prevent inappropriate associations. For example, an Additional Insured
Endorsement might apply to a General Liability policy, but not to an
Excess
Liability Policy. At this point, all our fields seem fixed, but who knows
what changes the future may bring? Again the question is whether to take
a
chance a cram all presently known fields into the Policies table, or set
up a
many-to-many relationship between the Policies table and a PolicyDetails
table.

I'd suggest, if you're comfortable with the above analysis on Certs, use a
similar approach here.


Another, less normalized, approach would be to create separate tables for
each type of policy, and just put the appropriate fields in each table.
This
would solve the problem of inappropriate associations between Policies and
Policy Details, but it would be less normalized and less flexible. For
example if the need arose to begin requiring a new type of policy for some
or
all of our subcontractors, this would mean creating an entirely new table.

I generally recommend against doing this. It would involve a lot more
maintenance than you've noted. You'd also have to modify queries, forms,
reports, code, etc.


This why I asked about others with experience in this same area, because
the
"right" structure depends on what you plan on doing with the data. I
wanted
to compare notes with someone else to see how they approached the task.

I disagree. I believe the "right" structure depends on your domain, and NOT
on the intended use. The "right" data, absolutely ...!

Because you have queries and forms and reports, you absolutely do NOT need
to make your table structure fit either your input or your output.


Please note that capturing the information from the Acord form is barely
one
third of the task. We also have to specify our insurance requirements in
detail for each subcontractor. Most insurance requirements will be the
same
from one subcontract to the next, but any of them may differ, so each
subcontract must have its own set of required values. The
subcontractor's
insurance certificates are then checked against those required values. If
the certificate is non-compliant, we want to report precisely which
parameters are at fault.

I'm not entirely clear from your description, but this sounds like it might
be amenable to an approach that associates one-to-many attributes (i.e.,
"insurance requirements") with each subcontractor. If that's a reasonable
statement, then look into using a main form/subform construction, or a
"paired listbox" approach (see the query wizard in action).

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.




Thanks,
Pew

I understand the basic idea of storing data once to ensure consistency and
facilitate maintenance. My parenthetical comment was written with more
extreme cases in mind. For example, you could have "Zip Code" as a field in
an address table, and let the user type the value, or you could normalize it
by having a separate table of Zip Codes. There would then be a field in the
Address table for "ZipCodeKey", and a separate table with fields for
"ZipCodeKey" and "ZipCodeDescription". Thus two tables and three fields
instead of one field in one table. Even though this is strict normalization,
I doubt Zip Codes are often handled this way, if ever.

This is analogous to my dilemma of whether to place my "Indefinite" fields
directly in the Certs table, or list them separately in a CertsDetail table.
I agree my Indefinite Cert Fields look like fixed attributes of the
certificate, which would argue for leaving them in the Certs table. But as I
have read elsewhere in this forum, some are strongly opposed to building a
table based upon a questionnaire type form (and aren't all forms ulitimately
questionnaires?) with one field per question. The reasons being 1) the
tables become too large, and 2) as soon as you get done, someone will revise
the form and add more questions and check boxes, and 3) it's just not
normalized and Access won't like it.

Originally, I wanted one big table for the entire Acord Form, treating each
piece of data as an attribute of the insurance certificate (excepting firm ID
references). When I mentioned a table with over a hundred fields, I was told
"No Way, you need to normalize". Now you're sounding like it's okay NOT to
normalize so long as your tables don't get too big.

The Policies table presents more of a problem than the Certs table, because
there are potentially more Policy attributes than Cert attributes. Also, as
mentioned previously, different types of policies have different types of
attributes. There is not a fixed set of attributes applicable to all
policies, so it wouldn't make sense to have a fixed set of fields in the
Policies table, unless you didn't mind a bunch of empty or inactive fields.
I suggested having a different table for each type of policy to solve this
problem, but you recommended against it.

I did not make this completely clear before, but insurance requirements are
determined by the subcontract language, so there would be one set of
requirements per subcontract, but the actual policies are carried by the
subcontractor, so there would be one set of policies in force for each
subcontractor. The subcontractor's coverage needs to be sufficient for worst
case, ie the most demanding set of requirements. Again, the requirements are
attributable to a document, but the certificate is attributable to a firm.

Thanks,
Pew

the many-to-many relationship between
"Jeff Boyce" wrote:

I'll start working my way through, adding in comments (see below)...

"oldblindpew" wrote in message
...
Thanks for reply.

Insurance certificates generally appear as a fairly standardized form,
known
as the Acord form. To the user, this certificate looks like one entity,
with
all its fields belonging in one table, but I have been warned over and
over
that Access cannot handle this kind of approach.

I suspect it isn't so much that Access cannot handle a "wide" table as it is
making proper use of the tool. Access is a relational database, and its
features/functions are optimized for well-normalized data. An analogy I'm
fond of using is that you absolutely can drive nails with a chainsaw, but
that doesn't mean it's the proper tool or a good idea!

(I still don't understand
why it is better to store a reference to a table containing a value,
rather
than just storing the value itself. It seems we are using three fields
and
two tables to do what could be done by one field in one table. And if the
same value appears in different records, so what? Computers are supposed
to
have plenty of muscle for this sort of thing).

Let's talk about person name ... one user enters "John Smith", another "John
J.J. Smith", another "J. Smith" and another "J.J.J. Smith" ... and they all
refer to the same human. And then John has his name legally changed to Jim.

If you "store the value itself" in a table, you have little chance of
connecting the dots and knowing those 4 (wait, 5!) people are all the same
person. If you store John once in a Person table, with a PersonID field,
then use the PersonID field in your 'other' table, you save the user data
entry time (just pick John from the combobox), reduce the risk of having 5
entries that are all the same person, and make updating John's name to Jim
quite simple ... go to the lookup table and change it once!


Anyway, each Firm for which we require insurance will have zero, one or
more
Certificates, and each Certificate describes one or more Policies.
Therefore
we need tables for Firms, Certificates, and Policies, with one-to-many
relationships.

FIRMS
PK IdFirm
Firm fields...

CERTS
PK IdCert
FK IdFirm
Cert fields...

POLICIES
PK IdPolicy
FK IdCert
Policy fields...

The Cert fields and Policy fields seem to fall into two categories. For
lack of better terms I will refer to them as the Definite and the
Indefinite.

Definite Cert Fields:
FK ProducerCode (Id for the insurance agency that produces the certifcate)
Cert Date (date certificate was produced or printed)
FK CertHolderCode (Id for the firm holding the certificate, which should
be
us)

Indefinite Cert Fields:
NOCDays (the number of days Notice of Cancellation)
NOCChange (do we also receive Notice of Material Change in coverage?)
NOCStrikeEndeav (are the words "Endeavor to" stricken from the NOC?)
NOCStrikeFailure (is the Failure clause stricken from the NOC?)
Future Fields... (unknown)

The Definite Cert fields unquestionably go in the Cert table, but the
Indefinite ones seem more subject to both repeating values, and to growth
or
change in the list of fields, and therefore may belong in a separate
many-to-many table. On the other hand, the Acord form is pretty stable,
and
in the unlikely event of change, how bad would it be to just add more
fields
to the Certs table?

Are you saying that Certificates have one-to-many "indefinite" fields? From
your above description, your Definite and Indefinite Cert fields appear to
be characterizing the Certificates (i.e., they are "attributes" of the
certificates). Why would you need to change the number of attributes? Yes,
you can always modify a table structure. Yes, spending the time before you
finalize the application cuts down on how much later changing you have to
do. In your situation, your "definite" fields for ProducerCode and
CertHolderCode seem like excellent candidates for lookup tables -- if you
add firms, you add them one place and do the lookup for these codes. But
the [CertDate] and the [NOCDays] and the others seem like
'fill-in-the-blank' type fields. It would be tough to try to generate the
list of all possible values, so why not just let folks fill in the
appropriate values (or check the checkbox or ...)?


Another question right here. Notice the Certs table has foreign keys for
three different firms, namely the Insured Firm, the Certificate Producer,
and
the Certificate Holder. Right now, I have all firms, regardless of type,
in
a single table. How can I have multiple foreign keys back to one common
table? Won't this confuse the daylights out of Access?

A human looking at that might get confused, but Access won't. If you tell
Access to use the row from [Firms] with ID = 17, it really won't matter
whether you tell it to do that for [InsuredFirm] or [Producer] or [Holder]
.... or for ALL THREE!

I notice in the
Northwind database there are separate tables for Suppliers and Customers.
I
thought I was doing right to put all my firms in one table. Was this a
mistake?

I'd keep them in one table. I suspect the reason for separate tables for
suppliers and customers is because they have too many un-shared attributes.
Yes, sure, they have names, addresses, etc. And if you wanted to get really
ana..., er, rigorous about normalizing, you COULD create a [Business] table
to hold all of them, and the common fields, then create "typeof" tables that
hold the data specific to each typeof ... but you have to look in multiple
places (or Access does) to pull it all together. Doable, but perhaps not
cost/time effective.


Definite Policy Fields:
FK InsurerCode (Id for the insurance firm issuing the policy)
FK PolicyTypeCode (Id for Policy Type, ie GL, Auto, Excess, Workers Comp,
etc)
Policy Number
PolicyDateEffective
PolicyDateExpires

Indefinite Policy Fields:
Type...
Basis...
Scope...
Limits...
Endorsements...

As was the case with Certs information, Indefinite Policy Fields are
subject
to repeating values and to variable and indeterminate field lists.
Different
types of policies have different lists of fields, so there has to be some
way
to prevent inappropriate associations. For example, an Additional Insured
Endorsement might apply to a General Liability policy, but not to an
Excess
Liability Policy. At this point, all our fields seem fixed, but who knows
what changes the future may bring? Again the question is whether to take
a
chance a cram all presently known fields into the Policies table, or set
up a
many-to-many relationship between the Policies table and a PolicyDetails
table.

I'd suggest, if you're comfortable with the above analysis on Certs, use a
similar approach here.


Another, less normalized, approach would be to create separate tables for
each type of policy, and just put the appropriate fields in each table.
This
would solve the problem of inappropriate associations between Policies and
Policy Details, but it would be less normalized and less flexible. For
example if the need arose to begin requiring a new type of policy for some
or
all of our subcontractors, this would mean creating an entirely new table.

I generally recommend against doing this. It would involve a lot more
maintenance than you've noted. You'd also have to modify queries, forms,
reports, code, etc.


This why I asked about others with experience in this same area, because
the
"right" structure depends on what you plan on doing with the data. I
wanted
to compare notes with someone else to see how they approached the task.

I disagree. I believe the "right" structure depends on your domain, and NOT
on the intended use. The "right" data, absolutely ...!

Because you have queries and forms and reports, you absolutely do NOT need
to make your table structure fit either your input or your output.


Please note that capturing the information from the Acord form is barely
one
third of the task. We also have to specify our insurance requirements in
detail for each subcontractor. Most insurance requirements will be the
same
from one subcontract to the next, but any of them may differ, so each
subcontract must have its own set of required values. The
subcontractor's
insurance certificates are then checked against those required values. If
the certificate is non-compliant, we want to report precisely which
parameters are at fault.

I'm not entirely clear from your description, but this sounds like it might
be amenable to an approach that associates one-to-many attributes (i.e.,
"insurance requirements") with each subcontractor. If that's a reasonable
statement, then look into using a main form/subform construction, or a
"paired listbox" approach (see the query wizard in action).

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.




Thanks,
Pew


.

OBP,

Can't help it... after the tblZipCode comment had to jump in...

Normalizing ZipCodes, that would not be the reason I have a tblCityStateZip.
I have it to prevent *bogus* information. Do not confuse *normalization*
with what I call *look-up* tables, which simply store values to keep the
data *pure*.

I don't know where you get the idea that building a database for a
questionnaire is opposed. There is a sample survey that is constantly being
recommended for review...

http://www.rogersaccesslibrary.com/f...osts.asp?TID=3

I have a database where the options of 9 are stored in a table. If you
choose to change and option you can do and that option once changed will go
forward storing the new option. BUT if you look up the old *form* you will
see the old option(s). That way I am not actually storing all that text,
ie...

A 1. ABC
A 2. DEF
A 3. GHI

....next month some one changes the option to...

B 1. ABC
B 2. DEF
B 4. JKL

....so my joiner table looks like the below. A & B would be the PK in the
Main table and FK in the Detail table. The numbers would be the Option PK
and the letters represent the text. So when an Options text changes it add
a field to my Options table thereby keeping my data normalized.

A - 1
A - 2
A - 3
B - 1
B - 2
B - 4

I should also note, I do not see every form as a *questionnarie*, perhaps
data entry form would be more accurate.

IMHO, if your table has over 50 fields (and some would argue that's alot)
it's not a database it's a flat file and you might as well use Excel. What
is the point of using a powerful tool if not to take advantage of the power?
If I'm never going to race why buy a race car? I might as well stick with
my Jeep.

I remember thinking about doing a database like this... a Client request. I
turned them down and suggested they by the already established software and
I even helped them select the one that best suited them. That is not to say
Access can't handle this because it can, however, it will not be a *quick
and dirty* task. It should be done modeling a flat file format. It should
be normalized with look-up tables. And while the initial set-up will be
interesting and time consuming the end result, if done properly, will be
magnificant! No one here wants to *argue* with you and if you insist upon
putting the data in a flat file then later you will have problems. Think of
building your 2,000 SF house on a 1,000 SF foundation. The house will fall,
not now and maybe not for many years but it's coming down.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"oldblindpew" wrote in message
...
I understand the basic idea of storing data once to ensure consistency and
facilitate maintenance. My parenthetical comment was written with more
extreme cases in mind. For example, you could have "Zip Code" as a field
in
an address table, and let the user type the value, or you could normalize
it
by having a separate table of Zip Codes. There would then be a field in
the
Address table for "ZipCodeKey", and a separate table with fields for
"ZipCodeKey" and "ZipCodeDescription". Thus two tables and three fields
instead of one field in one table. Even though this is strict
normalization,
I doubt Zip Codes are often handled this way, if ever.

This is analogous to my dilemma of whether to place my "Indefinite" fields
directly in the Certs table, or list them separately in a CertsDetail
table.
I agree my Indefinite Cert Fields look like fixed attributes of the
certificate, which would argue for leaving them in the Certs table. But
as I
have read elsewhere in this forum, some are strongly opposed to building a
table based upon a questionnaire type form (and aren't all forms
ulitimately
questionnaires?) with one field per question. The reasons being 1) the
tables become too large, and 2) as soon as you get done, someone will
revise
the form and add more questions and check boxes, and 3) it's just not
normalized and Access won't like it.

Originally, I wanted one big table for the entire Acord Form, treating
each
piece of data as an attribute of the insurance certificate (excepting firm
ID
references). When I mentioned a table with over a hundred fields, I was
told
"No Way, you need to normalize". Now you're sounding like it's okay NOT
to
normalize so long as your tables don't get too big.

The Policies table presents more of a problem than the Certs table,
because
there are potentially more Policy attributes than Cert attributes. Also,
as
mentioned previously, different types of policies have different types of
attributes. There is not a fixed set of attributes applicable to all
policies, so it wouldn't make sense to have a fixed set of fields in the
Policies table, unless you didn't mind a bunch of empty or inactive
fields.
I suggested having a different table for each type of policy to solve this
problem, but you recommended against it.

I did not make this completely clear before, but insurance requirements
are
determined by the subcontract language, so there would be one set of
requirements per subcontract, but the actual policies are carried by the
subcontractor, so there would be one set of policies in force for each
subcontractor. The subcontractor's coverage needs to be sufficient for
worst
case, ie the most demanding set of requirements. Again, the requirements
are
attributable to a document, but the certificate is attributable to a firm.

Thanks,
Pew

the many-to-many relationship between
"Jeff Boyce" wrote:

I'll start working my way through, adding in comments (see below)...

"oldblindpew" wrote in message
...
Thanks for reply.

Insurance certificates generally appear as a fairly standardized form,
known
as the Acord form. To the user, this certificate looks like one
entity,
with
all its fields belonging in one table, but I have been warned over and
over
that Access cannot handle this kind of approach.

I suspect it isn't so much that Access cannot handle a "wide" table as it
is
making proper use of the tool. Access is a relational database, and its
features/functions are optimized for well-normalized data. An analogy
I'm
fond of using is that you absolutely can drive nails with a chainsaw, but
that doesn't mean it's the proper tool or a good idea!

(I still don't understand
why it is better to store a reference to a table containing a value,
rather
than just storing the value itself. It seems we are using three fields
and
two tables to do what could be done by one field in one table. And if
the
same value appears in different records, so what? Computers are
supposed
to
have plenty of muscle for this sort of thing).

Let's talk about person name ... one user enters "John Smith", another
"John
J.J. Smith", another "J. Smith" and another "J.J.J. Smith" ... and they
all
refer to the same human. And then John has his name legally changed to
Jim.

If you "store the value itself" in a table, you have little chance of
connecting the dots and knowing those 4 (wait, 5!) people are all the
same
person. If you store John once in a Person table, with a PersonID field,
then use the PersonID field in your 'other' table, you save the user data
entry time (just pick John from the combobox), reduce the risk of having
5
entries that are all the same person, and make updating John's name to
Jim
quite simple ... go to the lookup table and change it once!


Anyway, each Firm for which we require insurance will have zero, one or
more
Certificates, and each Certificate describes one or more Policies.
Therefore
we need tables for Firms, Certificates, and Policies, with one-to-many
relationships.

FIRMS
PK IdFirm
Firm fields...

CERTS
PK IdCert
FK IdFirm
Cert fields...

POLICIES
PK IdPolicy
FK IdCert
Policy fields...

The Cert fields and Policy fields seem to fall into two categories.
For
lack of better terms I will refer to them as the Definite and the
Indefinite.

Definite Cert Fields:
FK ProducerCode (Id for the insurance agency that produces the
certifcate)
Cert Date (date certificate was produced or printed)
FK CertHolderCode (Id for the firm holding the certificate, which
should
be
us)

Indefinite Cert Fields:
NOCDays (the number of days Notice of Cancellation)
NOCChange (do we also receive Notice of Material Change in coverage?)
NOCStrikeEndeav (are the words "Endeavor to" stricken from the NOC?)
NOCStrikeFailure (is the Failure clause stricken from the NOC?)
Future Fields... (unknown)

The Definite Cert fields unquestionably go in the Cert table, but the
Indefinite ones seem more subject to both repeating values, and to
growth
or
change in the list of fields, and therefore may belong in a separate
many-to-many table. On the other hand, the Acord form is pretty
stable,
and
in the unlikely event of change, how bad would it be to just add more
fields
to the Certs table?

Are you saying that Certificates have one-to-many "indefinite" fields?
From
your above description, your Definite and Indefinite Cert fields appear
to
be characterizing the Certificates (i.e., they are "attributes" of the
certificates). Why would you need to change the number of attributes?
Yes,
you can always modify a table structure. Yes, spending the time before
you
finalize the application cuts down on how much later changing you have to
do. In your situation, your "definite" fields for ProducerCode and
CertHolderCode seem like excellent candidates for lookup tables -- if you
add firms, you add them one place and do the lookup for these codes. But
the [CertDate] and the [NOCDays] and the others seem like
'fill-in-the-blank' type fields. It would be tough to try to generate
the
list of all possible values, so why not just let folks fill in the
appropriate values (or check the checkbox or ...)?


Another question right here. Notice the Certs table has foreign keys
for
three different firms, namely the Insured Firm, the Certificate
Producer,
and
the Certificate Holder. Right now, I have all firms, regardless of
type,
in
a single table. How can I have multiple foreign keys back to one
common
table? Won't this confuse the daylights out of Access?

A human looking at that might get confused, but Access won't. If you
tell
Access to use the row from [Firms] with ID = 17, it really won't matter
whether you tell it to do that for [InsuredFirm] or [Producer] or
[Holder]
.... or for ALL THREE!

I notice in the
Northwind database there are separate tables for Suppliers and
Customers.
I
thought I was doing right to put all my firms in one table. Was this a
mistake?

I'd keep them in one table. I suspect the reason for separate tables for
suppliers and customers is because they have too many un-shared
attributes.
Yes, sure, they have names, addresses, etc. And if you wanted to get
really
ana..., er, rigorous about normalizing, you COULD create a [Business]
table
to hold all of them, and the common fields, then create "typeof" tables
that
hold the data specific to each typeof ... but you have to look in
multiple
places (or Access does) to pull it all together. Doable, but perhaps not
cost/time effective.


Definite Policy Fields:
FK InsurerCode (Id for the insurance firm issuing the policy)
FK PolicyTypeCode (Id for Policy Type, ie GL, Auto, Excess, Workers
Comp,
etc)
Policy Number
PolicyDateEffective
PolicyDateExpires

Indefinite Policy Fields:
Type...
Basis...
Scope...
Limits...
Endorsements...

As was the case with Certs information, Indefinite Policy Fields are
subject
to repeating values and to variable and indeterminate field lists.
Different
types of policies have different lists of fields, so there has to be
some
way
to prevent inappropriate associations. For example, an Additional
Insured
Endorsement might apply to a General Liability policy, but not to an
Excess
Liability Policy. At this point, all our fields seem fixed, but who
knows
what changes the future may bring? Again the question is whether to
take
a
chance a cram all presently known fields into the Policies table, or
set
up a
many-to-many relationship between the Policies table and a
PolicyDetails
table.

I'd suggest, if you're comfortable with the above analysis on Certs, use
a
similar approach here.


Another, less normalized, approach would be to create separate tables
for
each type of policy, and just put the appropriate fields in each table.
This
would solve the problem of inappropriate associations between Policies
and
Policy Details, but it would be less normalized and less flexible. For
example if the need arose to begin requiring a new type of policy for
some
or
all of our subcontractors, this would mean creating an entirely new
table.

I generally recommend against doing this. It would involve a lot more
maintenance than you've noted. You'd also have to modify queries, forms,
reports, code, etc.


This why I asked about others with experience in this same area,
because
the
"right" structure depends on what you plan on doing with the data. I
wanted
to compare notes with someone else to see how they approached the task.

I disagree. I believe the "right" structure depends on your domain, and
NOT
on the intended use. The "right" data, absolutely ...!

Because you have queries and forms and reports, you absolutely do NOT
need
to make your table structure fit either your input or your output.


Please note that capturing the information from the Acord form is
barely
one
third of the task. We also have to specify our insurance requirements
in
detail for each subcontractor. Most insurance requirements will be the
same
from one subcontract to the next, but any of them may differ, so each
subcontract must have its own set of required values. The
subcontractor's
insurance certificates are then checked against those required values.
If
the certificate is non-compliant, we want to report precisely which
parameters are at fault.

I'm not entirely clear from your description, but this sounds like it
might
be amenable to an approach that associates one-to-many attributes (i.e.,
"insurance requirements") with each subcontractor. If that's a
reasonable
statement, then look into using a main form/subform construction, or a
"paired listbox" approach (see the query wizard in action).

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.




Thanks,
Pew


.

Hello Pew,

This (and your name, and access comments ) sounds familiar to one we tried
to help on in a lengthy exchange a few months back. If so, or maybe even if
not, ....... Yours is a somewhat complex application in this area. If
this is one that you are having difficulty with on a long term basis, or if
your attempts to resolve it in this thread get bogged woen in complication,
in addition to the above, may I suggest the following:

You are really talking about two toolboxes here" Relational Database Design
and Architecture systesm (which basically successfully runs the whole world's
structure data systems) and Access, a related tool box. I wouldn't waste your
time implying that the practices-for-success covered by these toolboxes have
some "limitations" affecting your design. I think that that is confusing
you.

Start by refining a rigorous, complete enough, precise, unambiguous
description of your real world process that you want to database, with
sufficient detail in the areas relevant to databasing. To keep your self on
track, do this without using any Access or DB terminology. You might
use your 1/14/2010 11:38 AM PST post as the starting point, as it was an
excellent start.

That alone might guide you to the answers, but if not, you could include it
in your post. Sounds slow and methodical, but for your situation it might
provide a foundation for decisively moving forwards instead of spinning your
wheels.

I'm sorry, Gina. It was not my intention to irritate anyone, but to try to
better understand the practical limits of normalization. The zipcode
illustration is one I have seen used before for this purpose.

I never said or meant that anyone here opposes building a database for a
questionnaire. I said there was opposition to building a spreadsheet-like
table based on a questionnaire, with one question per field.

I'm sure you make some good points about your "options of 9" table, but I
couldn't follow any of it.

I think it is accurate to say that all forms are in the broadest sense
questionnaires, because they present a list of stated or implied questions to
which the respondent replies with answers (data) in the form of text,
numbers, checkmarks, etc.

We did have a commercial certificate tracking application a while back,
which was actually based on Access, but it didn't work very well for us and
was not being aggressively updated and enhanced, so we let it go. This is
not to say some other better application doesn't exist out there, but our
management does not seem inclined to pursue that option at this time.
Personally, I'm continually amazed at how quirky commercial business software
can be, and am confident that I have designed and can design better
interfaces, despite not being a programmer by trade. Learning Access and VBA
is just hard, that's all, like learning a foreign language. If it was easy,
one could get the answers from books instead of having to appeal to the
kindness of volunteers such as yourself.

I am definitely not into "quick and dirty". When have I ever said as much?
I feel sorry for those who say they just want to quickly set up a simple
database application. They should be told up front that there is no such
thing.

I don't understand your statement that my task should be "done modeling a
flat file format". Was this a typo? Later you state that if I insist on
putting the data in a flat file I will have problems.

BTW, the whole point of this thread is that I accept the notion that flat
tables are not viable. The point under discussion is HOW FAR TO GO in
breaking the data down into various tables, ie normalization. Genius in
design lies in knowing where to compromise.

Again, I'm sorry to be such a bother and hard to teach. I appreciate your
patience and help.

--Pew

"Gina Whipp" wrote:

OBP,

Can't help it... after the tblZipCode comment had to jump in...

Normalizing ZipCodes, that would not be the reason I have a tblCityStateZip.
I have it to prevent *bogus* information. Do not confuse *normalization*
with what I call *look-up* tables, which simply store values to keep the
data *pure*.

I don't know where you get the idea that building a database for a
questionnaire is opposed. There is a sample survey that is constantly being
recommended for review...

http://www.rogersaccesslibrary.com/f...osts.asp?TID=3

I have a database where the options of 9 are stored in a table. If you
choose to change and option you can do and that option once changed will go
forward storing the new option. BUT if you look up the old *form* you will
see the old option(s). That way I am not actually storing all that text,
ie...

A 1. ABC
A 2. DEF
A 3. GHI

....next month some one changes the option to...

B 1. ABC
B 2. DEF
B 4. JKL

....so my joiner table looks like the below. A & B would be the PK in the
Main table and FK in the Detail table. The numbers would be the Option PK
and the letters represent the text. So when an Options text changes it add
a field to my Options table thereby keeping my data normalized.

A - 1
A - 2
A - 3
B - 1
B - 2
B - 4

I should also note, I do not see every form as a *questionnarie*, perhaps
data entry form would be more accurate.

IMHO, if your table has over 50 fields (and some would argue that's alot)
it's not a database it's a flat file and you might as well use Excel. What
is the point of using a powerful tool if not to take advantage of the power?
If I'm never going to race why buy a race car? I might as well stick with
my Jeep.

I remember thinking about doing a database like this... a Client request. I
turned them down and suggested they by the already established software and
I even helped them select the one that best suited them. That is not to say
Access can't handle this because it can, however, it will not be a *quick
and dirty* task. It should be done modeling a flat file format. It should
be normalized with look-up tables. And while the initial set-up will be
interesting and time consuming the end result, if done properly, will be
magnificant! No one here wants to *argue* with you and if you insist upon
putting the data in a flat file then later you will have problems. Think of
building your 2,000 SF house on a 1,000 SF foundation. The house will fall,
not now and maybe not for many years but it's coming down.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"oldblindpew" wrote in message
...
I understand the basic idea of storing data once to ensure consistency and
facilitate maintenance. My parenthetical comment was written with more
extreme cases in mind. For example, you could have "Zip Code" as a field
in
an address table, and let the user type the value, or you could normalize
it
by having a separate table of Zip Codes. There would then be a field in
the
Address table for "ZipCodeKey", and a separate table with fields for
"ZipCodeKey" and "ZipCodeDescription". Thus two tables and three fields
instead of one field in one table. Even though this is strict
normalization,
I doubt Zip Codes are often handled this way, if ever.

This is analogous to my dilemma of whether to place my "Indefinite" fields
directly in the Certs table, or list them separately in a CertsDetail
table.
I agree my Indefinite Cert Fields look like fixed attributes of the
certificate, which would argue for leaving them in the Certs table. But
as I
have read elsewhere in this forum, some are strongly opposed to building a
table based upon a questionnaire type form (and aren't all forms
ulitimately
questionnaires?) with one field per question. The reasons being 1) the
tables become too large, and 2) as soon as you get done, someone will
revise
the form and add more questions and check boxes, and 3) it's just not
normalized and Access won't like it.

Originally, I wanted one big table for the entire Acord Form, treating
each
piece of data as an attribute of the insurance certificate (excepting firm
ID
references). When I mentioned a table with over a hundred fields, I was
told
"No Way, you need to normalize". Now you're sounding like it's okay NOT
to
normalize so long as your tables don't get too big.

The Policies table presents more of a problem than the Certs table,
because
there are potentially more Policy attributes than Cert attributes. Also,
as
mentioned previously, different types of policies have different types of
attributes. There is not a fixed set of attributes applicable to all
policies, so it wouldn't make sense to have a fixed set of fields in the
Policies table, unless you didn't mind a bunch of empty or inactive
fields.
I suggested having a different table for each type of policy to solve this
problem, but you recommended against it.

I did not make this completely clear before, but insurance requirements
are
determined by the subcontract language, so there would be one set of
requirements per subcontract, but the actual policies are carried by the
subcontractor, so there would be one set of policies in force for each
subcontractor. The subcontractor's coverage needs to be sufficient for
worst
case, ie the most demanding set of requirements. Again, the requirements
are
attributable to a document, but the certificate is attributable to a firm.

Thanks,
Pew

the many-to-many relationship between
"Jeff Boyce" wrote:

I'll start working my way through, adding in comments (see below)...

"oldblindpew" wrote in message
...
Thanks for reply.

Insurance certificates generally appear as a fairly standardized form,
known
as the Acord form. To the user, this certificate looks like one
entity,
with
all its fields belonging in one table, but I have been warned over and
over
that Access cannot handle this kind of approach.

I suspect it isn't so much that Access cannot handle a "wide" table as it
is
making proper use of the tool. Access is a relational database, and its
features/functions are optimized for well-normalized data. An analogy
I'm
fond of using is that you absolutely can drive nails with a chainsaw, but
that doesn't mean it's the proper tool or a good idea!

(I still don't understand
why it is better to store a reference to a table containing a value,
rather
than just storing the value itself. It seems we are using three fields
and
two tables to do what could be done by one field in one table. And if
the
same value appears in different records, so what? Computers are
supposed
to
have plenty of muscle for this sort of thing).

Let's talk about person name ... one user enters "John Smith", another
"John
J.J. Smith", another "J. Smith" and another "J.J.J. Smith" ... and they
all
refer to the same human. And then John has his name legally changed to
Jim.

If you "store the value itself" in a table, you have little chance of
connecting the dots and knowing those 4 (wait, 5!) people are all the
same
person. If you store John once in a Person table, with a PersonID field,
then use the PersonID field in your 'other' table, you save the user data
entry time (just pick John from the combobox), reduce the risk of having
5
entries that are all the same person, and make updating John's name to
Jim
quite simple ... go to the lookup table and change it once!


Anyway, each Firm for which we require insurance will have zero, one or
more
Certificates, and each Certificate describes one or more Policies.
Therefore
we need tables for Firms, Certificates, and Policies, with one-to-many
relationships.

FIRMS
PK IdFirm
Firm fields...

CERTS
PK IdCert
FK IdFirm
Cert fields...

POLICIES
PK IdPolicy
FK IdCert
Policy fields...

The Cert fields and Policy fields seem to fall into two categories.
For
lack of better terms I will refer to them as the Definite and the
Indefinite.

Definite Cert Fields:
FK ProducerCode (Id for the insurance agency that produces the
certifcate)
Cert Date (date certificate was produced or printed)
FK CertHolderCode (Id for the firm holding the certificate, which
should
be
us)

Indefinite Cert Fields:
NOCDays (the number of days Notice of Cancellation)
NOCChange (do we also receive Notice of Material Change in coverage?)
NOCStrikeEndeav (are the words "Endeavor to" stricken from the NOC?)
NOCStrikeFailure (is the Failure clause stricken from the NOC?)
Future Fields... (unknown)

The Definite Cert fields unquestionably go in the Cert table, but the
Indefinite ones seem more subject to both repeating values, and to
growth
or
change in the list of fields, and therefore may belong in a separate
many-to-many table. On the other hand, the Acord form is pretty
stable,
and
in the unlikely event of change, how bad would it be to just add more
fields
to the Certs table?

Are you saying that Certificates have one-to-many "indefinite" fields?
From
your above description, your Definite and Indefinite Cert fields appear
to
be characterizing the Certificates (i.e., they are "attributes" of the
certificates). Why would you need to change the number of attributes?
Yes,
you can always modify a table structure. Yes, spending the time before
you
finalize the application cuts down on how much later changing you have to
do. In your situation, your "definite" fields for ProducerCode and
CertHolderCode seem like excellent candidates for lookup tables -- if you
add firms, you add them one place and do the lookup for these codes. But
the [CertDate] and the [NOCDays] and the others seem like
'fill-in-the-blank' type fields. It would be tough to try to generate
the
list of all possible values, so why not just let folks fill in the
appropriate values (or check the checkbox or ...)?


Another question right here. Notice the Certs table has foreign keys
for
three different firms, namely the Insured Firm, the Certificate
Producer,
and
the Certificate Holder. Right now, I have all firms, regardless of
type,
in
a single table. How can I have multiple foreign keys back to one
common
table? Won't this confuse the daylights out of Access?

A human looking at that might get confused, but Access won't. If you
tell
Access to use the row from [Firms] with ID = 17, it really won't matter
whether you tell it to do that for [InsuredFirm] or [Producer] or
[Holder]
.... or for ALL THREE!

I notice in the
Northwind database there are separate tables for Suppliers and
Customers.
I
thought I was doing right to put all my firms in one table. Was this a
mistake?

I'd keep them in one table. I suspect the reason for separate tables for
suppliers and customers is because they have too many un-shared
attributes.

See below ...

"oldblindpew" wrote in message
...
I understand the basic idea of storing data once to ensure consistency and
facilitate maintenance. My parenthetical comment was written with more
extreme cases in mind. For example, you could have "Zip Code" as a field
in
an address table, and let the user type the value, or you could normalize
it
by having a separate table of Zip Codes. There would then be a field in
the
Address table for "ZipCodeKey", and a separate table with fields for
"ZipCodeKey" and "ZipCodeDescription". Thus two tables and three fields
instead of one field in one table. Even though this is strict
normalization,
I doubt Zip Codes are often handled this way, if ever.

I use PostalCodes, not a postal code lookup table.


This is analogous to my dilemma of whether to place my "Indefinite" fields
directly in the Certs table, or list them separately in a CertsDetail
table.
I agree my Indefinite Cert Fields look like fixed attributes of the
certificate, which would argue for leaving them in the Certs table. But
as I
have read elsewhere in this forum, some are strongly opposed to building a
table based upon a questionnaire type form (and aren't all forms
ulitimately
questionnaires?) with one field per question. The reasons being 1) the
tables become too large, and 2) as soon as you get done, someone will
revise
the form and add more questions and check boxes, and 3) it's just not
normalized and Access won't like it.

Access is optimized for normalized data. If you feed it 'sheet data, you
and Access both have to work harder to come up with workarounds, plus you
have the potential for a lot of maintenance work if the questions/columns
have to be changed.



Originally, I wanted one big table for the entire Acord Form, treating
each
piece of data as an attribute of the insurance certificate (excepting firm
ID
references). When I mentioned a table with over a hundred fields, I was
told
"No Way, you need to normalize". Now you're sounding like it's okay NOT
to
normalize so long as your tables don't get too big.

If I gave that impression, I was mis-speaking. I value normalization in
Access tables because it makes it easier for Access to work with the data.
If you have a table with over 30 fields, chances are good that it isn't
normalized.


The Policies table presents more of a problem than the Certs table,
because
there are potentially more Policy attributes than Cert attributes. Also,
as
mentioned previously, different types of policies have different types of
attributes. There is not a fixed set of attributes applicable to all
policies, so it wouldn't make sense to have a fixed set of fields in the
Policies table, unless you didn't mind a bunch of empty or inactive
fields.
I suggested having a different table for each type of policy to solve this
problem, but you recommended against it.

Remember, you are there, and we are not. You know your domain much better
than we do. If you have different Policy types, each with their own unique
attributes, then by all means model your structure on that reality!


I did not make this completely clear before, but insurance requirements
are
determined by the subcontract language, so there would be one set of
requirements per subcontract, but the actual policies are carried by the
subcontractor, so there would be one set of policies in force for each
subcontractor. The subcontractor's coverage needs to be sufficient for
worst
case, ie the most demanding set of requirements. Again, the requirements
are
attributable to a document, but the certificate is attributable to a firm.

I'm having trouble visualizing the relationships among the entities. Care
to clarify?

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.