Creating a Proper Table for Employee Tracking

A

Amar Kapadia

Hello Everyone:

Before I ask my question, just a quick thanks to Mr. Van T. Dihn. You had
helped me about a month or two ago and I never thanked you so a belated
thanks. Always appreciated. Okay, onto my question...

I need to create a table to handle employees working in my department.
Mostly, a pretty easy request, but then I was asked that my table should
allow for some employee hierarachy reports. At our organization, we have
employees who report to team leaders who report to Managers who ultimately
report to a single Executive Director. So I am not sure how to construct my
table (or tables). Allow me to list the following employee structure:

Bob Smith - Employee (Reports to Team Leader Chris Regal)
Tara Rollins - Employee (Reports To Team Leader Chris Regal)
Chris Regal - Team Leader (Reports to Manager Marcia Gellis)

Bart Simpson - Employee (Reports to Team Leader Heidi Jones)
Heidi Jones - Team Leader (Reports to Manager William Glenn)

Marcia Gellis - Manager (Reports to Executive Director Hayden Johnson)
William Glenn - Manager (Reports to Executive Director Hayden Johnson)
Hayden Johnson - Executive Director (Reports to Executive Director Hayden
Johnson)

The final result should be that if I run a query on Marcia Gellis, then I
should get (Chris Regal, Tara Rollins, and Bob Smith). If I run a query on
Chris Regal, I should only get (Tara Rollins and Bob Smith). And finally,
if I run the query on Hayden Johnson I should get everyone listed above
(except Hayden)

Goodness, where do I start? I guess, with a simple table such as:

employee_id (PK)
employee_first_name
employee_last_name

Then do I add a reports_to_id field and do a lookup from the very same
employee table? Is that possible or will that cause a "circular lookup
thingy" error? Or do I add two fields called team_leader_id and manager_id
in my employee table. That seems a bit wrong only in that if I select a
team_leader_id, I should automatically be able to extract a Manager for that
employee.

Sorry if that was long and I hope I conveyed the gist of my trouble. Any
assistance would be awesome. Thanks in advance.

Amar
 
D

Duane Hookom

I would just add a ReportsTo_ID. You can self join the table to as many
levels as you have levels in your company.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top