Why Links Matter
A dataset can contain plausible-looking subjects, concentrations, doses, and visit labels yet still describe the wrong records as belonging together. If a sample is linked to the wrong subject, its concentration contributes to that subject's information. If a dose is attached to the wrong occasion, the record no longer represents the intended study grouping. These are not cosmetic data problems: incorrect links can change the analysis dataset and its interpretation.
This tutorial focuses on constructing and checking identifiers and links. It does not determine how dose events are encoded in a longitudinal analysis table. The immediate goal is more fundamental: every record should have a stable identity, and every stated relationship should point to a valid, intended record.
Specify the Keys and Relationships First
A useful starting structure has four record types: subjects, occasions, samples, and doses. The subject table has one row per participant. An occasion is identified within a subject, so a natural occasion key is the pair \((\text{subject\_id},\text{occasion\_id})\). The sample and dose tables each have one row per sample or dose record and link to both a subject and an occasion.
Identifiers need not be meaningful. For example, a sample ID such as S1042 can identify a sample without encoding its subject, date, or visit. Keeping identity separate from context helps avoid errors when a study label changes. The links themselves should be stored explicitly rather than inferred from row order, file name, or a pattern that happens to hold in one extract.
| Record type | Example key | Required link or relationship |
|---|---|---|
| Subject | subject_id | One record per subject_id |
| Occasion | subject_id + occasion_id | subject_id must identify a subject |
| Sample | sample_id, or subject_id + sample_id | Subject and subject–occasion pair must exist |
| Dose | dose_id, or subject_id + dose_id | Subject and subject–occasion pair must exist |
An ID can be globally unique, or unique only within a stated scope. If sample IDs are unique only within subjects, then the key is the pair \((\text{subject\_id},\text{sample\_id})\); checking sample_id alone would incorrectly flag valid reuse across different subjects, or fail to distinguish records. The same principle applies to dose IDs. The dataset specification must state the scope, and validation must use that same scope.
For the checks below, let \(S\) be the set of valid subject IDs, \(O\) the set of valid subject–occasion pairs, \(P\) the set of sample records, and \(D\) the set of dose records. Let \(\operatorname{subj}(r)\) and \(\operatorname{occ}(r)\) return the subject and occasion assigned to record \(r\). Subject and occasion integrity requires:
These conditions check both record types: every sample and every dose must refer to a real subject and to an allowed subject–occasion pair. They do not, by themselves, establish that an ID is unique or that a link is scientifically correct. Those require separate checks and, where necessary, comparison with source records or study conventions.
How to Validate Links
The logic of a referential-integrity check is direct. For each sample or dose, look up its subject in the subject table; then look up the combined subject and occasion in the occasion table. A failed lookup identifies a record whose claimed parent does not exist. The pair lookup matters because an occasion label such as 2 may be valid for one subject but not another.
In database terms, these checks are anti-joins: select sample records with no matching subject, and separately select sample records with no matching subject–occasion pair; repeat both checks for doses. A zero-row result means no failures of that particular link test were found. It does not prove that the records are accurate in every respect: duplicate keys, swapped IDs, or incorrect but internally consistent links can still pass.
State whether each ID is globally unique or unique only within a subject, and identify the exact key columns.
Count records per declared key. Any key appearing more than once requires investigation unless the table's design explicitly allows repeated rows for that key.
Find sample and dose records whose subject_id is absent from the subject table.
Find sample and dose records whose combined subject_id and occasion_id is absent from the allowed occasion table.
Compare suspicious but technically valid links with source records and the study's identifier and occasion conventions. Do not silently repair them by guessing.
Worked Checks
Worked Example: Finding Orphan and Mismatched Records
Consider the following hypothetical tables. Subject IDs are intended to be unique, and occasion IDs are unique only within a subject.
Subjects: S01, S02.
Allowed occasions: (S01, 1), (S01, 2), (S02, 1).
Samples: P101 linked to (S01, 1); P102 linked to (S03, 1); P103 linked to (S02, 2).
Doses: D201 linked to (S01, 2); D202 linked to (S02, 1).
For P102, the subject ID S03 is not in \(S=\{\text{S01},\text{S02}\}\). Thus P102 fails the subject-link check and is an orphan with respect to the subject table. For P103, S02 is a valid subject, but the pair (S02, 2) is not in the allowed-occasion set. P103 therefore passes the subject check and fails the occasion check. Both doses refer to valid subjects and valid subject–occasion pairs.
The two sample failures need not have the same cause. P102 could contain a transcription error or refer to a subject missing from the subject table; P103 could have an incorrect occasion assignment or an incomplete occasion table. The checks locate the inconsistency but do not determine which source is correct.
Worked Example: Detecting Reused Identifiers
In a hypothetical extract, sample_id is specified to be globally unique. The sample records are P301 for S11, P302 for S11, P302 for S12, and P303 for S12. All subject and occasion links are otherwise valid.
Count occurrences of each sample_id. P301 appears once, P302 appears twice, and P303 appears once. Because the declared key is sample_id alone, P302 violates uniqueness. The fact that its two rows refer to different subjects does not make the reuse acceptable under this specification: one globally unique identifier is identifying two records.
Suppose instead the data specification explicitly said sample_id is unique only within subject. Then the relevant key would be \((\text{subject\_id},\text{sample\_id})\). The two keys (S11, P302) and (S12, P302) are distinct, so this reuse would be valid under that alternative design. The apparent conflict is resolved by declaring the key scope before checking, not by choosing whichever interpretation makes the data pass.
Worked Example: Finding an Invalid Subject–Occasion Pair
A hypothetical occasion table contains (S21, A), (S21, B), and (S22, A). A sample record X501 is linked to subject S22 and occasion B; a dose record X601 is linked to subject S21 and occasion B. Assume record IDs are globally unique and both IDs pass their uniqueness checks.
The pair for X501 is (S22, B), which is not in the occasion table. Its subject exists, so this is not an orphan-subject error; it is an invalid subject–occasion link. The pair for X601, (S21, B), does exist, so that dose passes the pair check.
Looking up occasion B by itself would give the wrong conclusion: B is a valid occasion label for S21, but that does not establish that B is valid for S22. The composite lookup uses both identifiers and correctly distinguishes the records. Before changing X501, confirm whether its occasion assignment is wrong or whether (S22, B) was omitted from the occasion table.
Why the Checks Work—and What They Cannot Prove
A foreign-key check is a set-inclusion test. For example, the sample subject check passes precisely when every subject value used by a sample occurs among the valid subject IDs. If even one sample uses a value outside that set, that value is a counterexample to the inclusion. The same reasoning applies to doses and to the subject–occasion pairs. This is why checking only the subject is insufficient: subject membership does not imply that every occasion label is allowed for that subject.
The checks establish structural consistency with the tables and rules supplied. They cannot establish that the supplied rules or links reflect the source data. If two valid subjects' IDs were accidentally swapped across otherwise valid records, every key could exist and every link could pass. A validation report should therefore distinguish structural errors (such as missing keys or duplicates) from suspected content errors that require reconciliation with source records.
| Approach | Strength | Limitation |
|---|---|---|
| Globally unique record IDs | Simple to reference a record without additional context | Requires uniqueness across the full dataset and careful ID generation or assignment |
| Composite keys, such as subject_id + sample_id | Make the scope of local IDs explicit and can accommodate reuse across subjects | Every link must carry all key components; omitting one can create ambiguous matches |
| Infer links from labels, row order, or file organization | May appear convenient in a narrowly controlled extract | Fragile when records are sorted, merged, relabelled, or received in separate files |
Neither globally unique nor composite keys are universally preferable. The important requirements are a documented key definition, consistent use of that definition, and checks that detect violations. Avoid changing an identifier merely to make a duplicate count disappear: first determine whether the duplicate represents an accidental reuse, a valid repeated label under a narrower scope, or a repeated record that needs investigation.
Common Mistakes and Failure Modes
- Checking only whether the subject exists. A valid subject can still be paired with an occasion that is not defined for that subject. Validate the composite subject–occasion pair for both samples and doses.
- Assuming an ID is global. A repeated sample label may be valid if the declared key includes subject_id. Check the documented key, not an unstated assumption.
- Using occasion labels without their subject context. Occasion 1 can occur for many subjects; the key is the pair, not the occasion label alone.
- Treating a passing check as proof of correctness. A wrong link can point to an existing subject and allowed occasion. Structural checks do not replace source reconciliation.
- Silently dropping or relabelling failures. Preserve the original values, document the finding, and resolve it using an authoritative source or a clearly stated rule.
Useful diagnostics include counts of duplicate keys, unmatched subject IDs, unmatched subject–occasion pairs, and records with missing key components. Review the actual records behind each nonzero count. A large batch of failures can indicate a systematic mapping problem, while a single failure may still be consequential. Also check that the occasion table is complete: a valid record can appear orphaned if its parent occasion was never loaded.
Check Your Understanding
Answer each question, then compare with the solutions below.
- The allowed occasion pairs are (S31, 1) and (S32, 1). A dose is linked to (S31, 2). Does it pass the subject check, the occasion check, both, or neither?
- Sample IDs are declared unique within subject, not globally. Two records have keys (S41, A7) and (S42, A7). Is this a duplicate key?
- A sample refers to subject S51 and occasion 3. Both values appear somewhere in the subject and occasion columns, but the pair (S51, 3) is not in the occasion table. Is the link valid?
- What can an unmatched-subject check establish, and what can it not establish?
Solutions. 1. The subject S31 exists, so the subject check passes. The pair (S31, 2) is absent, so the occasion check fails. 2. No. Under the declared composite key, (S41, A7) and (S42, A7) are distinct. 3. No. The subject–occasion pair is the relevant link, and it is absent even though each component appears separately. 4. It establishes that a record's subject ID has no match in the supplied subject table; it does not determine whether the record has a mistyped ID, the subject table is incomplete, or the record is otherwise correctly linked.