Relationship Help Please

[Guest]

I have created an employee table with a unique employee ID(also my Primary
Key) for each employee. I track each employee's various trainings attended
and the date they completed each training. I established a one-to-many
relationship based on the employeeID. That works fine. I am able to view
all the trainings each employee took and the dates they completed them. My
problem is, now I need to track all of their qualifications, specialties, and
skills the same way. Should I set them up the same way? Using a one-to-many
for each table based on employeeID looks wrong. What am I missing, or how
should I go about setting up relationships of each table back to the employee
table so that I can see all of these things for each employee? Eventually, I
will need to be able to search for an employee/s based on this information.
(ie. find employee/s with this qualification, or find employee/s with this
qualification and specialty, or any combination above.)
Your assistance is greatly appreciated.

 

[John Spencer]

Yes, you should be setting up a table or tables to hold the employees'
attributes. You can use your implied structure of separate tables for
skills, specialties, and qualifications or you could use a more generic
table that contained all three of these things with an additional field that
indicated the type of attribute.

One thing to guide you on that is whether you have differenct types of
information to store regarding a skill versus a specialty versus a
qualification.

For instance, a qualification might be valid for only a specific date range
while the other two may not have that limit. A specialty might require you
recording a certification date, etc.

The bottom line is you should have a one to many relationship from the
employee to his attributes. And it is actually a many to many relationship
although you may not need to model that. An employee has many skills and
each skill is possessed by many employees.