Design Help!

[alwayshouston]

Hi All
I need assistance in designing the database that contains multiple parent to child relationship (Hiearchical). I would like store Employee information with multiple reporting structure. The structure of the organization is as follow: Supervisor (On the highest Level), Manager (On the second highest Level OPTIONAL) and Employee ( on the lowest level). A supervisor can or cannot have Manager that reports to him or her. A Manager must have employees report to them. How would I define these three tables in Access with hiearchical relationship? Please note the Supervisor can have a manager or Supervisor just only have employees reporting to them. If my scenario does not make any sense, please let me know.

Thanks for your help in advance!

 

[cheerio]

Usually you would do this within the one employee table.

If your key field for employees is staffID you have another field say bossID that has the staffID of that person's boss.

You can list bosses and their direct subordinates by joining the table to itself.

 

[alwayshouston]

Thanks! I can use self joins to the get the relationship. But my problem is that I have few tables that only stores Supervisor level or Manager's or Employee level data. How would I maintain a referential integrity for specific employee type (type: Supervisor or Manager or Employee) table? For example, lets say that I have one employee table with relationship define as you suggested. I have another table that stores only Supervisor Information. How would I maintain referential integrity from Employee table to this new table that only holds supervisor information? Any thoughts on that?

 

[lespaul]

Why would you need a separate table for Supervisor Information?

tblEmployees
EmployeeID
FirstName
LastName
IsSupervisor (logic)
ReportsTo (FK EmployeeID)

or something along these lines should work.

 

[Golom]

lespaul has provided a good suggestion about how to store the information in the same table. If you're feeling brave and want to see how to use structures like this ... check out

http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci537290,00.html

 

[lespaul]

It always makes my day when you compliment my posts! You are the SQL/Access GURU, so I'm going to go preen now!

Thanks!

Leslie

 

[alwayshouston]

Thanks to everyone that replied to my thread. You guys are too good. I guess I was vague in my previous thread when I said that I want to store only Supervisor level information. What I mean by that is that lets say I have a table (SUPERVISOR_EXPENSE) that stores Supervisor Expenses ONLY. How would I maintain referential integrity between Employee Table and SUPERVISOR_EXPENSE table? If I have Emp_ID as a foreign in SUPERVISOR_EXPENSE, then any employee could have record in the table. I want to limit SUPERVISOR_EXPENSE table to only supervisors not all employees. Any ideas, observations, and feedback is welcome!!!!

 

[lespaul]

Only allow an entry to Supervisor_Expense if the 'IsSupervisor' field = Y


Leslie

 

[alwayshouston]

How can you allow an entry to Supervisor_Expense if the 'IsSupervisor' field = Y in Employee table in MS ACCESS? Can you do this by going to Tools>>Relationship and define there?

 

[psemianonymous]

The referential integrity way to do this is to have your "supervisor flag" stored in a separate table. You would just have a "Supervisor" table with the Emp_ID as the primary key. This would create a 1:1 relationship between the Employee table and the Supervisor table. You could then create a link between your Supervisor_expenses table and your supervisor table, and be guaranteed referential integrity.


Let me just tell you though: Access doesn't support these types of joins AS WELL AS the denormalized supervisor flag. Try making a form which has the supervisor information on the same form as the employee information--I have been struggling with this myself recently. There are trade-offs to be made--benefits of normalization (guaranteed data integrity) vs. benefits of denormalization (using Access' bound forms as they were meant to be used).

If you're curious as to why I wanted to try this approach, read this approach to avoid using Null values in your database, in any situation:

http://www.hughdarwen.freeola.com/TheThirdManifesto.web/Missing-info-without-nulls.pdf

 

[alwayshouston]

Thanks! I incorporated your design in my database. It works like a charm! Thanks for the feedback.