Relationships

[Guest]

I have a HUGE table with LOADS of fields that I have been informed is not the
way to go, that for example I should have a one to many relationship between
employee and dependent tables so.
I now have one table with empID (primary key), last name, first name,
address, date of birth, phone numbers, SIN # etc. etc.
2nd table has empID, dep1, dep1relation, dep1dateof birth, dep2,
dep2relation, dep2dateofbirth, dep3, ..... and so on.

Here are my questions:
1. Do I remove all fields from table one that are now in table two (dep1,
dep1relation...)?
2. If an employee down the road has more than 5 dependents(the number of
fields I have set up) do I just add new fields to the 2nd table
3. If I need to make changes that relate to both tables (i.e. remove a
record etc) do I make them on the first table.
4. Is there ANYTHING else I should know when doing this. I am a self taught
sole user in an organization with 175 employees so doing this right is
critical. This is our only means of housing this information.
Thank you for any advise and help you may offer.

 

[Guest]

Hi,

You need the Employee table. It needs a primary key. (BTW: SSANs are bad
primary keys, but that's a discussion for another day.)

Your Dependent table also needs a primary key. It also needs a foreign key
field that links to the Employee table primay key. You also need a
DepRelation and DepDOB fields plus any other data that you wish to cover.

Instead of going across with more fields / columns, you want to have each
dependent on his or her own record or row. That's a proper one-to-many
relationship with one Employee allowed to have No, One, or Many dependents.

Warning! Working with families can be a real challenge. For example you
could have both the mother and father as Employees. You might even have
something like two employees that got divorced and one married another
employee. If Step Child is a Relation, three people could have dibs on the
same dependent. In these cases you either need a business rule that states
only one person can claim the same dependent or a linking/bridging table
between the Employee and Dependents tables.

I highly suggest getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceding any further
on this database.

 

[Guest]

You did not set up the dependent table correctly. The way you set did it
there is no one-to-many relations. Instead of separate fields for each
dependent you need to have separate records.dep1relation...)?
Yes it is redundant data.fields I have set up) do I just add new fields to the 2nd table
No you just add more records - see the above about separate records for each
dependent.record etc) do I make them on the first table.
When you have the one-to-many relation the only information in both tables
is the empID and that will not change unless there was a data entry mistake
in the begining.You might add a status date and status field to the dependents table for
when a child reaches an age that is no longer considered a dependent for
financial/insurance purposes or some such. A full-time student may still be
covered by medical insurance where as a child of the same age and not in
school may not.

 

[Guest]

Karl you are wonderful!! Your response to point 4 'reaching the age of non
dependency', is what triggered this whole thing!!

What is a status field?

So instead of having dep1, dep2, dep3 etc. you are saying have one dependent
field and all records for all employees listed in that field? If so, would
my dependant table consist of the following fields in order to have the
proper one to many relationship:
EmpID (same as main table) and primary key of main table
should I repeat the EmpID for each dependant or is there a better way to
identify the records
DependantName
DependantRelation
DeptendantBirthdate
StatusDate

If I wanted to further perfect my tables and have a table for Previous
Positions Held would I set it up basically the same and can you relate the
main table to several tables with one to many relationship.

By the way thank you so much for your help and I am sorry to bother you so
much but you are awesome.

 

[Guest]

The post by Jerry Whittle laid out your tables pretty good.
identify the records
Yes, each dependent record has the EmpID which is known as a foreign key in
this table and primary key in the employee table. The employee table only
has one record per employee (EmpID). The dependent table can have many -
thus the one-to-many relationship.
A status field can be a text field identifying the dependent status --
-Full-time student < 23 years old
-Minor
-Major (not a dependent)
The status field can also be a number field with a related table that has
the primary key and text to list all possible statuses. This way you can
only pick from the list until management may approve a new status to be added
to the list.
Additionaly you may use a one-to-many table for the status so as to track
status changes. In this case the relation from dependent to status table
will use the DependentID.
This would be setup the same as above to track all statuses.

 

[Marc Noon]

Just remember this... you need the primary key of the parent table in the
sub table.

Not the other way around

-mnoon