Avoiding One to One Tables

[oldblindpew]

I cannot ignore warnings given elsewhere in this forum against tables with
many fields and the temptation to break them apart into a series of
one-to-one tables. I'm sure you are all tired of dealing with this question,
as am I, but I still need to find an answer.

I have a table of Agreements, with a record for each agreement. Some of
these agreements are subject to a long list of Insurance Requirements. I
have been warned not to make a separate one-to-one table of requirements with
one field for each requirement, but instead, to normalize the data by having
requirements listed once in a Requirements table, and then make a
many-to-many join between Agreements and Requirements. The Value of the
requirement would be in a separate field in the join table. So the join
table would tell us which Agreement, which Requirement, and the Value for
that Requirement.

Here is the question: How do I format the Value field in the join table,
seeing that there are different kinds of requirements with different kinds of
values? I hate to get into tedious details, but here is a sampling of
Requirements, with their Values in parentheses:

Is GL Insurance Required? (yes/no).
Who is the Producer? (foreign key to Firms table).
Who is the Insurer? (foreign key to Firms table).
Policy Type? (CGL, Claims Made, Occur).
How is GL Aggregate Applied? (per Policy, per Project, per Location).
Policy Number? (text).
Date Effective? (date).
Date Expired? (date).
Limits Each Occur: (dollars).
Limits Rent Property Damage: (dollars).
Limits Medical: (dollars).
Limits Personal & Advertising: (dollars).
Limits General Aggregate: (dollars).
Limits Products/Completed Operations: (dollars).
Limits Explosion/Collapse/Underground: (dollars).
Additional Insured Endorsement? (yes/no)
Waiver of Subrogation? (yes/no).
Primary Coverage? (yes/no).
etc, etc.....

Thanks. (BTW, I'd rather not read offers of service for a fee, or
complaints about people who make such offers).
--Thanks, O.B.P.

 

[Fred]

Hello O.B.P

Here my take on it.....

You have to start by thinking /deciding what the fundamental ENTITIES are
that you are going to database. You'll need to make the distinction
between such entities, and information which is merely an attribute of an
entity. And you should have a unique name for each type of entity.

I believe that you have the above process still all scrambled
up......without fixing that, you have no foundation to build your application
on. Including that you you are using the word "requirement" to refer to
multiple fundamentally different things. And I don't mean just that there
are different types of requirements (which would not be a problem) I mean
these fundamentally different things:

- Something which is not an entity, but merely an attribute about an
agreement (e.g. expiration date)

- Something which is on a "pick list" of types of insurance (e.g.
Explosion/Collapse/Underground)

- Something which records the (existence of and key information about) a
need for coverage. E.G. recording that a particular agreement has a need for
$1M of Explosion/Collapse/Underground insurance.

In my opinion, until you complete / unscramble the above process, your
question is not only unanswerable but not really communicated.

- - - - -

But, if I were to take a pre-mature guess, if expanding your 18 item list
to a few dozen items is sufficient to cover 99% of all requirements, you can
expand your 99.9% of your "requirements" (sic.) the one big flat table,
with a field for each of those "requirements" would be a good choice, and put
the other 1/10th % into a free form "special requirements" field.


Hope that helps a little.

Fred

 

[Sylvain Lafontaine]

« -- have been warned not to make a separate one-to-one table of
requirements with one field for each requirement, but instead, to normalize
the data by having requirements listed once in a Requirements table, and
then make a many-to-many join between Agreements and Requirements. »

On what basis? Sometimes it might be helpful; for example you have a sparse
table: each record have only an handful of requirements (1%, 10%, 20%?)
amidst a full collection of possibilities or these possibilities are
dynamically created (for example, for storing sondages where the questions
are created by the users). On other occasions, you have absolutely no
advantage of doing this and you will only make your database more complex,
longer to develop and (much) slower at the end. Also, if you think about
trying to save some space, don't forget that if you split your requirements
into a table where each requirement will be stored in its own record, this
will require a pointer (the foreign key) for each value and at the end,
instead of saving space, you might end up with a much bigger database
instead.

The decision to split the requirements into one - or more - table can only
be made after a careful examination of the different lists of requirements
and how it will be easier to store and retrieve them; without forgetting the
size and the performance of the database and the time required for the
programmers for not only creating it but also for maintening it.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[oldblindpew]

Fred,
Thanks for your reply. I wish I understood what you are talking about,
because I'm sure it's very important.

What do you mean by "entity"? In my mind, an entity is exemplified by a
record in a table. A Firm is an entity; we have a table of Firms. An
Agreement is an entity; we have a table of Agreements. A Certificate of
Insurance is an entity. It is a piece of paper describing the insurance
coverage which a Firm has in force, to insure themselves against loss. The
Certificate of Insurance is what we are trying to track here via our Access
database application. The sample fields listed in my original post came
directly from a certificate of insurance. We want to know 1) has a
Certificate has been furnished, 2) is it valid (i.e. meets or exceeds all the
requirements) and 3) if invalid, at what points is it non-compliant.

This is really all the information there is. I don't know how to describe
what we're doing any more clearly; it isn't rocket science.

Could you possibly restate the final paragraph of your previous post? I
couldn't make grammatical sense of it.

Thanks,
O.B.P.

 

[Gina Whipp]

OBP,

Does the below jive with what you are doing or trying to do?

tblFirm
fFirmID (PK)
fFirmName
etc...

tblAgreements
aAgreementID (PK)

tblFirmAgreements
faFirmID (FK)
faAgreementID (FK)

tblRequirements
rRequirementID (PK)
etc...

tblAgreementRequirements
arAgreementRequirementsID (PK)
arAgreementID (FK)
arRequirementID (FK)

tblCertificatesOfAgreement
coaCertificatesOfAgreementID (PK)
coaFirmID (FK)


--
Gina Whipp

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

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

 

[Fred]

Hello O.B.P.

In the following you were right on target:

"A Firm is an entity; we have a table of Firms. An
Agreement is an entity; we have a table of Agreements. A Certificate of
Insurance is an entity. It is a piece of paper describing the insurance
coverage which a Firm has in force, to insure themselves against loss. The
Certificate of Insurance is what we are trying to track here via our Access
database application.Up though the following you are"

So you have the above entities and the following nouns for them:

Agreement
Certificate of Insurance
Firm

After that it gets messy. For example, you never spoke about a Certificate
of Insurance being a databased entity.

And before, you said that you list of items was "requirements", and now you
are saying something quite the opposite...that they are attributes of
certificates of insurance. These are the types of things that you have to
clarify to yourself if not to us.

I messed up badly (duplicated text) when I wrote that last paragraph. Not
sure it's still applicable, but here's what I intended to write:

"But, if I were to take a pre-mature guess, if expanding your 18 item list
to a few dozen items is sufficient to cover 99% of all "requirements", my
guess would be that your requirets could be just fields in one big flat
Agreement table. "

 

[oldblindpew]

Hello, Gina.

There is not a many-to-many join between Firms and Agreements. The
Agreement is a purchase agreement, and the Firm is the supplier. We can have
many orders (purchase agreements) with one firm, but each order will have
only one supplier fulfilling it.

This is what I am thinking:

tblFirms
fFirmID (PK)
fFirmName
etc...

tblAgreements
aAgreementID (PK)
etc...

tblInsRequirements
iInsRequirementID (PK)
etc...

tblAgreementsRequirements
arAgreementRequirementID (PK)
arAgreementID (FK)
arInsRequirementID (FK)
arInsRequirementValue
arCertificateValue

In this model, the term "Requirement" may be too restrictive. The table of
Insurance Requirements is actually a table of Insurance Parameters. One of
those parameters would be the Limit of Liability per Each Occurrence for the
General Liability Policy. The join table would tell us that for a particular
agreement, this particular parameter has a required value of, say, $1m, and
the value supplied on the Certificate of Insurance is, say, $2m, so this
would be acceptable.

The problem is that my Insurance Parameters do not all have the same kind of
values. Some are Yes/No, Text, Date, Currency, or what have you. I don't
see how one field in the join table can hold all those different types of
values.

Thanks for your help.
--Pew

 

[oldblindpew]

Thanks, Sylvain. Your reply seems very rational to me. I have heard it said
that in Access, fields are expensive, but records are cheap. But it seems
that at some point, as you indicate, normalizing becomes
counterproductive.--Pew

 

[Gina Whipp]

OBP,

Answers in line...

--
Gina Whipp

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

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

Hello, Gina.

There is not a many-to-many join between Firms and Agreements. The
Agreement is a purchase agreement, and the Firm is the supplier. We can
have
many orders (purchase agreements) with one firm, but each order will have
only one supplier fulfilling it.

***I understand this which is why you need a join table OR add FirmID to
tblAgreements?

This is what I am thinking:

tblFirms
fFirmID (PK)
fFirmName
etc...

tblAgreements
aAgreementID (PK)
etc...

tblInsuranceRequirements
irInsuranceRequirementID (PK) irInsuranceRequirementValue
etc...

tblAgreementsRequirements ***This is a join table, not meant to store
anything but the two tables it is joining.
arAgreementRequirementID (PK)
arAgreementID (FK)
arInsuranceRequirementID (FK)
arInsuranceRequirementValue - ***REMOVE from here to
tblInsuranceRequirements
arCertificateValue - ***If this is part of Insurance Requirements move to
that table.

In this model, the term "Requirement" may be too restrictive. The table
of
Insurance Requirements is actually a table of Insurance Parameters. One
of
those parameters would be the Limit of Liability per Each Occurrence for
the
General Liability Policy. The join table would tell us that for a
particular
agreement, this particular parameter has a required value of, say, $1m,
and
the value supplied on the Certificate of Insurance is, say, $2m, so this
would be acceptable.

The problem is that my Insurance Parameters do not all have the same kind
of
values. Some are Yes/No, Text, Date, Currency, or what have you. I don't
see how one field in the join table can hold all those different types of
values.

That would be different fields that store different values. Each parameter
would be in a seperate field. Are there not a 'set' of parameters?

 

[oldblindpew]

Fred,
Thanks v.much for your reply and clarification.

Here is my original-original post (from another thread), where this all
started...

"This may prove to be an open-ended question, but here goes. I'm
anticipating having three tables with one-to-one relationships back to
another table. All of this information could therefore be placed in one
massive table, but somehow I feel it would be more manageable if separated.
The question is, should I key all three tables back to the main table, or
should I key the main to the first, the first to the second, and the second
to the third? Does it matter?

"More info: The main table is Agreements. The sub-tables are Requirements,
Certificates, and Validations. Each Agreement imposes a set of Requirements.
A Certificate lists what is being offered to meet those Requirements, and a
Validation checks what is being offered against what is being required, field
by field.

"So you could say Agreements result in Requirements, which result in
Certificates, which result in Validations. Or, you could say for each
Agreement there is a Requirement, a Certificate, and a Validation. There is
truth in both statements as all of these are interrelated. I'm just afraid
this will be another one of those cases where...it doesn't seem to matter,
but one day long afterwards you find out that despite all your caution you
made the wrong choice."

Fred, as you can see above, Insurance Requirements are attributes of an
Agreement, but I was planning to put them in a separate table because 1)
there are many fields, and 2) not all Agreements have Insurance Requirements.
It was at this point I was told the data was not normalized, and I was
headed for disaster.

I don't anticipate this database becoming very large, because at some point
we can begin deleting or archiving old information. Due to the terrible
difficulty I've found in understanding VBA, and finding answers to supposedly
simple questions, I am much more concerned about simplifying the programming
(if possible), than about making life easier for Access.

--Pew

 

[Fred]

First to clarify, this is all about structure, not VBA. And, structure
is really about making something that works well for YOU, not making it
easier for Access.

If you are interested in my thread towards really solving your problem, this
will get tedious, but I'm game if you're game. If so......


Could you describe, as thoroughly as you can, and example that involves an
Agreement, Requirement, Certificate, and a validation. When onle involves
more than on of the other, mention that, but go into detail on only one of
the other. For example, if an agreement has/creates many requirements,
mention that, but just go into detail on one requirement.


Fred

 

[oldblindpew]

Fred, I appreciate your willingness to look into this. I will try to
describe what I am doing.

We construct large civil projects, mainly highways. We issue numerous
contractual agreements (subcontracts, purchase orders, trucking agreements,
and addenda thereto). Tracking these documents through the process of
drafting, reviewing, and signing is the main purpose of our application, and
it has been working well for us. I am now trying to add the tracking of
Insurance Certificates.

There is one record in the Agreements Table for each agreement. Each record
contains foreign keys identifying the Firm and the Project, as well as data
fields such as agreement type, description, total dollar amount, etc.

Some of our agreements, (e.g. subcontracts), require the subcontracting firm
to carry insurance. We have a fairly well-standardized set of requirements,
or parameters, or specifications, or details, describing the insurance
coverage needed. Earlier in this thread I listed some examples of these
details.

Normally, our insurance requirements are similar for all our subcontracts,
but since it is possible for any particular requirement on any particular
subcontract to be waived or modified, it is vital that each subcontract have
its own set of insurance requirement values. Thus my initial solution was to
have a table of requirements with one record for each subcontract agreement
(one-to-one relationship), and a field for each parameter.

Subcontractors are required to furnish a Certificate of Insurance as proof
of coverage. If the devil is in the details, then getting a certificate that
complies with subcontract requirements is our hell. We want a relatively
unskilled person to be able to enter information from the Certificate into
our database so that we can programmatically check it against the previously
defined set of requirements. There will be only one Certificate per
subcontract. Thus my solution was to have a table of certificates with one
record for each Certificate (i.e. one record for each subcontract agreement)
and one field for each value we desire to capture from the certificate.

Once we have a Requirements record and a Certificate record stored in their
respective tables, we would need some sort of VBA procedure to pull up the
two records and compare appropriate fields to see whether the certificate is
valid. My thought was to have a third table to hold the results of this
validation check. There would be one record for each Certificate, and fields
as needed for each insurance parameter being checked.

All this looked pretty ugly to me, but seemed necessary to get the job done.
I did not think Access would be challenged by having these three tables with
long records and lots of fields. After all, computers are supposed to be
good at handling much larger stuff than this, I thought. My biggest worry
was and is writing the validation procedure. My experience so far with
Access and VBA is that it's easier done than said. Once you know how, it's
easy, but asking for directions and getting straightforward answers is tough.

Thanks,
Pew

 

[Fred]

Hello Pew,

To start with, long story short, I'm going to go only by your 9/11/09 post
except possibly to glance back at the others for a few factoids where your
new posts brings up a quesiton or has missing info.

First, I'm going to ask a couple of questions and discuss them and then
temporarily presume an answer.

Question#1 Can you assume that the types coverage requirements that need
this type of databasing come from a standardized list (lets call it
ListOfCoverageTypes) That all other variable attributes (e.g. required dates
of coverage) are implied

Question #2 Can you assume that each coverage requirement that needs this
type of databasing can be defined by specifying one item from the
ListOfCoverageTypes plus ONE number (limits)?

Questions #3 Can you assume that the data entered can be split up the
certificate into individual instances of coverage, specifically the same as
Question #1 & #2? I.E. each entered as a type from the ListOfInsuranceTypes,
plus one number (limits).

I'll temporarily assume that the answers to all three are "yes". If not
true for #1 or #2, then it would be "back to the drawing board" regarding
structure. If not true for #3, then you will need a team of lawyers and
insurance experts to do the split up and definition described in Q#3

If so, then here's my idea on a way to do it. Change/shorten the names as
desired.


Table: ListOfInsuranceTypes PK = InsuranceType_ID

Table of Agreements , PK = Agreement_ID. (a subcontract is an agreement)

Table: InsuranceRequirements:

FK = Agreement_ID. Also include an integer numerical field "RequiredLimit"
default value = 0
PK = InsuranceRequirement_ID
FK = RequiredInsuranceType_ID

Table: InsuranceCertificates.
PK: Certificate_ID
FK: Agreement_ID

Next we'll be talking about InstancesOfCoverageFurnished, as fufilled by the
Insurance certificates. Here we hit a fork in the road.

If, for every entry, they can immediately pair it with an insurance
requirement, then just add the following fields to your InsuranceRequirements
table:

- FK FurnishedInsuranceType_ID
- LimitProvided (integer)
- FK Certificate_ID

If you need to be able to record coverage furunished that has not been
paired with a requirement, then you can go the intellectually complex but
structurally simple route of still making the above additions to your
InsuranceRequirements table, but rename it
"InsuranceRequirementsAndOrInsuranceItemsProvided" (of course shorten that)

The above is normalized not because it fulfills a normalizaiton religion
commandment, and not because it makes it easire for Access or your computer.
It's normalized because I think that it will make it easirer for you.
Including, I think, that you will be able to use a simple query (not that
anticipated complex VBA) to test that a certificate fufills all insurance
requirements.

I just wrote the above, I didn't test it (for errors , but I think that the
concept is good for your requirements, provided that the temporary answer to
the above 3 questions were right.

 

[oldblindpew]

Gina,
I'm sorry, I just now realized you had embedded your replies down in the
past entries.

***I understand this which is why you need a join table OR add FirmID to
tblAgreements?

Pew responds:
FirmID has always been a foreign key in my tblAgreements. I'm sorry if I
didn't make this clear. I'm not sure how we got into a discussion of the
relationship between Firms and Agreements, as I'm not aware of any question
or problem in this area.
Pew responds:
I was not aware of any reason why a join table could not also contain data
fields pertinent to the join. For example, in a Retailing application,
wouldn't you join Orders and Products to create an Order Details table, and
wouldn't that table also have to include a field for Quantity?
Pew responds:
To extend the Retailing example given above, what you are suggesting sounds
like marrying Quantity to Product. In my case, the "item" we are "buying"
would be (for example) "General Liability Limits, per Ocurrence". The
"quantity" would be the dollar amount of the coverage, which may vary from
one "order" to the next. Now, I could redefine the "item" as "$1m GL per
Occurrence", thus combining quantity with description, but this would require
creating separate items for every conceivable dollar amount. This in itself
might not be too bad, but the bigger problem lies in not being able to
compare two such items programmatically for SIZE. In other words, if we
required $1m and the certificate provided $2m, we could easily tell that we
did not get what we asked for, but we could not tell that we actually
received MORE than we asked for.
Pew responds:
The CertificateValue is NOT an Insurance Requirement, but is what is being
OFFERED in order to meet the requirment. In the above example, we required a
$1m policy, but the certificate provided $2m worth of coverage. Again, in a
retailing context, this might be analogous to Quantity Ordered vs Quantity
Shipped.

What is the problem, then? The problem (for the umpteenth time) is that my
insurance items are not all measured in dollars. Some are dates, some are
yes/no, and some are multiple choice answers.

Gina, I will not take it amiss if you decide not to reply. I'm growing
weary of trying to explain, and it seems more and more evident that there are
not going to be any clear answers and that I just need to work this out for
myself.

--Pew

 

[Gina Whipp]

OBP,

No problem... these long posts can sometimes be confusing, so let's make it
easier...

1. Your answer...

FirmID has always been a foreign key in my tblAgreements. I'm sorry if I
didn't make this clear. I'm not sure how we got into a discussion of the
relationship between Firms and Agreements, as I'm not aware of any
question
or problem in this area.

*** My Answer...
I just wasn't sure where that field was so I mentioned that but I see you
have that solved.


2. You answer...

I was not aware of any reason why a join table could not also contain data
fields pertinent to the join. For example, in a Retailing application,
wouldn't you join Orders and Products to create an Order Details table,
and
wouldn't that table also have to include a field for Quantity?

My Answer...
I would join Order to Products but I think not the way you mean. I would
have a seperate OrderDetails table. (One Order to Many Order Details OR my
join table)

3. You answer...

To extend the Retailing example given above, what you are suggesting
sounds
like marrying Quantity to Product. In my case, the "item" we are "buying"
would be (for example) "General Liability Limits, per Ocurrence". The
"quantity" would be the dollar amount of the coverage, which may vary from
one "order" to the next. Now, I could redefine the "item" as "$1m GL per
Occurrence", thus combining quantity with description, but this would
require
creating separate items for every conceivable dollar amount. This in
itself
might not be too bad, but the bigger problem lies in not being able to
compare two such items programmatically for SIZE. In other words, if we
required $1m and the certificate provided $2m, we could easily tell that
we
did not get what we asked for, but we could not tell that we actually
received MORE than we asked for.

My Answer...
All of the above would be in my seperate table OrderDetails

4. You answer...

The CertificateValue is NOT an Insurance Requirement, but is what is being
OFFERED in order to meet the requirment. In the above example, we
required a
$1m policy, but the certificate provided $2m worth of coverage. Again, in
a
retailing context, this might be analogous to Quantity Ordered vs Quantity
Shipped.

What is the problem, then? The problem (for the umpteenth time) is that
my
insurance items are not all measured in dollars. Some are dates, some are
yes/no, and some are multiple choice answers.

My Answer...
Okay then this too would be in my OrderDetails table.

5. Your Answer...

Gina, I will not take it amiss if you decide not to reply. I'm growing
weary of trying to explain, and it seems more and more evident that there
are
not going to be any clear answers and that I just need to work this out
for
myself.

My Answer...
I understand why you feel fustrated. Trying to explain this to folks who
are not familar with this area can be fustrating and I am sorry you feel
that. Realize that I and a few others, are not familar with your area and
sometimes that means we ask alot of questions but all we are trying to do
understand to make sure we give you the best possible advice. Up to you
what you do from there...

--
Gina Whipp

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

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

 

[oldblindpew]

Gina,
Thanks for your patience.

I think we are both thinking the same thing. When I said "...join Orders
and Products to create an Order Details table", I meant that you create an
OrderDetails table for the purpose of joining Orders and Products.

When you said you would have a separate OrderDetails table with one Order
and many Order Details, I assume you meant "one Order and many Products".
When you said "...OR my join table" I asume you meant "OR" as in "i.e.", not
"OR" as in "alternatively".

The question still remains, how can a single field in my details table for
"requested value" (analogous to Quantity Ordered) hold values of differing
data types? In an earlier post you gave the only possible reply, that
different data types require different fields, and in response to my last
post, you stated that everything would go in my details table. This seems to
bring us back full circle to one record for each order, with a field for each
insurance parameter--the spreadsheet approach.

Could the answer lie in subdividing my insurance parameters down into at
least four different tables? Separate tables for yes/no-, text-, date-, and
currency-type parameters? Then I would need four join tables to link each of
those back to the Order table. It staggers my mind that Access would have so
much trouble dealing with large tables (i.e. having many fields) that it
would actually be better, perhaps even necessary, to create such a webwork of
linked tables.

--Pew

 

[Gina Whipp]

OBP...

Answers below in line

--
Gina Whipp

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

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

Gina,
Thanks for your patience.

I think we are both thinking the same thing. When I said "...join Orders
and Products to create an Order Details table", I meant that you create an
OrderDetails table for the purpose of joining Orders and Products. ******Yeppers******

When you said you would have a separate OrderDetails table with one Order
and many Order Details, I assume you meant "one Order and many Products".
When you said "...OR my join table" I asume you meant "OR" as in "i.e.",
not
"OR" as in "alternatively". ******Yeppers******

The question still remains, how can a single field in my details table for
"requested value" (analogous to Quantity Ordered) hold values of differing
data types? In an earlier post you gave the only possible reply, that
different data types require different fields, and in response to my last
post, you stated that everything would go in my details table. This seems
to
bring us back full circle to one record for each order, with a field for
each
insurance parameter--the spreadsheet approach.

Could the answer lie in subdividing my insurance parameters down into at
least four different tables? Separate tables for yes/no-, text-, date-,
and
currency-type parameters? Then I would need four join tables to link each
of
those back to the Order table. It staggers my mind that Access would have
so
much trouble dealing with large tables (i.e. having many fields) that it
would actually be better, perhaps even necessary, to create such a webwork
of
linked tables.

******OR 1 table with that field set as text BUT on the form set your
varying parameters. It is not the hard time dealing with large tables, it's
normalization AND when it's time to display the data to insure the table
will allow for *proper* display. That being said I have *broken* the
*rules* when the situation warrants it, sometimes and let me say that again
SOMETIMES the rules have to be broken to get what you need. In your case,
they might need some breaking BUT bare in mind that is the exception not the
rule.******

******AND THANK YOU for your patience.******

 

[oldblindpew]

Hi Fred,
Your solution echoes what has been under discussion since 8/27. Here is a
snippet from that date:

TblAgreements
AgreementID
<Agreement fields>

TblInsParameters
InsParameterID
InsParameterDescrip
<Other fields, if any>

TblCertificates
CertificateID
AgreementID
InsRequirementID (same as InsParameterID)
InsOfferingID (same as InsParameterID)

Your questions were:
Can we have a standardized list of Insurance Parameters? (Yes).
Can each parameter be defined by a text description plus a numeric value?
(No, I don't think so).

See also exchanges with Gina in this thread. The idea is to have a table of
Agreements and a table of Insurance Parameters (your "Types"). A third table
joins these together. You suggested calling it "Requirements"; I called it
"Certificates". If the same Parameter key appears in the join record as both
required and provided (offered), then the Certificate is valid for that
parameter.

I soon realized, however, that this model allows only for exact match
between required and offered parameters, whereas I need a "meets or exceeds"
standard. It occurred to me to add a numeric field to the join table to
contain the "value" of the parameter, just as you suggest. Of course, two
fields would be needed, one for the value as required, and another for the
value as offered.

But what about expiration date? The value of the expiration parameter is a
date, not a number; do I add another field to the join table for this? A
field which will remain empty for most records? This led to questioning the
whole idea of normalizing this data. At first, normalization seems to
simplify, but then you have to add in and tack on all the stuff that was
removed via normalization, so in the end it seems like you have more fields
than before, plus all the keys and indexes needed to make it work. But
apparently, one-to-one tables are such a horrifying prospect to experienced
Access developers as to make them worth going to almost any lengths to avoid.
(Sylvain Lafontaine offered a dissenting view elsewhere in this thread, but
his advice boils down to "maybe, maybe not").

Gina suggested storing the Parameter Values as text and then converting them
as needed to what they actually are, for display purposes. I'm not sure I
understood her correctly, but this sounds like another instance of having to
stand on one leg and sing "Doo-Dah" to get Access to work.

--Pew

 

[Fred]

Hello Pew,

A couple of notes - this is a complex application with a lot a good ideas in
a lot of threads. I really am not able to spend the time to absorb them
all.

Gina is an overall Access Goddess. You can't go wrong by listening to her.
Just make sure you communicate cleqrly by defining and consistently using
your you-specific terms.

My strength is structure, and heavy use of Access in things that I run
(companies) or rund data for (organizations). I'm not a developer.

First, there's one area of confusion.

Now you said: joins these together. You suggested calling it
"Requirements"; I called it "Certificates".

This conflicts with what I think that you said previously (and which I was
going by) which was the each agreement/subcontract has ONE certificate and
many requirements.

In my method, you only combined coverage into the same requirement record
when the TYPE matched. Otherwise they are seperate until reconciled or
combined. Dates of coverage can be added fields in both....just use
consistend definitions.

Again, I think that in this case going by the book (normalizing) is the best
way to serve YOU and YOUR NEEDS. Nothing to do with making it easy for
Access or being a purist.

 

[Gina Whipp]

Just peeking in...

Thank you Fred!

Peanut Gallery... Certificates = Requirements??? Using the same term is
VERY important. Could be one of the reasons we are all getting confused and
have to keep asking more questions.

--
Gina Whipp

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

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