A linked records database earns its keep on the day somebody asks you to prove something. An auditor, a sponsor, or a colleague who needs to know which instrument produced a result that now looks wrong. The question is rarely complicated. How easily you answer it depends almost entirely on decisions someone took months earlier, when they worked out how the information would sit together.
That is an uncomfortable thing to discover under time pressure.
What is a linked records database?
A linked records database holds each piece of information on one record and connects related records to it. Nothing repeats. A test result points to its sample, the sample points to its study, and no detail travels between them as a copy.
The system stores that connection, which is the part that matters. No code typed into a field implies it, and nobody reconstructs it later by matching names. The system knows this result belongs to that sample as concretely as it knows the result's value.
The question that exposes your structure
Imagine a request arriving without warning. Show me every test performed on this sample, who ran each one, on which instrument, and confirm each instrument held a valid calibration that day.
Nothing in that request is unreasonable, and in a well-run laboratory every one of those facts exists somewhere. The difficulty almost never lies in missing information. It lies in the walk from one end of the chain to the other, and that walk only works if someone stored the links.
Where the connections exist, the answer takes a search, a report and a few minutes. Where they do not, someone assembles it by hand, cross-referencing identifiers across eighteen months of work. Both eventually produce an answer. Only one produces an answer you would enjoy defending. And the gap stays invisible day to day, because nobody walks the chain until something external asks them to.
Why one thing having many others is the difficult part
Operational information in regulated work is almost never a single flat list, and this is the source of most structural difficulty.
One study runs at several sites. Each site enrols many participants, and each participant produces many samples. Every sample then undergoes many tests, run by different analysts on different instruments. All of those relationships point the same way: one parent, many children.
A single record type holds one of those layers cleanly. Asked to carry two, it compromises, usually in one of two ways. Either the parent details repeat on every child record, so a protocol number needs correcting in hundreds of places. Or a second record type appears, joined to the first by an identifier someone enters by hand. That works exactly as well as the person's attention on the day.
Neither reflects poor judgment. Both follow from asking a one-layer structure to carry two. It happens quietly because each step seems small at the time.
How does a linked records database avoid duplicating information?
By storing the relationship itself rather than copying information across records. The study exists once. Each sample references it, each test references its sample, and the study details live in one place however many records depend on them.
The practical consequences deserve specifics.
- Corrections stay contained. Change a protocol number on the study record, and every connected sample and result reflects it at once.
- Counts and totals come from the structure. How many tests did this sample undergo? How many samples has this site produced this quarter? The system answers from the connections rather than asking somebody to tally them.
- Calculations cross layers. An average across every reading in a run comes from the relationship, rather than from assembling the parts first.
- The chain holds up under scrutiny. Result to sample to participant to study, with every step stored, so producing it means retrieval rather than reconstruction.
That last point separates evidence from an argument. A chain the system stored reads the same today and next year, long after the people who built it have moved on. A chain somebody reassembled by hand is a claim about what they believe happened. It invites an awkward follow-up about how you know.
Research data guidance reaches the same conclusion from another direction. The FAIR principles for scientific data put provenance at the centre of good practice, and guidance on applying them stresses that relationships between data should be explicit rather than inferred. Different vocabulary, same point.
When information belongs on the record itself
Not every repeating thing deserves records of its own. A reagent list on a method, or readings taken during a single run, can live on the record they belong to, in a section that repeats as often as the work requires.
One question usually settles it. Would you ever search these entries on their own, report on them across parents, or give them a status somebody tracks? If so, they count as records in their own right. If they only carry meaning alongside their parent, keeping them on the record is tidier.
Getting this roughly right beats getting it perfect. Separating a few things unnecessarily is mildly inconvenient. Burying genuine records inside a parent means you cannot answer questions about them at all.
Designing for the question you will eventually be asked
Half an hour with a pen, before anyone builds anything, prevents most of the difficulty above. The exercise is less technical than it sounds and needs nobody from IT.
Start by listing the things your work genuinely involves, in whatever words your team already uses. Studies, sites, participants, samples, tests, instruments, analysts. Then take each pair and ask one question: can one of these have several of those? A sample can have several tests, so that is a connection. An analyst runs many tests, so that is another.
That conversation produces your structure. Each thing on the list becomes a record type, and each connection becomes a stored link. Anything repeating only within a parent stays on the record. The whole exercise costs less than one afternoon of the cross-referencing it prevents.
One habit is worth adopting alongside it. When somebody asks for a new field, ask whether there could ever be more than one of the thing they want to record. If yes, you have found a connection rather than a field. Handling it now costs far less than handling it in a year, which I take up on Thursday in a piece about how the second layer tends to arrive unannounced.
Our overview of data normalization covers the wider principle, and our piece on centralized operational databases looks at how it holds up as an organization grows. For a research-specific view, our guide to research data management is the natural companion. And if you would like to work through your own structure with someone who has done it before, talk to an expert.
