Why a Longitudinal Record Needs More Than a Subject Identifier
A subject identifier answers whose data a record describes. It does not identify which record belongs to that subject. A longitudinal dataset can contain many records for one subject, including records at different times and multiple records at the same time. Treating repeated subject identifiers as duplicates would discard information; treating every row as unique without checking how records are distinguished can conceal actual collisions.
A record key is a set of fields whose combined values identify one record at the dataset’s declared grain. For longitudinal records, a useful starting point is the combination of subject, time, event type, and measurement identifier. Whether that combination is sufficient depends on what one row represents. The record grain must therefore be declared before a key is assessed: the same values may identify a record in one dataset but be insufficient in another.
Here, a measurement identifier may represent the measured variable or endpoint, such as a particular analyte, and may also need to distinguish replicate measurements if the declared grain includes more than one record for the same subject, time, event type, and measurement. The names and meanings of fields should be explicit: a value such as “1” is not useful as a key component unless its interpretation and scope are clear.
The subject identifier should distinguish subjects within the dataset scope. If identifiers are only unique within a site or study, that scope must be included in the key or otherwise guaranteed by the data specification. Likewise, time must be recorded in a form that distinguishes the record times at the required resolution. These are design conditions, not properties guaranteed merely by joining fields together.
When Is the Composite Key Valid?
For a dataset with records \(r_1,\ldots,r_n\), the proposed key is valid as a unique identifier when each pair of distinct records differs in at least one key component. This condition can be stated directly:
This is a testable uniqueness condition, but passing the test does not prove the key represents the intended grain. For example, adding a row number makes every row unique, yet may not explain what distinguishes two otherwise identical measurement records. Conversely, a key that fails the condition may reveal either a genuine data problem or an incomplete key design.
A key should also be stable enough for its intended use. A combination based on meaningful, consistently represented fields can be checked across file transfers and updates. A key assembled from ambiguous text, inconsistently rounded times, or values that change during data cleaning may identify records inconsistently. A key is thus both a technical uniqueness rule and a specification of what the dataset considers one record.
Worked Example: Distinguishing Measurements at the Same Time
Consider a hypothetical dataset whose declared grain is one record per subject, recorded time in hours, event type, and measured analyte. The table contains these records:
| Subject ID | Time (h) | Event type | Measurement ID | Result |
|---|---|---|---|---|
| P014 | 2.0 | OBS | ANALYTE_A | 4.8 |
| P014 | 2.0 | OBS | ANALYTE_B | 1.3 |
| P014 | 4.0 | OBS | ANALYTE_A | 3.9 |
| P021 | 2.0 | OBS | ANALYTE_A | 5.1 |
The subject identifier alone is not unique: P014 occurs three times. Subject and time are also insufficient: the first two records share P014 and 2.0 h. Adding event type still leaves those two records tied, because both are OBS records. The measurement identifier distinguishes them.
The four proposed keys are (P014, 2.0, OBS, ANALYTE_A), (P014, 2.0, OBS, ANALYTE_B), (P014, 4.0, OBS, ANALYTE_A), and (P021, 2.0, OBS, ANALYTE_A). Each tuple differs from every other tuple in at least one component. The composite key therefore meets the uniqueness condition for these four records and the stated grain. The two records at 2.0 h for P014 are not duplicates: they describe different measurements.
Check the Grain Before Adding More Fields
A composite key that fails a uniqueness check calls for investigation rather than automatic row deletion. Ask what the repeated key values mean under the declared grain. The records may be accidental duplicates, or the key may omit a field that distinguishes legitimate records. For example, if two replicate measurements are intentionally stored as separate rows but the key contains only subject, time, event type, and analyte, those replicates collide. The grain then requires an additional replicate or record identifier.
Avoid adding fields indiscriminately. A result value might make two rows appear unique, but measurements can be equal by chance; result values can also be corrected or rounded. Using a variable that is an outcome of the record rather than a stable identifier can make the key fragile. Add a component because it expresses a real distinction in the declared record grain, not simply because it removes a warning.
Worked Example: A Collision Created by Rounded Time
In a hypothetical dataset, two records for subject P008 are distinct measurements of the same analyte and event type. Their recorded times, in hours, are 1.234 and 1.236. Suppose a proposed key stores time rounded to two decimal places.
The first time becomes 1.23 h and the second becomes 1.24 h under ordinary rounding to two decimal places; in this case, the rounded values remain distinct. But if the source values are 1.231 and 1.234, both round to 1.23 h. For those records, the proposed key tuples become identical: (P008, 1.23, OBS, ANALYTE_A). The key fails to distinguish them, even though their source times differ.
The arithmetic shows the risk: rounding 1.231 h to two decimal places gives 1.23 h, and rounding 1.234 h to two decimal places also gives 1.23 h. The correction is not to claim those records are duplicates. Instead, retain a time representation with sufficient precision for the declared grain, or add a stable identifier that distinguishes the records if the grain permits separate records at that time resolution. A time precision that is too coarse creates a key collision; a precision that is finer than the data support can also give a false impression of meaningful distinction.
Representing Nonapplicable Components
A field may not apply to every event type. If a key includes a measurement identifier, a record without a measurement should not be assigned an ambiguous blank value without a documented rule. Missingness can make records impossible to compare consistently: software may treat blank values, null values, and text such as “NA” differently. A robust specification defines whether the component is required, when it applies, and how nonapplicable values are represented.
One option is a controlled, explicit sentinel such as “NOT_APPLICABLE,” provided it cannot be confused with a real measurement identifier. Another is to define different key rules for different record classes. Either choice should preserve the key’s meaning and make uniqueness checks reproducible. A sentinel should not be used to conceal genuinely unknown information: “unknown measurement” and “no measurement applies” are different states.
Worked Example: Distinguishing Record Types With a Defined Sentinel
A hypothetical file contains two observation records and one nonmeasurement event for subject P003 at 6.0 h. The dataset specification says the measurement identifier applies to observation records and requires the explicit value NOT_APPLICABLE for other event types.
| Subject ID | Time (h) | Event type | Measurement ID |
|---|---|---|---|
| P003 | 6.0 | OBS | ANALYTE_A |
| P003 | 6.0 | OBS | ANALYTE_B |
| P003 | 6.0 | OTHER | NOT_APPLICABLE |
The key tuples are (P003, 6.0, OBS, ANALYTE_A), (P003, 6.0, OBS, ANALYTE_B), and (P003, 6.0, OTHER, NOT_APPLICABLE). Each differs from the others, so the key is unique in this example. The event type distinguishes the nonmeasurement record from the observations, while the measurement identifiers distinguish the two observations.
This result depends on the stated convention. If NOT_APPLICABLE were a possible real measurement identifier, or if blank values were used inconsistently, the rule would not be safe. The sentinel is an explicit representation choice, not a general property of composite keys.
Construct and Validate a Key Systematically
A practical key review has four steps. Each step links the technical check to the meaning of a record, so that a uniqueness result is interpretable rather than merely computational.
State what one row represents, including whether separate measurements or replicates at the same subject and time are allowed.
Start with subject, time, event type, and measurement identifier. Add a component only when the grain requires it; include any study or site scope needed to make subject identifiers distinct.
Define time precision, identifier formatting, and the treatment of nonapplicable or unknown components. Use the same rules wherever the data are produced or checked.
Count records per key tuple. For every tuple occurring more than once, inspect the records and decide whether the cause is duplication, an omitted grain component, or inconsistent representation.
Why Composite Keys Can Fail
Several diagnostics are useful because different failure modes can look alike in a simple duplicate-key report. First, count how often each key tuple occurs; a count greater than one identifies a collision requiring review. Second, check whether any required component is missing or invalid. A key with an absent subject identifier or an undefined event type may be unique in a narrow computational sense but not a valid record identifier. Third, check the key across the full intended scope. A subject code reused in two studies may appear unique within each study but collide when files are combined.
Also inspect repeated full rows and repeated key tuples separately. Identical rows may indicate a copied record, but they may also represent legitimate repeated measurements if the data specification permits them and does not yet contain a distinguishing identifier. Different rows with the same key are not automatically errors either: they may reveal an omitted key component or a value that was altered during formatting. The correct response is to resolve the discrepancy against the declared grain and source records, not to delete rows solely because a software check found a repetition.
Concatenating fields into one text string can introduce ambiguity. Without separators, the components (12, 3) and (1, 23) both become “123.” Separators alone are not always sufficient if a component can itself contain the separator. Prefer a structured tuple or an unambiguous encoding, such as length-prefixed components, and preserve the original component fields for inspection. A hash of the tuple can be convenient for storage, but it does not replace a clear specification of the components and encoding.
Alternatives and Limitations
| Approach | Strength | Limitation |
|---|---|---|
| Meaningful composite key | Expresses why records differ and can be checked against the data specification. | Can become incomplete when the grain includes unrepresented distinctions; representation rules must be consistent. |
| Surrogate record identifier | Provides a compact identifier that can remain stable even when descriptive fields change, if assigned and maintained correctly. | Does not by itself explain the record grain or detect two records that should not both exist. |
| Row number assigned during processing | Easy to create and unique within one particular file version. | May change when rows are added or rearranged; it is not a reliable identifier across versions unless explicitly managed. |
A surrogate identifier can be useful alongside a composite key: one can identify records operationally, while the composite components support checks of the data structure. Neither approach repairs an unclear grain. Composite keys also have limits when source data lack enough information to distinguish legitimate records. In that situation, the correct resolution may require clarification from the data provider or a revised data specification; no key formula can recover distinctions that were never recorded.
Common Mistake
Wrong reasoning: “The subject and time repeat, so one of these rows must be a duplicate.” This confuses a subject-time pair with a record key. A subject can have different event types or measurements at the same recorded time, and the repeated values may be expected at the declared grain.
Correction: Compare the full tuple defined by the record grain, including event type and measurement identifier where applicable. If the full tuple repeats, inspect the records and specification. A repeated key signals a collision to investigate; it does not, by itself, establish that either record is invalid.
Check Your Understanding
Answer each question using the stated record grain. Full solutions follow the questions.
- A dataset has one row per subject, time, event type, and analyte. Two records share subject, time, and event type but have different analyte identifiers. Are they duplicates under this grain?
- Two different subjects in separate studies both have the subject identifier S12. What should be considered when defining a key across the combined file?
- A record key is subject, time rounded to one decimal place, event type, and analyte. Explain one way this key could fail even if the source time values differ.
- Two rows have identical values for every field in the proposed key but different measurement results. What conclusion is justified, and what should be checked next?
Solutions. (1) No. The analyte identifier differs, so the composite tuples differ; both records can be valid at the declared grain. (2) The study identifier or another scope field must be included, or there must be a documented guarantee that subject identifiers are unique across studies. Without one of these conditions, the combined-file key may collide. (3) Distinct source times can round to the same tenth of an hour. For example, 2.31 h and 2.34 h both round to 2.3 h, so the time component no longer distinguishes the records; retain sufficient precision or specify another legitimate distinguishing component. (4) The key is not unique, so the proposed key fails for these rows. The difference in results does not establish which row is correct or whether one is a duplicate. Check the declared grain, source records, and whether a meaningful component such as a replicate identifier is missing before deciding how to resolve the collision.