Avoiding One to One Tables

[oldblindpew]

Gina and Fred,
Yes, I hear you. Naming is a problem because structure is a problem. With
the spreadsheet approach, I knew I needed a Requirements table, a
Certificates table, and a Validation table. Once I departed from this and
tried to normalize, it led to confusion about what my entities were and
therefore what they should be named. It doesn't help that there seems to be
a lack of good candidates for names.

I think that insurance elements, or entities or "things", outside the
context of any specific Agreement or Certificate, should be called Insurance
Parameters, and should go in a table of Insurance Parameters with one record
for each parameter.

Joining a Parameter with an Agreement and adding a "Required Value" field
gives you an Insurance Requirement, so the first impluse was to call the join
table an Insurance Requirements table.

However, adding a "Provided Value" field makes this same join table begin to
look like a collection of Certificate of Insurance items, with built-in
references back to the Required Value imposed by the Agreement. So my second
impulse was to call the join table a Certificates table. This was also
somewhat motivated by difficulty in getting people to understand what I meant
by "Requirements" as an entity.

More recently I have been thinking of it as a "Validation" table, because
each record ultimately brings together a Requirement and a Certificate
offering, to see if they agree.

-Pew

 

[Fred]

Hello Gina,

On a lot of structure questions in the forum the situation is that someone
is so close to or so expert with their business or process that they can get
by with using the same noun to refer to, what is (in DB structure) multiple
different types of entities, or to in essence use (in DB structure terms)
definitions that can vary. Works fine until one needs to create a DB
structure while trying to use those "nouns", or rigorously communicate the
situation to this forum. Not sure whether or not such is the case here.

Fred

 

[Gina Whipp]

Fred,

Good Point! Also, not sure...

--
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 Pew,

Didn't know that you posted after I wrote but before I posted the last one.

I think that my previously recommended
nsuranceRequirementsAndOrInsuranceItemsProvided table (of course, shorten
that name) is the same as your "validations" table idea.

Sincerely,

Fred

 

[Michael Gramelspacher]

Gina and Fred,
Yes, I hear you. Naming is a problem because structure is a problem. With
the spreadsheet approach, I knew I needed a Requirements table, a
Certificates table, and a Validation table. Once I departed from this and
tried to normalize, it led to confusion about what my entities were and
therefore what they should be named. It doesn't help that there seems to be
a lack of good candidates for names.

I think that insurance elements, or entities or "things", outside the
context of any specific Agreement or Certificate, should be called Insurance
Parameters, and should go in a table of Insurance Parameters with one record
for each parameter.

Joining a Parameter with an Agreement and adding a "Required Value" field
gives you an Insurance Requirement, so the first impluse was to call the join
table an Insurance Requirements table.

However, adding a "Provided Value" field makes this same join table begin to
look like a collection of Certificate of Insurance items, with built-in
references back to the Required Value imposed by the Agreement. So my second
impulse was to call the join table a Certificates table. This was also
somewhat motivated by difficulty in getting people to understand what I meant
by "Requirements" as an entity.

More recently I have been thinking of it as a "Validation" table, because
each record ultimately brings together a Requirement and a Certificate
offering, to see if they agree.

-Pew

This thread has been going on a long time, so I thought I would add my 2 cents worth:

CREATE TABLE Firms (
firm_no LONG NOT NULL,
firm_name TEXT(50) NOT NULL,
PRIMARY KEY (firm_no)
);
CREATE TABLE Agreements (
agreement_no LONG NOT NULL,
firm_no LONG NOT NULL
REFERENCES Firms (firm_no),
agreement_date DATETIME NOT NULL,
start_by_date DATETIME NOT NULL,
finish_by_date DATETIME NOT NULL,
PRIMARY KEY (agreement_no)
);
CREATE TABLE [Insurance Options Master List] (
option_no LONG NOT NULL,
option_name TEXT(50) NOT NULL,
PRIMARY KEY (option_no)
);
CREATE TABLE [Agreement Options] (
agreement_no LONG NOT NULL
REFERENCES Agreements (agreement_no),
option_no LONG NOT NULL
REFERENCES [Insurance Options Master List] (option_no),
coverage_amount CURRENCY NOT NULL,
PRIMARY KEY (agreement_no, option_no)
);
CREATE TABLE [Insurance Policies] (
policy_no LONG NOT NULL,
firm_no LONG NOT NULL
REFERENCES Firms (firm_no),
issuer_name TEXT(50) NOT NULL,
effective_date DATETIME,
termination_date DATETIME,
PRIMARY KEY (policy_no, firm_no)
);
CREATE TABLE [Policy Options] (
policy_no LONG NULL,
firm_no LONG NOT NULL,
FOREIGN KEY (policy_no, firm_no)
REFERENCES [Insurance Policies] (policy_no, firm_no),
option_no LONG NOT NULL
REFERENCES [Insurance Options Master List] (option_no),
coverage_amount CURRENCY NOT NULL,
PRIMARY KEY (policy_no, firm_no, option_no)
);

 

[oldblindpew]

Hello Michael,
Thanks for your suggestions. I'm sorry this has become such a bother, and I
appreciate your input.

It looks like you are introducing the term "Options" vs my "Parameters",
which has the advantage of being shorter.

You are also first to suggest a separate table for Policies, although I
think for my purposes, Policies and Certificates can be viewed as the same
thing. A Certificate summarizes the coverage provided by a Policy. The
advantage of a separate table for Certificates/Policies is that it is a
parent table, where one can store stuff that doesn't seem to fit well into a
detail/join table, such as expiration dates.

You are suggesting two join tables, one for Agreements/Options, and another
for Policies/Options. This would put what I've called Insurance Requirements
in the first table, and Insurance Offerings in the second. I'm a little
fuzzy on how to compare the two, for validation. Also you seem to assume
that all insurance options are quanitified as dollar amounts
(coverage_amount), whereas I've been looking at insurance options as
representing four different data types.

This last point is, I think, right at the heart of my normalization dilemma.
I've been considering trying to reduce the number of data types by treating
everything except dates as integers. I'm fuzzy again on how my validation
procedure would know what to do with these integer values, in other words,
whether to check for exact match, or to check for greater-than-or-equal-to.

I suppose I could assume that regardless of structure, there will always be
a way somehow to write whatever procedure is needed, but I've been burned by
this before. I've altered structure in the past only to find that I had to
devise entirely different code in VBA to restore functionality.

I'm probably going to have to shelve this project again to work on other
things for a while. This news may come as a relief to us all. Thanks again,
everyone.

--Pew

Gina and Fred,
Yes, I hear you. Naming is a problem because structure is a problem. With
the spreadsheet approach, I knew I needed a Requirements table, a
Certificates table, and a Validation table. Once I departed from this and
tried to normalize, it led to confusion about what my entities were and
therefore what they should be named. It doesn't help that there seems to be
a lack of good candidates for names.

I think that insurance elements, or entities or "things", outside the
context of any specific Agreement or Certificate, should be called Insurance
Parameters, and should go in a table of Insurance Parameters with one record
for each parameter.

Joining a Parameter with an Agreement and adding a "Required Value" field
gives you an Insurance Requirement, so the first impluse was to call the join
table an Insurance Requirements table.

However, adding a "Provided Value" field makes this same join table begin to
look like a collection of Certificate of Insurance items, with built-in
references back to the Required Value imposed by the Agreement. So my second
impulse was to call the join table a Certificates table. This was also
somewhat motivated by difficulty in getting people to understand what I meant
by "Requirements" as an entity.

More recently I have been thinking of it as a "Validation" table, because
each record ultimately brings together a Requirement and a Certificate
offering, to see if they agree.

-Pew

This thread has been going on a long time, so I thought I would add my 2 cents worth:

CREATE TABLE Firms (
firm_no LONG NOT NULL,
firm_name TEXT(50) NOT NULL,
PRIMARY KEY (firm_no)
);
CREATE TABLE Agreements (
agreement_no LONG NOT NULL,
firm_no LONG NOT NULL
REFERENCES Firms (firm_no),
agreement_date DATETIME NOT NULL,
start_by_date DATETIME NOT NULL,
finish_by_date DATETIME NOT NULL,
PRIMARY KEY (agreement_no)
);
CREATE TABLE [Insurance Options Master List] (
option_no LONG NOT NULL,
option_name TEXT(50) NOT NULL,
PRIMARY KEY (option_no)
);
CREATE TABLE [Agreement Options] (
agreement_no LONG NOT NULL
REFERENCES Agreements (agreement_no),
option_no LONG NOT NULL
REFERENCES [Insurance Options Master List] (option_no),
coverage_amount CURRENCY NOT NULL,
PRIMARY KEY (agreement_no, option_no)
);
CREATE TABLE [Insurance Policies] (
policy_no LONG NOT NULL,
firm_no LONG NOT NULL
REFERENCES Firms (firm_no),
issuer_name TEXT(50) NOT NULL,
effective_date DATETIME,
termination_date DATETIME,
PRIMARY KEY (policy_no, firm_no)
);
CREATE TABLE [Policy Options] (
policy_no LONG NULL,
firm_no LONG NOT NULL,
FOREIGN KEY (policy_no, firm_no)
REFERENCES [Insurance Policies] (policy_no, firm_no),
option_no LONG NOT NULL
REFERENCES [Insurance Options Master List] (option_no),
coverage_amount CURRENCY NOT NULL,
PRIMARY KEY (policy_no, firm_no, option_no)
);

 

[Gina Whipp]

OBP,

Not a relief... I myself was enjoying the exchange. Hopefully, we didn't
*scare* you off!

--
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 Michael,
Thanks for your suggestions. I'm sorry this has become such a bother, and
I
appreciate your input.

It looks like you are introducing the term "Options" vs my "Parameters",
which has the advantage of being shorter.

You are also first to suggest a separate table for Policies, although I
think for my purposes, Policies and Certificates can be viewed as the same
thing. A Certificate summarizes the coverage provided by a Policy. The
advantage of a separate table for Certificates/Policies is that it is a
parent table, where one can store stuff that doesn't seem to fit well into
a
detail/join table, such as expiration dates.

You are suggesting two join tables, one for Agreements/Options, and
another
for Policies/Options. This would put what I've called Insurance
Requirements
in the first table, and Insurance Offerings in the second. I'm a little
fuzzy on how to compare the two, for validation. Also you seem to assume
that all insurance options are quanitified as dollar amounts
(coverage_amount), whereas I've been looking at insurance options as
representing four different data types.

This last point is, I think, right at the heart of my normalization
dilemma.
I've been considering trying to reduce the number of data types by
treating
everything except dates as integers. I'm fuzzy again on how my validation
procedure would know what to do with these integer values, in other words,
whether to check for exact match, or to check for
greater-than-or-equal-to.

I suppose I could assume that regardless of structure, there will always
be
a way somehow to write whatever procedure is needed, but I've been burned
by
this before. I've altered structure in the past only to find that I had
to
devise entirely different code in VBA to restore functionality.

I'm probably going to have to shelve this project again to work on other
things for a while. This news may come as a relief to us all. Thanks
again,
everyone.

--Pew

Gina and Fred,
Yes, I hear you. Naming is a problem because structure is a problem.
With
the spreadsheet approach, I knew I needed a Requirements table, a
Certificates table, and a Validation table. Once I departed from this
and
tried to normalize, it led to confusion about what my entities were and
therefore what they should be named. It doesn't help that there seems
to be
a lack of good candidates for names.

I think that insurance elements, or entities or "things", outside the
context of any specific Agreement or Certificate, should be called
Insurance
Parameters, and should go in a table of Insurance Parameters with one
record
for each parameter.

Joining a Parameter with an Agreement and adding a "Required Value"
field
gives you an Insurance Requirement, so the first impluse was to call the
join
table an Insurance Requirements table.

However, adding a "Provided Value" field makes this same join table
begin to
look like a collection of Certificate of Insurance items, with built-in
references back to the Required Value imposed by the Agreement. So my
second
impulse was to call the join table a Certificates table. This was also
somewhat motivated by difficulty in getting people to understand what I
meant
by "Requirements" as an entity.

More recently I have been thinking of it as a "Validation" table,
because
each record ultimately brings together a Requirement and a Certificate
offering, to see if they agree.

-Pew

:

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

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.

This thread has been going on a long time, so I thought I would add my 2
cents worth:

CREATE TABLE Firms (
firm_no LONG NOT NULL,
firm_name TEXT(50) NOT NULL,
PRIMARY KEY (firm_no)
);
CREATE TABLE Agreements (
agreement_no LONG NOT NULL,
firm_no LONG NOT NULL
REFERENCES Firms (firm_no),
agreement_date DATETIME NOT NULL,
start_by_date DATETIME NOT NULL,
finish_by_date DATETIME NOT NULL,
PRIMARY KEY (agreement_no)
);
CREATE TABLE [Insurance Options Master List] (
option_no LONG NOT NULL,
option_name TEXT(50) NOT NULL,
PRIMARY KEY (option_no)
);
CREATE TABLE [Agreement Options] (
agreement_no LONG NOT NULL
REFERENCES Agreements (agreement_no),
option_no LONG NOT NULL
REFERENCES [Insurance Options Master List] (option_no),
coverage_amount CURRENCY NOT NULL,
PRIMARY KEY (agreement_no, option_no)
);
CREATE TABLE [Insurance Policies] (
policy_no LONG NOT NULL,
firm_no LONG NOT NULL
REFERENCES Firms (firm_no),
issuer_name TEXT(50) NOT NULL,
effective_date DATETIME,
termination_date DATETIME,
PRIMARY KEY (policy_no, firm_no)
);
CREATE TABLE [Policy Options] (
policy_no LONG NULL,
firm_no LONG NOT NULL,
FOREIGN KEY (policy_no, firm_no)
REFERENCES [Insurance Policies] (policy_no, firm_no),
option_no LONG NOT NULL
REFERENCES [Insurance Options Master List] (option_no),
coverage_amount CURRENCY NOT NULL,
PRIMARY KEY (policy_no, firm_no, option_no)
);