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 |
|
dba123
Yak Posting Veteran
90 Posts |
 Posted - 2008-07-29 : 22:29:07
|
| I'm taking a look at an ERD a consultant made. It doesn't rub me right but maybe this is common. I don't particularily think this is a good practice though.The situation is this. There are 2 tables:UserTaskA user could be both an approver, and also an implementer. That is, a user may approve a task if they are get a request requiring their approval in some process. Or they may be the one to implement a task. For example, I may implement a task to install an application on someone's PC. Therefore I'd be an impelementer. So there are basically two types of tasks as well as two types of User Accounts.My consultant did this:User TableUserIDyada yadaTask TableTaskIDForPersonID (which is UserID, the person who made the request)AssignedToPersonID (person who will approve or implement)yada yadaI guess I've never seen two FKs be the same PK from a table. In this instance I see 2 FKs, both are UserID (ForPersonID, and AssignedToPersonID)The way I would design this is to have a UserTask bridge table and also a TaskType table. The UserTask table would look like thisUserTaskUserIDTaskIDTAskTypeIDthat looks more sound to me. I just think adding two of the same type of IDs to a table and renaming them is a bit odd. You tell me. |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-07-30 : 03:52:18
|
| Given the requirements there is nothing wrong with the original design in my opinion.If there is a need for multiple requestors and/or implementors/approvers then you can only do it by having a table containing tasks, users and what role that user is taking. |
 |
|
|
dba123
Yak Posting Veteran
90 Posts |
Posted - 2008-07-30 : 09:17:39
|
| I just have never seen 2 FKs that are of the same PK |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-07-30 : 09:45:23
|
quote:
Originally posted by dba123
I just have never seen 2 FKs that are of the same PK
Then you haven't LIVED, man!Nothing wrong with that design.e4 d5 xd5 Nf6 |
|
|
|
dba123
Yak Posting Veteran
90 Posts |
Posted - 2008-07-30 : 11:01:01
|
| Thanks, that's what I wanted to know. |
|
|
|
|
|
|
2 UserID FK fields in a table bad design?