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 |
|
Maxer
Yak Posting Veteran
51 Posts |
 Posted - 2008-02-28 : 11:36:20
|
| I have an Employee table that hasEmployeeID (PK)SupervisorID (which is really just another EmployeeID)..random junk...Now that part makes sense, everyone gets one and only one boss.Their boss can change, and therefore the SupervisorID would be updated.Now I have an EmployeeEvals table that has quarterly evaluation data.I want to relate these two tables.Eval table hasEvalID (PK)ReviewedEmployeeID (the one being evaluated)SupervisorID (the one doing the evaluation)Now I need to link this back to the employee table (at least I think I do).So I would want to relate it by the ReviewedEmployeeID going back to EmployeeID in the employee table and I also want the SupervisorID to do the same...But of course that won't work because that would seem to indicate that a single record on the Employees table (say EmployeeID 55) should have a matching (or could) record in the Eval table that would look likeEvalID: 12345ReviewedEmployeeID: 55SupervisorID: 55which of course wouldn't happen as an employee wouldn't evaluate themself.How do I handle the relationships for this properly?Do I just not link the SupervisorID back to anything? |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-02-28 : 11:49:05
|
| Both will point to EmployeeID of Employee table, that's fine. You just have to make sure that ID entered in ReviewedEmployeeID and SupervisorID is different by employing check constraint.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
Maxer
Yak Posting Veteran
51 Posts |
Posted - 2008-02-28 : 12:16:02
|
| Can you elaborate on that a bit, by a check constraint do you mean something in SQL Server or are you talking about data validation when it comes to the user interface validating input to make sure the user doesn't enter the same ID twice (well it will be a drop down box, but same thing)? |
|
|
|
|
|
|
Using Employee/Boss self referencing table