2 Foreign key references to the same table

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

2 Foreign key references to the same table

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-10-05 : 06:16:21

I was wondering, can you have an FK reference to the same table twice in the same row of another table?

For example, I have a table (only logical at this stage) called 'workflow stage' that contains a list of the different stages a job can have (such as 'file arrives at Feds', 'file processed', 'Billing report produced', 'client signoff', 'file sent to print', etc). I want to have an additional table that calculates the difference in time of when one stage finishes, and the next stage begins. I am aware that, as a rule, you do not retain calculated data in a DB, so am not sure whether I will do so as of yet, but I was wondering, if I do, can I reference the workflow stage table twice in one row? I need the workflow stage of when one stage begins (As from)and the subsequent workflow stage (as To). Therefore, both From and To columns will contain 'StageID' field, referencing the 'Workflow stage' table. Resulting in something like below:

From To Time Difference
File into Feds File Processed 2hrs
File Processed Samples/Billing Report Produced 4hrs

And when is it acceptable for a database to contain calculated values? When a lot of processing is involved and the values are used for other calculations for example?

Thanks

Hearty head pats

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-05 : 08:10:48

yes you can do that. Try it !


create table WorkFlowStage( StageID int primary key)

create table Jobs(JobID int primary key,
                  StartStageID int references WorkFlowStage (StageID),
                  EndStageID int references WorkFlowStage (StageID))

that's just an example, but it should work fine. (I might have a minor syntax error in there, i always have trouble with syntax for constraints for some reason !)

- Jeff

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-10-05 : 08:55:20

Brilliant! Thanks a lot. I can go ahead with a clear conscience.

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources