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 |
|
simflex
Constraint Violating Yak Guru
327 Posts |
 Posted - 2006-08-30 : 15:30:36
|
| Gurus,We currently have an application that tracks employees, their titles, and their supervisors.The way our former developer has the db designed is presenting us with some problems.For instance, an employee's unique id is his/her titleID.Each jobtitle has one id that uniquely identifies that title and who occupies it.Recently, we discovered that *one* titleID has been assigned to more than one employee.We also found out that the reason for this is that this employee created a composite key of date datatype.Essentially, his logic is that if you vacate one job and assume another, he made you retain the same titleID and at the same time, assign the same titleID to the employee who assumes that position that was vacated. He would then use date to differentiate the two.If you vacated the job, the datefield, called vacated will have a date value showing the date you vacated the job. The emp who assumed that new job would have a blank value for that datefield.We found this causing confusion and would like to fix this.Any ideas how to fix the db in such that if you vacate a position and assume a new one, you get a new titleID and would have nothing to do with the position you vacated except for historical reasons.Sorry for my rambling and hope i hav not *totally* lost you. |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2006-08-30 : 15:49:23
|
| this makes me feel better about the first projects I designed out of college.Give the employees a unique id number. (identity probably) and make that the Primary Keythen simply make titleid a FK to the title table.if you want the history of an employee, probably need an table of employeeXtitle with a start/end date pairI the former developer is consulting now...________________________________________________I am a man, I can change, if I have to, I guess. |
 |
|
|
|
|
|
|
|
Ensuring unique key rule is enforced