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  

Avoiding One to One Tables



 
 
Thread Tools Display Modes
  #21  
Old September 15th, 2009, 10:34 PM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default Avoiding One to One Tables

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

"Gina Whipp" wrote:

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

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




  #22  
Old September 15th, 2009, 10:46 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Avoiding One to One Tables

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

  #23  
Old September 15th, 2009, 10:54 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Avoiding One to One Tables

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



  #24  
Old September 15th, 2009, 10:56 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Avoiding One to One Tables

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

  #25  
Old September 16th, 2009, 01:11 PM posted to microsoft.public.access.tablesdbdesign
Michael Gramelspacher
external usenet poster
 
Posts: 482
Default Avoiding One to One Tables

On Tue, 15 Sep 2009 14:34:05 -0700, oldblindpew wrote:

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

"Gina Whipp" wrote:

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

"Fred" wrote in message
...
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)
);
  #26  
Old September 17th, 2009, 12:02 AM posted to microsoft.public.access.tablesdbdesign
oldblindpew
external usenet poster
 
Posts: 128
Default Avoiding One to One Tables

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

"Michael Gramelspacher" wrote:

On Tue, 15 Sep 2009 14:34:05 -0700, oldblindpew wrote:

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

"Gina Whipp" wrote:

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

"Fred" wrote in message
...
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)
);

  #27  
Old September 17th, 2009, 12:53 AM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Avoiding One to One Tables

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

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

"Michael Gramelspacher" wrote:

On Tue, 15 Sep 2009 14:34:05 -0700, oldblindpew
wrote:

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

"Gina Whipp" wrote:

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

"Fred" wrote in message
...
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)
);



 




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 08:52 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.