A comprehensive laboratory information management system (LIMS) database schema for clinical and diagnostic laboratories. Covers patient intake, test catalog with reference ranges, sample collection and tracking with barcodes, detailed test results with abnormal flagging, and lab technician assignment. Suitable for building hospital lab systems, independent diagnostic centers, or pathology lab management software.
The sample is the physical object the whole system tracks, and the barcode is its identity. samples carries the barcode, the sample_type, who collected it and a status, because from the moment a tube is filled everything that happens is about that tube — not about the patient directly and not about the test yet.
One sample produces many results. test_results keys on both the sample and the test, so a single blood draw can be run for a dozen analytes without drawing blood a dozen times. Each result carries its own status, because one test on the sample can be complete while another is still running.
Technicians appear twice, in different roles. samples.collected_by records who took it, and test_results.technician_id records who ran the analysis — and they are usually different people. Keeping both is what makes a result traceable from the needle to the number.
Keying results directly to the patient is shorter and loses the specimen. Two draws on the same day can give different results, a haemolysed sample invalidates everything run on it, and a mislabelled tube has to be traceable to every number it produced. The sample in the middle is what makes all three answerable, at the cost of one more join on every patient-facing query.
Unlike a clinical record that freezes the range onto each result, this is a laboratory's own system where the range is a property of the method it currently runs. That keeps one definition to maintain and means changing a method updates every pending result's interpretation. The trade is explicit: historical results reinterpret against the current range unless the laboratory versions its tests.
Comparing result_value to a range at read time fails as soon as a value is non-numeric — positive, negative, trace, or a value below the detection limit. A stored flag records the judgement that was made, works for every result type, and lets a list of abnormal results be an indexed filter rather than a parse of every row.
They answer different questions. The sample's status tracks the physical object — collected, received, rejected. The result's status tracks the analysis — pending, complete, verified. One column could not say that a sample has arrived while three of its five tests are still running, which is the normal state of any working laboratory.