Database Design – Maintaining Data Integrity with Multiple Relationships

database-designsql server

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?

Best Answer

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.

Related Solutions

Database design with different user roles connected by different relationships to one organizaiton table

What are the pros and cons of above schema?

The major con that I see is that freelancer and employee have the same fields, but are different tables. The difference is what primary_key is composed of, but I wonder if you could normalize this as

emplpoyee
{
    user_id,
    employee_type, (could be "FREELANCER" or "FULLTIMEEMPLOYEE")
    organization_id,
    Primary_key
}

For primary_key, you might need some application logic to create a string based on user_id and employee_type, and optionally include tha value of organization_id if the employee is a freelancer.

You could even go a step further and have a table employee_type:

employee_type
{
    id,
    description
}

and then reference that ID in employee.employee_type. This would deal with your problem of adding new tables every time there is a new type of user.

Also, Have you considered merging employee with user? This would simplify your schema even more, but you would lose the ability to have a user_id associated with multiple employees, which I think is important for this case.

PostgreSQL – Combining Multiple ‘Type’ Tables When Normalizing

I would go for separate tables for each type. With so few rows performance will not be a consideration either way. Individual tables will only have a page or two each. Similarly for the combined table.

Either way you will have to join a "type" table to complete your views. Inserts are infrequent at this level of abstraction so locking is unlikely to be an issue either way.

For me, though, having different tables keeps logically distinct items separated, which is a cleaner design.

There will be many different tables which will have a relationship to a type table of some sort. If there are many different type tables the foreign key relationships are self-documenting and enforcable through DRI. With one combined table it is difficult to use DRI to say "Venue.TypeID comes from Type.TypeID but only if the Type.Category = 'V'", for example. The complementary side of this relationship - "Type.TypeID with Type.Category = 'V' can only be used in the Venue table" - is also not supported in any RDBMS's DRI that I know of.

Best Answer

Related Solutions

Database design with different user roles connected by different relationships to one organizaiton table

PostgreSQL – Combining Multiple ‘Type’ Tables When Normalizing

Related Question