Table relations. Is this overkill? 2

[Dherrera]

i was wondering if it too much to set up tables as follows.
ive currently got an employees table with all employees listed on it. would it be a good idea to have each different level of employee on a different table. for example, employees would have their own table, their managers would be on another table and so on.

the reason for this question is because in my program employees submit expense reports to their managers and only their managers can approve it. so a specific employee has to be tied to his manager.

what do you all think? any comments are greatly appreciated.

 

[lespaul]

There are several ways to do this. If you look around there are a few threads that deal with this specifically. Basically what you are looking at is a table that has a relationship with ITSELF.

You could have:

tblEmployees
EmployeeID
EmployeeName
ManagerID (FK to tblEmployees.EmployeeID)

Another option could be having a department table:

tblEmployees
EmployeeID
EmployeeName
DepartmentID

tblDepartment
DepartmentID
DepartmentName

tblDeptMgrs
DepartmentID
ManagerID
StartDate
EndDate

(you could also have instead of the dates an ACTIVE MGR logical field)


that way you can get to the manager through the department.

There are a couple of suggestions and like I said there are a few threads that deal very in depth with this kind of issue.

I'll see if I can find any and point you in the right direction.

HTH

Leslie

 

[lespaul]

Ok, here are a few to review:

Thread700-687179
Thread700-838128
Thread700-853667

HTH

Leslie

 

[willir]

Dherrera

It depends on what your needs are.

Suppose you want to grab Joe Bob's phone number - you don't know if he is a lowly peon, or upper manager. How would you grab the phone number?

I suspect you just need a "title" or EmployeeType field on the employee file.

The way of handling "who is my manager" issue is to include the a field for the manager, and have it point back to the employee.

Example:

tblEmployee
EmployeeID - primary key
EmployeeLN - last name, text
EmployeeFN - first name, text
ManagerID - Foreign key pointing to EmployeeID on Employee table

Sample data would be as follows...
[tt]
EmployeeID Last + First Name ManagerID
1 Goodkind, Terry 1
2 Asimov, Issac 1
3 LeGuin, Ursula 1
4 Herbert, Frank 2
5 Pritchard, Terry 2
6 Weis, Margaret 3
7 Hickman, Tracy 3
[/tt]

Employees report to the ManagerID -> EmployeeID. A manager that points to himself is either the big cheese, or autonmous.

this solution works for many sitatuations, but it does not work if a person has more than one manager. For example, and EH&S expense to Manager #2, a Maintenance expense goes to #3, etc. In this case, you have to set up a many-to-many relaitonship with a joiner table that allows the expense to be routed per the requeirements. If you have a many-to-many, post back for more help if required.

Richard

4

 

[lespaul]

[aside]You know Richard, we REALLY need to discuss reading some day! (You left off Jordan you know! and what about Martin?)[/aside]

les

 

[Dherrera]

thanks guys.

i dont have any m:m tables, they are all normalized.
im not sure if im understanding this correctly, but you suggest that i just add another column to my current table for a ManagerID? am i ready this correctly? if i do

 

[PHV]

Do a keyword search in this forum for self join

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Hi Leslie - As I said, you type like the dickens -- you had two posts in the time I got one done, and yes, it would be nice to SciFi together. Note that I had Goodkind on top, but then I never read Jordan yet -- I am always afraid to start a new series and have to wait 2 or 3 years before I can finish it. Perhaps this summer, on top of some plateau, say in New Mexico - sigh...

Dherrera - Yes create a new new field that has the same charactersitcs as your employee primary key. PHV has used the correct technical term - self-join - the table joins to itself. I will leave the name of the field up to -- I just chose ManagerID as an example.

Richard

 

[Dherrera]

thanks to all.