E2 · Publication Volume 24

Identifiers and Identity

natural and business keys, surrogate identifiers, merges and aliases

Learning objectives

  • Explain why natural and business keys, surrogate identifiers, merges and aliases require explicit semantic modelling.
  • Design identities, relations and constraints that preserve identifiers and identity across exchange.
  • Separate hard release gates from diagnostic metrics and interpretation choices.
  • Produce an identity registry with aliases, reviewed merges and invariant tests from synthetic evidence.

The lesson is complete only when the learner can defend both the model and the release decision. A neat schema without evidence, tests or declared limitations is an unverified design. The assessed artefact must make assumptions visible and distinguish source assertions from derived conclusions.

Decision context

Choose an identifier strategy based on scope, uniqueness authority, persistence and exposure. The identifier must remain stable when a label is corrected and must not be reassigned. Identity resolution should produce an auditable decision with evidence, not an irreversible string-cleaning shortcut.

Start with a decision record: name the intended use, the evidence required, the consequence of error, the accepted uncertainty and the role authorised to accept residual risk. Then ask whether the proposed model can answer the decision question without relying on filename conventions, row order, undocumented defaults or someone’s memory. This prevents technology selection from concealing a missing semantic requirement.

The same record may be fit for one use and unfit for another. A rapid exploratory view can tolerate conditions that a released exchange package cannot. Fitness is therefore stated against a use, contract version and quality gate rather than attached permanently to the data.

Core concept

Identity answers whether two records refer to the same thing across time and systems. Natural and business keys are useful matching evidence but often change, collide or reveal internal conventions. A surrogate identifier provides durable reference, while aliases and merge records preserve how external labels map to that identity.

The working scope is natural and business keys, surrogate identifiers, merges and aliases. For each item in that scope, distinguish the thing itself, the label used by a source, the claim made about it and the record that carries the claim. Identity is not a display name; a value is not its unit; an observation is not a model; current is not the same as valid. These distinctions create explicit places for correction, uncertainty and competing interpretations.

A good semantic design can be explained as a set of sentences before it is encoded. Each sentence identifies a subject, a property or relationship, an object or result, and the context under which the claim holds. Physical tables and files are then projections of those sentences, not their source of meaning.

Semantic model

Maintain an identity registry with durable identifier, entity type, lifecycle state, canonical display label and creation event. Store aliases with namespace, source, validity interval and normalised comparison value. A merge creates a directed relation from retired identities to the surviving identity and records reviewer, evidence and effective time.

Test every proposed record against seven questions: What has identity? What type is it? Which property or relationship is asserted? Which spatial and temporal context applies? Which state or qualifier modifies the assertion? Which evidence supports it? Which version and activity produced the stored representation? Missing answers become explicit contract gaps.

Normalisation is used to separate independent facts, not to maximise the number of tables. A compact nested object can be semantically sound if the same identities, constraints and provenance remain explicit. Conversely, a highly normalised database can still be ambiguous when relationships and units exist only in documentation or application code.

Constraints and invariants

| Invariant | Executable or review test | | --- | --- | | Identifiers are opaque | Consumers do not infer entity properties from identifier characters. | | No reassignment | A retired identifier never points to a different entity. | | Aliases are scoped | Every alias declares source namespace and validity. | | Merge chains terminate | Detect cycles and resolve every retired identity to one active survivor. |

An invariant is a condition that must remain true across storage, export, correction and reprocessing. Implement it as close to the authoritative boundary as practical and repeat the check at exchange boundaries. Record rule identifier, version, severity, evaluated scope, observed value and outcome so a failure can be reproduced.

Hard gates protect identity, semantic validity, required provenance and authorised use. Diagnostic checks reveal unusual values or patterns but require interpretation. Never convert a diagnostic threshold into deletion or correction without a reviewed rule and preserved source evidence.

Quantitative reasoning

Track collision rate R_c = n_c / n_i, unresolved-alias rate R_u = n_u / n_a and merge-chain depth. These metrics reveal risk but do not automate identity. Two records with a perfect text match may still represent different physical objects, while differently spelled records may share one identity.

Every reported ratio states its numerator, denominator, exclusions and evaluation time. Stratify results by source, entity type, contract version or processing run where aggregation could hide a local failure. Counts accompany percentages so a seemingly large change based on a tiny denominator remains visible.

Precision is part of meaning. Do not add decimal places merely because a storage type permits them, and do not round identity, interval or coordinate fields without a declared tolerance and test. Quantitative summaries support a release decision; they do not replace semantic review.

Evidence and uncertainty

Use independent attributes for candidate matching: entity type, spatial position, parent entity, dates and supporting documents. Record the matching evidence and contradiction evidence. Uncertain candidates stay unmerged with a review state; uncertainty must not be hidden behind a deterministic identifier.

Build an evidence packet containing preserved source reference, acquisition or assertion context, applicable method, validation results, reviewer decision and links to derivatives. Classify uncertainty as observational, semantic, structural, parametric or policy-related where that distinction changes treatment. “Unknown” is a valid state when the evidence cannot justify a stronger claim.

Contradictory evidence remains available. The model may select one current assertion, but the reason, competing assertion and effective time are retained. This makes later reinterpretation possible without pretending the earlier evidence never existed.

Interfaces and storage

Expose durable identifiers plus the identifier namespace and entity type. When sharing aliases, distinguish canonical label, source label and retired label. Consumers should follow explicit replacement links and cache the registry version used, rather than rewriting identifiers during every import.

Design an interface from the logical contract outward. Specify identifiers, types, cardinalities, units, value states, coordinate and time references, version negotiation, validation behaviour and structured errors before choosing a serialisation. The physical representation then declares its mapping to those logical elements.

Storage optimisation may partition, compress, index or cache data, but it must not change identity or silently remove context. A derived representation points to immutable inputs and a processing manifest. A cache carries freshness and contract-version information and is never treated as the only evidence copy.

Governance and access

Creation can be automated if uniqueness is guaranteed, but merge and split operations require controlled review because they redirect evidence. A split is not the inverse of a merge: it must state which observations move to each new or restored identity and why.

Governance is expressed through named roles, review states and versioned decisions, not through references to a particular organisation. Define who may propose, validate, approve, supersede and withdraw each governed resource. The audit trail records the role and event while avoiding unnecessary personal data.

Apply least-necessary access to source evidence and derivatives. Access controls must not erase identifiers, lineage or quality metadata needed to understand an authorised release. When policy is unresolved, quarantine the output with a precise reason and escalation route.

Integration checkpoint

Candidate labels resolve to durable identities through evidence
Candidate labels resolve to durable identities through evidence

The diagram summarises the control flow for this lesson. Read it from source evidence through semantic structure and validation to a decision-ready artefact. Each arrow should correspond to a declared relationship or transformation; each boundary should have a contract; each released node should have an identity, version and provenance pointer.

Integrate the lesson by adding an identity registry with aliases, reviewed merges and invariant tests to the evolving synthetic data package. Verify that earlier artefacts still resolve and that the new model does not overwrite observations, identifiers, values or versions introduced in previous lessons. Record every changed assumption.

Synthetic worked example

A synthetic registry contains “BH-07”, “BH07” and “BH-007”. Two share collar position, site and drilling date; the third is 12 km away. The learner normalises labels only for candidate search, merges the first two after evidence review, retains all aliases and leaves the distant record separate despite its similar spelling.

Work the example in four passes:

  1. Preserve the received records and write the intended decision without correcting anything.
  2. Identify entities, claims, context, uncertainties and policy constraints; mark every unresolved item.
  3. Apply the versioned rules, create derivatives and record the exact transformation plus validation evidence.
  4. Issue an accept, reject or quarantine decision and show how an independent reviewer can reproduce it.

Because the example is entirely synthetic, its values demonstrate method only. The important result is the chain from received evidence to justified decision. If a required fact is absent, the worked solution records the gap rather than manufacturing a plausible value.

Practice task

Build an identity-resolution worksheet for thirty synthetic records. Define normalisation only for matching, rank independent evidence, document accept, reject and review outcomes, then test no reassignment, alias uniqueness, merge-cycle prevention and deterministic survivor resolution.

Use the following acceptance criteria:

  • All required identifiers and references resolve to declared types.
  • Every transformation preserves the received evidence and records its derivation.
  • Invalid, unknown and inapplicable states remain distinct and machine-testable.
  • The output identifies the contract, vocabulary and processing versions used.
  • A second reader can reproduce the validation result without private knowledge.

Submit the source snapshot, authored contract or model, validation output, derivative, manifest and a short decision record. A screenshot alone is insufficient because it cannot demonstrate the exact input, version or rule execution.

Common failure modes

  • A display label doubles as the primary identifier.
  • Case folding changes identifiers rather than comparison keys.
  • A merge deletes the retired identity and its evidence.
  • A similarity threshold silently approves ambiguous matches.

These failures share a pattern: convenient representation is mistaken for verified meaning. Diagnose the earliest boundary at which an assumption became implicit. Correct by restoring source evidence, making the assumption a versioned field or rule, rerunning dependent transformations and superseding—not overwriting—the affected release.

Do not repair a failure by adding an undocumented default. A blocked result with a specific missing dependency is safer and more reusable than a complete-looking result whose meaning cannot be reconstructed.

Review questions

  1. Why are natural keys evidence rather than guaranteed identity?
  2. What must an alias record contain?
  3. Why can a split require more information than undoing a merge?
  4. Which invariants prevent identifier corruption?

For each answer, identify the governing invariant, the evidence needed to evaluate it and the appropriate release behaviour when the invariant fails. A strong answer distinguishes scientific uncertainty from missing semantics and distinguishes a recoverable warning from a hard contract violation.

Sources and further reading