thebench.bio #1 Ecosystem for the Bio-Tech Industry
All SQL questions

Question 19 of 20 Advanced Practical

How would you design a schema that preserves sample identity, aliquot lineage and repeated measurements?

Short answer

Separate subjects, samples, aliquots and measurements into their own tables linked by foreign keys. Give aliquots a nullable parent_aliquot_id so splits form a tree, and give each measurement its aliquot, assay, run and replicate number under a unique constraint, so repeats are kept rather than overwritten.

Why

Lab data has a natural hierarchy. A subject gives samples, a sample is split into aliquots, aliquots can be split again, and each aliquot can be measured many times in many runs. Storing it as one wide table loses that structure and invites duplicated, inconsistent copies.

A normalized design puts each level in its own table, enforces links with foreign keys and lets constraints carry the rules so every program that writes data obeys them. The goal is that any measurement can be traced back to the tube, sample and subject it came from, and that a rerun adds evidence instead of replacing it.

How to work it through

  1. List the real-world entities and how many of each relate to the next, for example one sample to many aliquots.
  2. Create a table per entity with a primary key and a unique constraint on the external identifier printed on the tube.
  3. Model aliquot splits with a self-referencing foreign key, parent_aliquot_id, that is NULL for the first split from a sample.
  4. Store each measurement as its own row with aliquot, assay, run, replicate, value, unit and timestamp, and add a unique constraint across aliquot, assay, run and replicate.
  5. Add check constraints for rules such as positive volumes, and record corrections as new rows with a reason rather than updates in place.
  6. Query lineage with WITH RECURSIVE and test the design with realistic edge cases before loading production data.

An example

Plasma sample P-0042 from subject 1007 is split into aliquots A1 and A2. A2 is later split into A2a and A2b. A2b is measured for IL-6 in run R31 in triplicate, and again in run R35 after a failed control.

The measurements table holds six rows for A2b, each with its run and replicate number. A recursive query starting from P-0042 returns all four aliquots, and a join to measurements shows that every IL-6 value traces back to subject 1007 through one path.

Where the answer stops holding

Strict normalization makes some analytical queries longer, so a reporting view or a derived wide table may be added, but generated from the normalized source. Recursive lineage queries can be slow on very deep trees without indexes on parent_aliquot_id. The schema records lineage only as well as people scan tubes, so barcode workflows matter as much as constraints.

Pooled samples, where several parents form one child, need a separate link table rather than a single parent column.

A strong answer covers

  • One table per entity with foreign keys
  • Self-referencing parent for aliquot splits
  • Measurement rows keyed by aliquot, assay, run, replicate
  • Unique constraint on the tube identifier
  • Corrections as new rows, lineage via WITH RECURSIVE

Common mistakes

  • Storing repeated measurements as extra columns such as value_1 and value_2, which breaks when a fourth repeat arrives
  • Overwriting a measurement on rerun so the failed result disappears from the record
  • Using a single parent column when pooled samples have several parents