More than one identity column in a SQL Server table

Question

I have the following problem to resolve and I think I need some help as I don't think what I'm thinking as a possible solution is correct:

I have a SQL Server table with an identity column. This table is going to store current data as well as historical data as described below:

1. Let's assume the following table:

   CREATE TABLE Patient 
   (PatientKey int IDENTITY(1,1) NOT NULL,
    PatientId int,
    UpdateDate datetime NOT NULL,
    UpdateUser int NOT NULL,
    RecordStatus tinyint NOT NULL)
   

PatientKey is the primary key and also the identity column. PatientId is to uniquely identify a patient (the current record as well as the historical ones). UpdateDate is for the date when a patient is inserted or updated. UpdateUser is for the key of the user who inserted or updated the patient. RecordStatus is to indicate the status of a record: 0 - history, 1 - current, and 2 - deleted.

1. In the table above PatientId + RecordStatus = 1 is to uniquely identify the current record of a patient.

2. When a new patient is inserted a new PatientKey is assigned to the record (this is done automatically by the SQL Server engine) and a new PatientId has to be calculated and assigned to the record too. Also, RecordStatus is set to 1.

3. When an existing patient is updated, the current record is updated to set RecordStatus equals to 0. Then, and a new row is inserted with the modified data of the patient. As part of the insert a new PatientKey is generated for the record, but no new PatientId is calculated (i.e. the PatientId doesn't change). Also, RecordStatus is set to 1.

4. When a patient is deleted, the current record is updated to set RecordStatus equals to 2.

The problem is with the calculation of the PatientId. I'm thinking about using a MAX(PatientId) + 1 in an stored procedure to obtain the new PatientId to assign to the new record, but this would be a problem with concurrency. If more than one user is creating a new patient at the same time the SP would basically calculate the same PatientId for the two of them and then I'd end having two different patients in the table with the same PatientId.

Then, I was thinking about putting the update in a SQL Server transaction to block the tables, but I think this would be too much. Besides, for complex updates I think the table would be blocked too long and other attempts to create patients would time out. Is there a better way to implement this?

Thanks.

Answer 1

/*

   this trigger exhibits the following behavior:
   for one or more inserts into the Patient table
   where the inserted record did not explicitly provide a value for PatientId
   the patient id is cacluated using the ROW_NUMBER function and
   an arbitrarily chosen seed value such that each new inserted row
   gets the next available patient id

   works for single or bulk inserts,
   only assigned patient ids to records who were inserted without one.

   NOTE 1: your inserts will probably need to be made within a transaction to avoid problems.
           i'll leave that for commentors to pick on ;)

   NOTE 2: you can then write an udpate trigger to ensure the immutability of PatientId

*/
CREATE TRIGGER [Rewards].[Trigger_Patient_Id]
   ON Patient
AFTER INSERT
AS
BEGIN
   SET NoCount ON

   /* create arbitrary seed value, just like an identity field */
   DECLARE @PatientIdSeed int = 512; 

   /* get the highest id currently in the database greater than the seed value */
   DECLARE @MaxPatientId int = (SELECT ISNULL(MAX(PatientId), @PatientIdSeed) FROM [Patient] WHERE PatientId > @PatientIdSeed) ;

   /* update the patient table... */
   UPDATE 
      [Patient]
   SET 
      /* ... getting new patient id ... */
      PatientId = NewPatientId
   FROM
      /* ... by joining the patient table ... */
      [Patient] AS Patient
      JOIN
         /* ... to this sub-query, that uses the ROW_NUMBER() function
                over the inserted table + MaxPatientId to get new PatientIds */
         (SELECT 
            inserted.Id,
            @MaxPatientId + ROW_NUMBER() OVER(ORDER BY inserted.Key) AS NewPatientId
          FROM 
            inserted
          /* remove this where clause if you wish to overwrite PatientId regardless of insert */
          WHERE
            inserted.PatientId IS NULL
         ) AS newinserts
         ON Patient.Key = newinserts.Key
END

Answer 2

You could just use the patientKey as the PatientId (when creating a new patient) and use that Id from then on.

I'd see the insert SP working something like this:

-- Insert A new record with some values
INSERT INTO Patient (UpdateDate, UpdateUser, RecordStatus) 
SELECT @UpdateDate, @UpdateUser, @RecordStatus
     --if patient id is `not null` then just use a place holder value like 1 or 0

--update the new record with the UNIQUE patientKey
UPDATE Patient 
SET PatientId = PatientKey
WHERE PatientKey = SCOPE_IDENTITY() 

SELECT SCOPE_IDENTITY() --to return the new id 

---

You could resolve this issue entirely by having a Patient table that holds 1 record per patient and using a patient_log table to maintain the changes over time. Using before update and delete triggers to insert from Patient to Patient_log. This has a number of benefits including simplified querying to get the current state, which is probably the most common query you perform, faster performance on table seeks because you don't have as many records on table, reduced complexity and better constraining of data, while giving you full auditability and the ability to create a snapshot of what the patient table looks like at a specific point with some clever querying.

If you want me to ellaborate on this approach just email me or leave a comment.

Answer 3

Why do you need a PatientKey column and a PatientId column -- can those not be combined as a single field? Since your table is Patient, it makes sense to have your Primary Key as PatientId which could be your identity column.

Assuming not, look into using a Computed Column for your PatientId.

Here is a good reference guide:

http://msdn.microsoft.com/en-us/library/ms191250(v=sql.105).aspx

Good luck.