help with Table structure

[SF]

Hi,

My office has a staff database consists of 3 tables. tblEmployee,
tblEducationAllowance, tblMedicalAllowance. tblEmployee has EmpID, Empname,
EmpPosition, DOB, DateofService...

Now my office needs to incorporate employee dependents into the database.
Each dependent will have medical allowance as well.

I cannot decice whether to use employee table for storing dependent info by
adding another filed to refer to the employyID (just like adding a filed for
supervisorID???) or to create new table for it. If I decide to create new
table for dependent, I will have difficulty in adding dependent info
including employee itseft into tblMedicalAllowance (EmployeeID FK) as
emploee and dependent located in 2 different tables.

SF

 

[Allen Browne]

I don't have a cut'n'dried answer to your question. Let's think through the
implations of the 2 approaches.

A very flexible approach might be to put all persons in one table
(employees, dependents, others), without any fields about employement. Use a
related table to store employement data, e.g. the dates they started and
terminated employment. This one-to-many copes with cases where a person left
for a while and came back, or radically changed roles. It's easy enough to
design a query with an inner join and criteria to get current employees.

You would then have a table to define dependencies between people. At the
simplest level, person 99 has person 86 as a dependent. Again, these related
records are probably date-limited. You can then figure out the associated
benefits for the dependencies in effect at any point in time. Clearly,
that's only the start of a structre, but will hopefully give you a grain to
work with.

Whether that's the best structure will really depend on what you are
actually doing. It may be overkill. The best structure is the very simplest
one that copes with everything that you really have to do.

HTH