See 'Almost Decided' below
NOTE: This is a continuation and simplification of Optional Database Entities.
I am designing a new laboratory database that tests a wide variety of tests on a wide variety of sample types.
Main Entities:
Each must have exactly one parent (except REQ) and at least one child (except MEA).
Request REQ - the form Sample SAM - the materials on the form to be tested Test TST - the procedures to be performed on the sample (Trial ** TRI - instance of duplicate methods for statistics) Measurement MEA - a single measured number ** A Trial is optional. (see below)
Optional Trial Explanation
Many tests are a simple procedure with a few measurements. For example, "Add 10 mL 15% of KNO3 to sample then obtain density and pH."
However, some tests call for the same procedure to be performed on distinct portions of a sample. Lets use ballistics testing as an example. The requester might ask for the average exit velocity and accuracy for these 20 bullets. The sample is the set of 20 bullets. The test is "collect exit velocity and accuracy." The trials are the 20 individual shots being fired. The measurements are exit velocity and accuracy for each shot (trial).
QUESTION
How should I model the entities Test, Trial, and Measurement, since the Trial entity is optional?
Option 1: Use a "blank" trial entity as a placeholder if not needed.
Good: Parent entity always the same.
Bad: Trial entries exist even when not needed.
Option 2: Roll Trial into the Test table as a sub-test. A measurement would then always have a test as a parent.
Good: Single parent type for measurement (Test)
Bad: Multiple parent type for Test: Sample or Test
Option 3: A measurement still has one parent, but the parent could be either a test or a trial.
Good: Single parent type for Test (and Event when required)
Bad: Multiple parent type for Measurement: Test or Trial
Option 4: Trial as sub-entity. Measurement has required test_id and optional trial_num. Trial has a PK of (test_id, trial_num).
Good: No multiple parent type.
Bad: Not sure
Option X: Any other option not already mentioned.
Almost Decided: I now believe Option 4 (Trial as sub-entity) is the best. The following are the basic rules for Option 4.
- A measurement always belongs to a test.
- A trial only exists when needed.
- Trial_num is set when multiple trials exist under a set.
- Otherwise trial_num is null to indicate a trial is not needed.
Simple ER Diagram
-----------------
REQ <- SAM <- TST <- MEA
^ |
| |
|-(TRI)<-|
Table Keys
----------
Table | PK | FK
------+-----------------+----------------
REQ | REQ_id |
SAM | SAM_id | REQ.PK
TST | TST_id | SAM.PK
(TRI | TST_id, TRI_num | TST.PK )
MEA | MEA_id | TST.PK, TRI.PK*
* TRI.PK is null if trial entity is not needed.
Please offer any thoughts on why it is a good or bad option.