Gary, interestingly enough, the application has an audit table already established with the typical fields (AuditDateTime,User,TableName,FieldName,OldValue,NewValue,etc) and as it turns out, this is one of the problems with the original design. The CaseStatusOpen & CaseStatusClosed tables each had a dozen or so entries in them, with both ID fields starting in sequential order from 1. Well, when the user Closed a Case and changed it from a status of Open with an ID of 2 to a status of Closed with an ID of 2, the audit process never entered a row into the audit table because it determined that the value never changed. What actually changed was which table it supposedly linked to because of the value of the Closed flag. I've had to write numerous queries similar to this just to show the descriptive values in the lookup tables.

FROM TBLCSCASE t

  INNER JOIN (

    SELECT 

      'Y' CLOSED,

      ITEMID,

      ITEMDESC

    FROM LKPCASESTATUSCLOSED

    UNION ALL

    SELECT

      'N' CLOSED,

      ITEMID,

      ITEMDESC

    FROM LKPCASESTATUSOPEN) ls 

      ON t.CASESTATUSID = ls.ITEMID

      AND t.CASECLOSED = ls.CLOSED

It seems to me that this join is problematic if you try to use two different tables to hold the close & open statuses, and then use the flag to determine which table should relate to the field. If you use 1 table to hold both closed & open statuses, then it seems to me that the status table should be a compound primary key consisting of StatusID & StatusClosed (or something similar). If StatusID is an IDENTITY value then you can actually use just a single join for reporting & lookups to find the descriptive value, but for referential integrity type operations (deletes,inserts,updates), you use the constraint relationship between the two fields of each table. Here's an example of what I'm thinking about created real quickly:

create table lkpcasestatus

(

casestatusid int not null identity(1,1),

casestatus char(1) not null,

casestatusdesc varchar(100) not null,

constraint pk_lkpcasestatus primary key (casestatus,casestatusid)

)

go

create table tblcase

(

caseid int not null identity(1,1),

casestatus char(1) not null,

casestatusid int not null,

casedesc varchar(100) not null,

constraint fk_tblcase foreign key (casestatus,casestatusid)

references lkpcasestatus (casestatus,casestatusid)

This seems to make it easy for reporting, (you only have to link the id field to determine the statusdesc), as well as for relational integrity, as well as making it impossible to have the same value in the table, but have it mean something else just because another field in the table changed.

Let me know what you think, maybe I'm missing something here.

David