Table setup trouble

[mdooley via AccessMonster.com]

I am working on setting up a db of employee information. I have one table
with name, address, phone, etc. A second table for employee confidential
info that contians dob, etc. A third table that covers their paperwork
completed/turned in (date field) only. And then I am struggling with this
table. I want the table to be insurance information; option chosen and
premium amount for that option. There are 3 options to choose from and for
each option there is 5 groups to choose (for example opt 1, employee only;
opt 1, emplolyee and spouse, opt 1 employee and one child, opt 1 employee w/
more than one child and opt 1 employee and family...this goes on for the
second and third option as well). Now on top of that the premium amount will
change each year, now I don't want to over ride the premium amount each time
it changes, I would like to track it over the years. And when I finally
create the form for each employee I would like (though this isn't completely
nessessary) to only show the current years insurance info. I will create a
very similar table to track salary increases. Did I mention I have never
build a db before. Ha (although I have been reading alot on this forum a
couple books and some websites you guys recommended). ANY help would be
greatly appreciated!!

 

[Rod Plastow]

Oh my gosh, this is more than one table. I don't know your data and can only
base my suggestions on what you have written.

You need an Insurance Option table. From what you say this will have
initially only three rows. I would add columns in this table for state (i.e.
active, planned, supeseded and the like) and dates. Another poster will
probably leap in here and say, correctly that this is not normalised. If you
want to keep track (history) of all state changes then you need to mormalise
the design to have a second child table subordinate to Insurance Option that
has a row for each state change of the related Insurance Option.

You also need a child table subordinate to Insurance Option to define the
Insurance Groups. The same debate about states applies also here. Now, not
knowing your data, I suspect the set of groups is the same (or much the same)
for each option. If this is the case then you can define an Insurance Group
table and an Option Group table that is the intersection/juntion/cross
reference table resolving the many-to-many relationship between options and
groups. Again the debate about states applies, this time for each
option-group pairing but let me drop this debate from here on otherwise this
post will become too confused.

OK, so far I've described three tables: Insurance Option, Insurance Group
and Option Group. It is the last table that should be related to Employee.

Now you mention that each Option Group has a Premium and that you want to
keep a history of premiums. I envisage a fourth table that is a child of the
Option Group table. Let's call this one simply Premium. It will contain at
least four attributes: Option (foreign key), Group (foreign key), Date
Effective and Premium value.

So there is a minimum of four tables to fulfil your requirement.

If the date column on the Premium table is an Access date value (that is in
fact a decimal number) use the Max function to select the most recent (and
presumably current) premium value. The technique works on and numeric date
format come to that: yyyy, yyyymm, and the like.

Hope this helps. I wish we could draw diagrams on the board as it makes it
much easier to explain.

Regards,

Rod

 

[mdooley via AccessMonster.com]

I figured on more than one table (maybe 2) ha shows I am still learning -
let me sort through this and see if I can actually apply it. Thank you SOOO
much for your help - I will probably have more questions later.

 

[mdooley via AccessMonster.com]

Oh my gosh, this is more than one table. I don't know your data and can only
base my suggestions on what you have written.

You need an Insurance Option table. From what you say this will have
initially only three rows. I would add columns in this table for state (i.e.
active, planned, supeseded and the like) and dates.

I am not sure if I understand this part; could I use a yes/no column to say
whether the option is still active instead of state? And the date would that
be the effective date? And does it have anything to do with the date
effective below in the premiums table?

Another poster will
probably leap in here and say, correctly that this is not normalised. If you
want to keep track (history) of all state changes then you need to mormalise
the design to have a second child table subordinate to Insurance Option that
has a row for each state change of the related Insurance Option.

You also need a child table subordinate to Insurance Option to define the
Insurance Groups. The same debate about states applies also here. Now, not
knowing your data, I suspect the set of groups is the same (or much the same)
for each option. If this is the case then you can define an Insurance Group
table and an Option Group table that is the intersection/juntion/cross
reference table resolving the many-to-many relationship between options and
groups. Again the debate about states applies, this time for each
option-group pairing but let me drop this debate from here on otherwise this
post will become too confused.

OK, so far I've described three tables: Insurance Option, Insurance Group
and Option Group. It is the last table that should be related to Employee.

So for the first two tables I would use a auto generated number as the
primary key. So basically the table would consist of the ID and either an
option column or a group column. Am I understanding that correctly? Then
the Opt Group would be and auto generated ID (prim key) and then a column for
the ID from the Ins Opt table and a column for the ID from the Ins Group
table??

Now you mention that each Option Group has a Premium and that you want to
keep a history of premiums. I envisage a fourth table that is a child of the
Option Group table. Let's call this one simply Premium. It will contain at
least four attributes: Option (foreign key), Group (foreign key), Date
Effective and Premium value.

Here again use a auto generated Primary key??

So there is a minimum of four tables to fulfil your requirement.

And I am assuming that I would just query this all together into something
that makes sence to someone other than myself??

If the date column on the Premium table is an Access date value (that is in
fact a decimal number) use the Max function to select the most recent (and
presumably current) premium value. The technique works on and numeric date
format come to that: yyyy, yyyymm, and the like.

Hope this helps. I wish we could draw diagrams on the board as it makes it
much easier to explain.

Regards,

Rod

Hope these questions make sence. Thanks for the help.
'
Melinda

 

[Rod Plastow]

Hi Melinda,

Unfortunately I can't give you a definitive answer; you and only you know
your data and requirements to the extent of making such decisions. I assume
insurance options come and go so you probably need some way of identifying
the active ones. You could delete superseded options if you define cascade
delete through all the child tables; this however means losing all the
history; the parent 'anchor' record must remain if the child records are to
remain.

There are many ways of modeling states. You could implement a date pair:
date effective and date withdrawn. If date withdrawn is null then you assume
the option is active. Your suggestion is also viable; a simple boolean field
for active/inactive will work well if that's all you ever want to know about
the state of the option. By all means include date fields to further qualify
the state.

My suggestion caters for a situation where there are more than two states.
In fact a better normalised solution would be to create a separate life cycle
table that maintains a history of state changes for each option. I suspect
however this is overkill in your case.

Let me reiterate: you and only you know your data and requirements; it's
your decision.

No, this state refers to the state of the Insurance Option and has nothing
to do with the state or effective date of the Premium except to say that
presumably only active Insurance Options have premium changes.

Concerning the option and group tables close your senses to any debate
taking place in other threads about primary keys and the use of autonumbers.
For this exercise I recommend you use autonumbers as primary keys for the
tables. Yes you seem to have understood the concept but I'm not certain
about your terminology of 'an option column' and 'a group column.'

Let me try and guess at a possible design of the option table.

Name: tblInsuranceOption
Attributes:
OptionId Autonumber (pk)
DisplayName Text
Description Text
Underwriter Text or Long Integer if foreign key
Active Yes/No (I use your suggestion here)
....

The group table design would follow much the same pattern.

For the option group table the design would be something like the following.

Name: tblOptionGroup
Attributes:
Id Autonumber (pk)
OptionId Long Integer (fk)
GroupId Long Integer (fk)

Basically this table defines the valid combinations of options and groups.
However - and I can feel other posters reaching for their keyboards - there
are a couple of points to make concerning this table. First I have proposed
an independent, separate autonumber as a primary key. This is simply my
style; others would dispense with the autonumber and make the combination of
OptionId and GroupId to be the primary key. There are pros and cons but this
is not the place to open up that debate. The second and probably more
important point is that this is what is known as a key-only entity; it
consists entirely of keys and has attracted no data attributes. Key-only
entities always deserve a second look but more of that in a moment.

The fourth table I suggested is the premium table. For now let me just
consider the key values.

Name: tblPremium
Attributes:
PremiumId Autonumber (pk)
OptionId Long Integer (fk)
GroupId (fk)
....

But wait a minute, this look very similar to the foregoing option group
table except that the combination of option and group will no longer be
unique; there may be many records for each combination as the premiums
change. If the option group table attracts no data attributes and remains a
key-only entity, if you are not interested in a history of valid option group
combinations then you can 'throw away' the option group entity and not model
it as a table. Now you have a three-table design.

To address your last question I will describe the generic rather than try to
answer specifics. Data normalisation and the subsequent modeling of entities
into a relational database often means that the information is fragmented
across many tables. No one (although some have a bloody good try at it)
implements a fully normalised data model. I believe the theory of
normalisation recogonises eight levels, we all tend to stop at Third Normal
Form and even then for sheer practicality and performance (or disinterest)
denormalise some aspects of the model. My point is that sets of data that
the human brain deals with subconsciously get spread across many tables.
Yes, to recollate those sets of information use queries. Try to move away
from the concept of basing your forms and reports on tables, base them rather
on queries.

If you need any more support then may I suggest you email me at

(e-mail address removed)

as these posts are getting beyond the concept of this discussion group.

Regards,

Rod