Avoiding One to One Tables

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.

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

« -- 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" wrote in message
...
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 b 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,
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.


"Fred" wrote:

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

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

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


"Fred" wrote:

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

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. "

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

"Gina Whipp" wrote:

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

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


"Fred" wrote:

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

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

"Sylvain Lafontaine" wrote:

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

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

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


Thanks for your help.
--Pew

"Gina Whipp" wrote:

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

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


"Fred" wrote:

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

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

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. "