Insurance Certificates Database

[oldblindpew]

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

 

[Jeff Boyce]

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]

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]

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

 

[Jeff Boyce]

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

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.

 

[oldblindpew]

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

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

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

.

 

[Gina Whipp]

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/forum/forum_posts.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

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

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

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

.

 

[Fred]

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.

 

[oldblindpew]

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

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/forum/forum_posts.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

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

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

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.

 

[Jeff Boyce]

See below ...

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.

 

[Gina Whipp]

OBP,

Not irrated and sorry if my posting came off as such... it was more a less,
I see warm clear blue water and I'm jumping in. I want to give you a better
reply, with more depth, but work is calling so I will have to wait till a
wee bit later! I also will try to explain myself clearer for the Options of
9 because I do believe it is important to what you want to accomplish...

....till my next post!
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

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

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/forum/forum_posts.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

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
:

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

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.

 

[Underwriting]

Let me preface by saying this thread is way, way, over my head.

But I want to let you know how we issue our certificates of insurance. We
are a special events firm insuring concerts, fairs, sporting events, etc.

We have one table called Clients which contains both our insureds and the
companies we may be issuing certificates to (certificate holders). Each
record has a class field. So our insured would be classified as Customer. The
certificate holder would be classified as Venue, etc.

We then have a child table called Policies. This contains all of the policy
info for a particular insured; carrier, policy number, limits, etc. This
table is updated manually as policies are renewed.

Finally we have a table called Certificates. When our insured requests a
certificate, we create a record in this table which pulls in insured
name/address info from Clients, venue info and additional insured wording
from Clients, and policy info from the Policies table. We then complete it
with any special wording (more additional insureds, any special conditions,
etc.) the certificate holder may require.

 

[oldblindpew]

Thanks for chiming in.

You said you have "a child table called Policies". A table is a place for
records that share a common set of fields. How do you handle the fact that
different types of policies have different sets of descriptive fields? Do
you 1) perhaps have separate child tables for GL, Auto, Excess, and Worker's
Comp Policies? Or, 2) perhaps have one table for common policy information,
and another table or tables for the messier bits of information? Or 3) maybe
your Policies table is extremely wide and has fields for almost every bit of
information on the Acord form?

This last case is what I started out doing, and in fact had actually built
the tables and forms for both the Insurance Required and Insurance Provided.
I was exploring how to go about checking provided coverage for compliance
with required coverage when I learned that my approach was not recommended.

Is your application something homemade, or is it a commercial product or a
trade association project?

Thanks,
Pew

 

[Underwriting]

Pew,

We have one Policies record for each insured. It contains fields for the
policies we provide (GL, Work Comp, Property, auto, etc.); carrier, policy
number, limits, policy dates, etc.

The messier bits of information, such as additional insured wording, special
certificate wording, etc. is entered in the actual Certificate table. This
info is either pulled automatically from the Certificate Holder's client
record, or it can be entered manually for unusual circumstances.

Our application was created in-house. We have been using/improving it since
1998.

We keep 'insurance required' data (specific additional insured wording,
minimum coverage requirements, etc.) with the Certificate Holder record. The
'insurance provided' info is what we input in the Policies table for that
particular insured.

 

[oldblindpew]

It sounds like we were taking a similar approach. My Insurance Certificates
table had 86 fields, one field for each parameter for each policy. If a new
type of policy were to come on the scene, widespread changes would be
required throughout the application. The cognoscenti refer to this as the
spreadsheet approach or "committing Excel" (as committing a crime or
suicide). I get the impression that the spreadsheet approach is both harder
to operate and harder to revise, although I am not sure which of the two
criteria is most dreaded. My intuition is that it takes more effort to build
flexibility into any product, and there are usually performance tradeoffs
too, as complexity increases. On the other hand, the folks on this forum
have been at this a long time, and I'm sure their views are very well founded.

Thanks,
oldblindpew