A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

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

Insurance Certificates Database



 
 
Thread Tools Display Modes
  #11  
Old January 15th, 2010, 08:32 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Insurance Certificates Database

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

"oldblindpew" wrote in message
...
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.



  #12  
Old January 19th, 2010, 10:04 PM posted to microsoft.public.access.tablesdbdesign
Underwriting
external usenet poster
 
Posts: 3
Default Insurance Certificates Database

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.

  #13  
Old January 20th, 2010, 12:10 AM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default Insurance Certificates Database

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" wrote:

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.

  #14  
Old January 21st, 2010, 08:53 PM posted to microsoft.public.access.tablesdbdesign
Underwriting
external usenet poster
 
Posts: 3
Default Insurance Certificates Database

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" wrote:

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" wrote:

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.

  #15  
Old January 22nd, 2010, 12:46 AM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default Insurance Certificates Database

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


"Underwriting" wrote:

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" wrote:

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" wrote:

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.

 




Thread Tools
Display Modes

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

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


All times are GMT +1. The time now is 03:01 PM.


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