Are you stuck with this data model? It would seem much easier if you had a table to hold the case information (tblCase) and just one table to hold the status (tblCaseStatus). Then have a lookup table with the various valid statuses that you can set the case to (tblStatus).

You could then keep track of the changes the case goes through.

IE:

CREATE TABLE tblCase

   (

    iCaseID int 

    ...

    )





CREATE TABLE tblCaseStatus

   (

    iCaseID int

    , iStatusID int

    ,...

    , dtCreatedDate datetime default (GetDate())

    )



CREATE TABLE tblStatus

   (

    iStatusID  int

    , ...

    )

In this case the current status of the case would be the last record put in the tblCaseStatus table.

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer