Maintaining data integrity when one table has multiple relationships, but only one should be used. (is this bad design?)

Question

I'm working on a database that holds laboratory medical testing orders. The issue I have (maybe) is that I have a table that holds an order number, and a test code. There is also a TestNumber PK. There are two other tables that have a relationship with this table. One of them holds information for testing urine samples, the other holds information for testing blood samples. Depending on the test code in the first table, an additional record should be inserted into one of the related tables. The problem I have is there is nothing stopping a record being inserted into the wrong table for the test code.

Here's a simplified image of the table diagram showing what I'm talking about:

So if a test code is inserted into the LabOrders table and it's sample type is effectively "Blood," then a record should also be inserted into the table BloodTestDetails. But there is nothing stopping a record from also being inserted (or instead, being inserted) into the UrineTestDetails table... Is there a way I can prevent this and maintain data integrity, or is this a bad design?

If it's a bad design, what kind of recommendation would someone make to hold this type of data?

Answer 1

I've worked with six or seven different laboratory information management systems (LIMS) over my years in IT.

The general structure those systems have used tends to be something like:

Accessions (This is where the case begins.)
AccessionKey PK
AccessionDate
CreatedBy
{other columns as needed}

Patients (This holds information about the case's patient)
PatientName
PatientBirthDate
{other columns, including foreign keys to the address entries, etc.}

AccessionPatients (links an existing patient to a new case)
AccessionPatientKey PK
AccessionKey FK
PatientKey FK
{etc.}

Specimens (this holds information about any specimens captured from the patient)
SpecimenKey PK
AccessPatientKey FK
SpecimenTypeKey FK CollectionDate
(etc)

SpecimenTypes (this holds information on the different kinds of specimens)
SpecimenTypeKey PK
SpecimenTypeName
SpecimenContainerTypeKey FK (link to a container type table OR just a text field for EDTA container, or specimen cup, or blood tube or...)
{etc}

SpecimenTestRequests (This holds what tests have been ordered against what specimens)
SpecimenTestRequestKey PK
SpecimenKey FK (the specimen that this test was requested against.)
TestKey FK (The test that was requested.) RequestDate
{etc}

Tests (this stores information about the tests the lab can perform)
TestKey PK
TestName
CreatedDate
EndDate
TestFee
{etc}

TestResultTypes (This tracks the kinds of results a test can have) TestResultTypesKey PK
TestKey FK
TestResultName (display name for this answer in this test) UnitsKey FK (link to a units of measure table) TestResultDataType (free text, dropdown list name, numeric, etc.)
TestResultListName (FK to a lists table or NULL)
DisplayOrder (numeric, to set where in the results this appears)
{etc.}

TestResultLists (this holds valid list entries for drop-down results)
TestResultListKey PK
TestResultListName (used in TestResultTypes)
TestResultListValue (what shows in the dropdown list)
(Etc.)

TestResults (This is where actual results are stored)
TestResultKey PK
SpecimenTestRequestKey FK (what test was requested for what case?)
TestResultTypeKey FK (what answer is this the result for?)
TestResultAnswer (long text that holds the actual result)
EnteredDate
EnteredBy FK (the user who entered the result)
ReleasedBy FK (the user who released the results)
ReleasedDate
(etc)

Units (Units of measure table for values like mg or mg/dL)
UnitsKey PK
UnitsText
(etc)

There are usually several other tables wrapped up in a LIMS -- history tables to track changes over time (audit trails), user tables, permissions tables, address tables, sometimes fee schedule tables, diagnoses tables, and so on. I'm looking through the schema for one of the LIMS I've supported and it has 457 tables. Another has 465 tables.

Anyway, this creates a case or accession, connects a patient to that case (if your system has multiple patients on 1 case, then you'll need a linking table in between). Then one or more specimens are assigned to the case with specimen types. Next, tests are requested for each specimen. Each test can have 1 or more result values. Then there's a results table to provide the results for each test.

A full LIMS would also have reference ranges (your Blood Glucose was 100. It should be between 80 and 120, so you are within normal ranges). It might also have the idea of Panels (a group of tests performed together, like a CBC panel), which can be defined and ordered as a group rather than as individual tests...)

But hopefully this helps.

Answer 2

The easiest way to enforce this with your current data model is to create a function that validates the TestCode for a supplied TestNumber and use that in a CHECK CONSTRAINT on your BloodTestDetails and UrineTestDetails tables. Example below.

Setup:

CREATE TABLE TestCodes (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    TestType NVARCHAR(255) NOT NULL
)
GO
INSERT INTO TestCodes (TestType)
VALUES ('Urine'), ('Blood')
GO

CREATE TABLE LabOrders (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    TestCode INT NULL,
    CONSTRAINT FK_LabOrder_TestCode FOREIGN KEY (TestCode)
    REFERENCES TestCodes (ID)
    ON UPDATE SET NULL
    ON DELETE SET NULL
)
GO

CREATE FUNCTION LabOrderTestCode (@LabOrderID INT)
RETURNS NVARCHAR(255)
AS
BEGIN
    DECLARE @RetCode NVARCHAR(255)

    SELECT @RetCode = tc.TestType 
    FROM LabOrders lo
    INNER JOIN TestCodes tc ON tc.ID = lo.TestCode
    WHERE lo.ID = @LabOrderID

    RETURN @RetCode
END
GO

CREATE TABLE BloodSample (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    LabOrder INT NOT NULL,
    CONSTRAINT FK_BloodSample_LabOrder FOREIGN KEY (LabOrder)
    REFERENCES LabOrders (ID)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
    CONSTRAINT CK_BloodSample_TestCode CHECK (dbo.LabOrderTestCode(LabOrder) = 'Blood')
)
GO

CREATE TABLE UrineSample (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    LabOrder INT NOT NULL,
    CONSTRAINT FK_UrineSample_LabOrder FOREIGN KEY (LabOrder)
    REFERENCES LabOrders (ID)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
    CONSTRAINT CK_UrineSample_TestCode CHECK (dbo.LabOrderTestCode(LabOrder) = 'Urine')
)
GO

Test:

-- Insert a Urine Sample Lab Order -- Should Succeed
BEGIN TRANSACTION
BEGIN TRY
    CREATE TABLE #Inserted (ID INT)

    INSERT INTO LabOrders (TestCode) OUTPUT INSERTED.ID INTO #Inserted (ID)
    VALUES (1)

    INSERT INTO UrineSample (LabOrder)
    SELECT ID FROM #Inserted

    COMMIT

    DROP TABLE #Inserted
END TRY
BEGIN CATCH
    ROLLBACK
    SELECT 'Rolling back'
END CATCH

-- Insert a Blood Sample Lab Order -- Should Succeed
BEGIN TRANSACTION
BEGIN TRY
    CREATE TABLE #Inserted (ID INT)

    INSERT INTO LabOrders (TestCode) OUTPUT INSERTED.ID INTO #Inserted (ID)
    VALUES (2)

    INSERT INTO BloodSample (LabOrder)
    SELECT ID FROM #Inserted

    COMMIT

    DROP TABLE #Inserted
END TRY
BEGIN CATCH
    ROLLBACK
    SELECT 'Rolling back'
END CATCH

-- Insert a Blood Sample Lab Order -- Should Fail due to TestCode violation
BEGIN TRANSACTION
BEGIN TRY
    CREATE TABLE #Inserted (ID INT)

    INSERT INTO LabOrders (TestCode) OUTPUT INSERTED.ID INTO #Inserted (ID)
    VALUES (1) -- This is the TestCode for Urine Samples not Blood Samples

    INSERT INTO BloodSample (LabOrder)
    SELECT ID FROM #Inserted -- Will fail due to TestCode = 1. Check Constraint will block.

    COMMIT

    DROP TABLE #Inserted
END TRY
BEGIN CATCH
    ROLLBACK
    SELECT 'Rolling back'
END CATCH

Answer 3

I work with system that stores lab results (not medical). I believe your design will be restrictive.

IMO you should have a generic table with your samples. This means that in the future you could add other sample types without having to add more tables.

I would think A doctor orders tests. 1 or more samples are taken. Each sample could have 1 or more tests. An order for a test and a test result should be stored seperately.

In the field i'm in, samples can sometimes be retested. Although that may be a very rare circumstance its still worth allowing for it.