Second Thoughts on Normalizing

  • Thread starter Thread starter oldblindpew
  • Start date Start date
O

oldblindpew

I'm having second thoughts on normalizing my Insurance information. I
thought I had a good theory this morning, but am having problems fleshing it
out. (Previous thread was One-To-One Tables).

I was originally attempting a spreadsheet-like approach, with an Insurance
Requirements table having a record for each Agreement (subcontract) and a
field for each requirement, (60 or 70 fields). Insurance requirements do not
vary greatly from one subcontract to the next, so there would be a lot of
similar stored values.

I was advised that this data was not properly normalized. All possible
insurance requirements should be stored once in a Requirements table, and be
referenced by the Agreements table. This would be a many-to-many
relationship. My brainstorm was that the junction table could have another
field added to it to store the insurance offering by the subcontractor. So
for example if Agreement #39 requires Insurance Requirement #2, and the sub's
Insurance Certificate provides Requirement #2, then the requirement has been
met.

But I ought to have mentioned the type of data involved here. Insurance
Requirements consist of check boxes, currency, and a few numeric fields.
Does it make sense to normalize these kinds of data? Will it save space and
improve performance? Normalizing turns these into a long list of yes-or-no
propositions: required or not required. For the currency and numeric fields
I'll neeed a record for every likely value. This in itself is not too bad,
but the standard for numerics is "meets or exceeds" rather than "exact
match". If we require a $1m General Liability Policy, and the sub furnishes
$2m, these won't match. I thought to solve this by adding another field to
the table for DollarValue, which would enable me to determine whether one
requirement was bigger or smaller than another. This field would remain
unused for yes/no type requirements. I think this would work, but it feels
like I'm going in circles, adding back capabilities that I took away by
normalizing.

Another problem is maintaining the requirements table. Normalizing turns it
into a long rambling one-dimensional list. In order to group and sort the
information, I felt it necessary to add a couple of fields for sort codes.

Finally, and I apologize for the length, I realized that a junction between
Agreement and Requirements does not a Certificate make. There is other
information from the Certificate that I cannot store in the junction table,
so I'll have to rethink. Sorry again for the ramble.
 
Steve said:
You appear to be really struggling with what to do. Part of it I'm afraid
to say is that you don't fully comprehend what normalized means. My
suggestion would be to post all your existing tables with the fields in
each and let us here in the newsgroup help you to design normalized
tables. Alternatively, I can help you arrive at the correct tables for a
small fee. I provide help with Access, Excel and Word applications for a
nominal fee. I have helped design many table structures for customers.
After we worked together to get a design for you, you could then proceed
on your own to design the forms, reports and associated queries. Contact
me if that will work for you.

Steve
(e-mail address removed)
Click to expand...



These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)
Yes, you are right but a database is the correct tool to use not a
spreadsheet.
Click to expand...


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP
 
On Thu, 27 Aug 2009 20:40:35 -0400, "John... Visio MVP"

Yes we can.
Steve, you're now officially voted off the island. Please do not try
to return here anymore. This island is for FREE newsgroup support.

-Tom.

<clip>
 
A few thought that won't take you to the finish line but hopefull start you
on a solid course towards it.

I would suggest that before you talk structure, to complete the process of
defining the entities that you want to database. This will inevitably
also inclde resolving some terminology issues. (both for yourself and to
communicate to us)

I'm assuming that your (so) far main databased entity which is a set of
requirements is really more than just an agglomeration of requirements, and
that it has some defining noun (like "proposal" or
"InstanceOfFufillingASetOfInsuranceRequirementsForACustomer" or
"MegaInsuranceDeal") that you haven't told us about. I would start using
that "noun" for that entity. Here I'll use "MegaInstranceDeal"

Next, I suspect that you are using the word "requirement" to refer to two or
three types of entities rather than one including:

1. A pick list of general types of requirements
2. An instance of (merely) saying that an item from #1 is required for this
MegaInsuranceDeal
3. An instance of saying that a particular amount of a coverage of one of
the types from #1 is required
4. An instance of fufillment of a #2 (e.g. by a subcontract)
5 An instance of fufillment of a #2 (e.g. by a subcontract)
6. Etc.

Once you get that done, it might lead you to your answer, or else I would
post again with those decision/ clarifications.

- - - - -

To jump ahead and comment on one item, a record in a junction table is
itself an entity, an "InstanceOfLinkage", an entity which your must more
fully define......is it a statement of a need?......is it a statement of
fufillment of a need? Once you have defined that, typically the only
fields which should be added to your junction tables are ones which contain
information that relate directly to that linkage entity, in a one-to-one
fashion.
 
John, FWIW, I figured MS was paying you guys a royalty for every response and
a bounty for responses that yielded a positive rating by the questioner. As
I see it, there are a lot of reasonably intelligent people out here
experiencing unusual levels of difficulty trying to understand and get
results from Access. MS should be paying you guys for pulling people through
this muck. --OBP
 
Fred, thank you for your considerate response.

I had already built a table for Insurance Requirements, a table for
Certificates, and a table for Validations, complete with all their fields and
some pretty nice forms for data entry. I then posed a question to the forum
about relationships between tables, and it turned into a discussion about
normalization. I became convinced what I had done was all wrong and needed a
complete redesign. Yesterday, while working on the redesign, I began to
second-guess my second guess.

"Requirements", as the term suggests, is a distinct list of insurance
requirements to be met by the subcontractor, as specified in our Agreement.
Here are some examples:

A General Liability Policy will be provided.
GL coverage will be in the form of a Commercial GL policy.
GL coverage will be on a "Per Project" basis.
GL coverage must be currently in force (not expired).
GL coverage limits shall be at least $1m per each occurrence.
etc...

These requirements can vary for different Agreements. Clarification: The
FIELD LIST is not expected to vary appreciably. The VALUES are what can
vary. For example, it's not likely, but we could waive the requirement for a
GL policy. We could require a different limit of liablity, maybe $2m instead
of $1m.

We are providing a list of insurance specifications. In the Certificate of
Insurance, the subcontractor is submitting a list of what he will provide.
We then want to compare his list with our list for compliance, and produce a
report showing where the Certificate is out of compliance. Maybe it would be
helpful to think of it like an order form and a packing list. We ordered
certain items, and when the shipment arrives, we check the packing list
against the order form to see we got what we ordered.

--OBP
 
Hello OBP,

Whether it be inadvertant or a specific choice, your post basically say that
you elected to not follow my suggestion. Which, of course is absolutely
fine, but that sort of ends the "Fred thread" on this particular question.


Fred
 
Fred, I thought I was saying that I've already done what you suggested, and
have a good understanding of the data and the tasks.--OBP
 
All the legitmate posters do it free. Each poster has his own reasons, they
have been helped before or they are always looking to put their skills to
the test.

The only exception to this in the Access newsgroups is stevie. His sole
purpose is to troll for work in FREE help forums. There are venues for job
posting, but it appears stevie can not handle the competition.

John... Visio MVP
 
Hello OBP,

You know know your business a zillion times better than I do, and what you
refer to as "data" is actually just some notes about the aspects of your
business that you want to database. My post recommended, as a starting
point, establishing clear rigorous definitions of the terms that you are
using, and then converting/ analyzing your business situation into a
structure of entities that are going to be databased. Respectrully, you did
not do that, hence my comment.

There are zillions of other ways (besides my suggested one) to successfully
et to the finish line. If interested in my little 'ole idea, please give it
a whirl and let us know the results.

Sincerely,

Fred
 
Back
Top