Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ironcladlou
Starting Member
2 Posts |
 Posted - 2004-01-16 : 14:28:28
|
| A simple problem with a couple solutions I've come up with, but neither of which I can decide upon...I want to make a small, simple database that contains a simple office hierachy. For example, tables may include -Office -Department -Subdepartment -Teams -EmployeesA department may only belong to one office. A subdepartment may only belong to one department. A team may only belong to one Subdepartment. Employees must be in a department, but also may or may not be in a subdepartment or a team.The two options I've figured are something along these lines:Solution 1 (simplified example)----------Table Offices -office_id -name -address -..Table Departments -dept_id -office_id -description -...Table SubDepartments -subdept_id -dept_id -description -...Table Teams -team_id -subdept_id -description -...Table Employees -emp_id -dept_id -subdept_id (NULLABLE)Table EmployeeTeams (Juntion table) -emp_id -team_idUsing this solution, I can store and retrieve all the information needed. Employees' optional subdepartment status is set by the nullable field, and outer joins against Employees/SubDepartments will simply return NULL for subdepartment fields that don't apply to employees not in subdepartments. Problem: In this solution, in order to go back up the hierarchy, a LOT of joins are required for each level in the hierarchy. So, for example, if I wanted to determine what office an employee is in, it would do several joins.Is this a problem?The only way around this that I can figure is to store parent and grandparent information in each level (For example, add office_id to all tables, etc). This would make determining lineage far easier, but would cause a lot of redundant information everywhere.Which is the better solution? Using joins to go back up the known-depth tree, or storing the hierarchy information for all previous levels in each level of the tree?Or is there a way to accomplish this that I have overlooked?Thanks for bearing with me! :) |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-16 : 15:17:42
|
| Why not carry the keys down...it'll codt in overhead on updates and inserts (although it actually might be faster) but you'd get performance gains since you won't have to join backFor example...Table SubDepartments-subdept_id-dept_id-OfficeId-description-...I've seen several systems handle it that way....PeopleSoft does it that way....Brett8-) |
 |
|
|
|
|
|
|
|
Office hierachy design question