Maintaining data integrity when one table has multiple relationships, but only one should be used. (is this bad design?) 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?
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.