Entity Modeling and Multi-Source Alignment for Movie and TV Character Data

1. The Real Problem: From a Single Entry to a Full Entity System

The raw material is typical: a person or character name, plus attributes such as the work it belongs to, the release year, the cast and crew list, and how the name is credited. The record is assembled by merging several separate entries. This is fundamentally an entity data (Entity Data) governance problem. The difficulty is not the number of fields. The difficulty lies in four implicit assumptions:

1. Missing identifiers: the name is the only visible clue, but a name is neither a stable identifier nor guaranteed to be unique (same-name individuals, transliteration differences, abbreviations, and nicknames).
2. Conflated entity types: a person (a real actor), a character (a fictional identity), a work, and an organization belong to different entity types. Once they are flattened into a single string field, relationships such as “who played whom” and “who belongs to which work” can no longer be reconstructed.
3. Unequal source reliability: entries come from manual editing, external metadata, scraping, and user submissions. Accuracy and update frequency differ widely.
4. Currency and change: credit format, attribution, and even a person’s status all change. The system must retain “when a fact was true”, not only “what it is now”.

The engineering goal should therefore be explicit: turn volatile names into stable identifiers plus traceable attributes, that is, into entity master data (Master Data), and make every change auditable, traceable, and reversible.

2. Modeling Layer One: Identifiers, Types, and Attribute Tiers

2.1 Dual-Key Design: Internal Stable ID Plus External Source Key

Using the name as the primary key is the most common fatal mistake. A recommended pattern is an internal surrogate key plus a mapping table of external natural keys:

• entity_id: a system-generated stable identifier that never changes once assigned, referenced by all downstream systems.
• (source_system, source_key): the original key in the external source, used for incremental reconciliation and idempotent writes.
• The name field is for display and search only and is not part of any uniqueness constraint.

2.2 Making Entity Types and Relations Explicit

Model “character–appears in–work–creator” as independent entities and relations, and attach attributes to the relations themselves (credit name, ordering, character role, time interval). In this way, “the same person credited under different names” and “the same character name reused across different works” can be expressed naturally, without guessing from strings.

-- Entity master table: internal ID + type + canonical name + time interval
CREATE TABLE entity (
  entity_id      VARCHAR(64) NOT NULL,        -- internal stable identifier
  entity_type    VARCHAR(16) NOT NULL,        -- PERSON / CHARACTER / WORK / ORG
  canonical_name VARCHAR(256) NOT NULL,       -- normalized standard name
  status         VARCHAR(16) NOT NULL,        -- ACTIVE / MERGED / OBSOLETE
  valid_from     TIMESTAMP   NOT NULL,
  valid_to       TIMESTAMP   NOT NULL,        -- business validity period (one of the two time axes)
  PRIMARY KEY (entity_id, valid_from)
);
-- External source mapping: ensures idempotent incremental ingestion
CREATE TABLE entity_source_map (
  source_system  VARCHAR(32)  NOT NULL,
  source_key     VARCHAR(128) NOT NULL,
  entity_id      VARCHAR(64)  NOT NULL,
  payload_hash   CHAR(64)     NOT NULL,       -- content fingerprint, used to skip unchanged records
  synced_at      TIMESTAMP    NOT NULL,
  PRIMARY KEY (source_system, source_key)
);
-- Relation table: attribute-rich relationship modeling
CREATE TABLE entity_relation (
  from_entity_id VARCHAR(64)  NOT NULL,
  to_entity_id   VARCHAR(64)  NOT NULL,
  relation_type  VARCHAR(32)  NOT NULL,       -- PLAYED_BY / APPEARS_IN / CREATED_BY
  attrs_json     VARCHAR(2048),               -- credit name, ordering, character role, etc.
  valid_from     TIMESTAMP    NOT NULL,
  valid_to       TIMESTAMP    NOT NULL
);

2.3 Four Classes of Attributes

Splitting attributes by their change characteristics makes the model far easier to maintain than piling everything into one wide table:

Class	Characteristics	Handling strategy	Examples
Identifying	Used for matching, not for display	Multiple aliases coexist; no overwriting	Standard name, transliterated name, abbreviation
Descriptive	Rarely change	Updatable, change history retained	Type, language, region
Time-varying	Change over time	Dual time axis, effective interval	Attribution, credit, status
Provenance	Describe where a record came from	Append-only, never rewritten	Source, fetch time, raw payload pointer

3. The Name Reconciliation and Entity Resolution Pipeline

Alias reconciliation (Entity Resolution) is best implemented as a six-stage pipeline. Each stage can scale independently and use a swappable algorithm:

1. Text normalization: Unicode NFKC normalization, case folding, punctuation and whitespace folding, full-width and half-width unification, stripping of honorifics and title prefixes (Mr., Ms., Dr.), and unification of transliteration schemes. This step has the highest payoff because it eliminates a substantial share of “false differences”.
2. Index key generation: generate separate keys for exact matching, phonetic matching, and inverted-index retrieval. Do not mix all three purposes in one field.
3. Candidate recall: first use inverted indexes or hash keys for blocking, then use vector nearest neighbors to supplement semantic recall. This reduces the number of comparisons from O(N²) to near-linear. All-pairs comparison without blocking is not feasible at a million entities.
4. Feature scoring: field similarity (Jaccard, normalized edit distance), time proximity, source reliability, and a penalty term for attribute conflicts.
5. Three-way decision: auto-merge, manual review, or treat as a new entity. This avoids the binary trap of “merge everything or merge nothing”.
6. Merge execution: implement logical merge with a mapping table and a read-time view, keep the ability to roll back, and forbid physical overwriting.

def decide(cand, th_auto=0.92, th_review=0.65):
    """Make a three-way decision based on weighted similarity and conflict penalties.
    cand: a candidate pair (pending entity, existing entity) and its similarity features
    """
    s = weighted_score(cand.features)          # weighted similarity
    s -= conflict_penalty(cand.conflicts)      # penalty for key attribute conflicts (e.g., type mismatch, incompatible periods)
    if s >= th_auto:
        return "AUTO_MERGE", cand.existing_id
    if s >= th_review:
        return "MANUAL_REVIEW", cand.existing_id
    return "NEW_ENTITY", None

Thresholds must come from precision/recall tradeoff experiments on labeled samples and be adjusted as the business risk tolerance changes. Under-merging fragments a single entity; over-merging causes irreversible data pollution, which is usually far more costly. The thresholds should therefore be conservative, with a manual review path kept open.

4. Multi-Source Ingestion and Consistency

• Ingestion forms: batch files, read-only API pulls, change subscription (CDC), and message queue delivery. Running batch and streaming side by side is recommended: batch for full alignment and repair, streaming for near-real-time updates.
• Idempotent writes: use (source_system, source_key) for upserts, and skip unchanged records with a content hash so repeated deliveries have no side effects.
• Conflict resolution: do not use “last one wins” as the only rule. Use a combination of “source priority plus source update timestamp” to arbitrate, and persist the conflict records for auditing.
• Failure isolation: records that fail parsing or validation go to a dead-letter queue to be replayed separately. They must never block the main pipeline.
• Raw payload retention: when data lands in the lake, write the raw payload to object storage (bucketed by source and date), and keep only parsed results in the standardized layer. This way, the pipeline can be rerun from the raw data after the rules change, which is key to long-term maintainability.

5. Data Quality Checks (DQC)

Make the validation rules configurable, with two levels of effect: block and warn.

rules:
  - name: entity_name_required
    type: not_null
    fields: [canonical_name]
    on_fail: block
  - name: entity_type_enum
    type: in_set
    field: entity_type
    allowed: [PERSON, CHARACTER, WORK, ORG]
    on_fail: block
  - name: period_order
    type: expr
    expr: valid_to >= valid_from
    on_fail: block
  - name: source_map_unique
    type: unique
    fields: [source_system, source_key]
    on_fail: block
  - name: relation_dangling_ref
    type: referential_integrity
    expr: both entities on either end of the relation exist and are ACTIVE
    on_fail: warn
  - name: dup_name_ratio
    type: rate_threshold
    expr: ratio of entities sharing a duplicate canonical name < 2%
    on_fail: warn

Additional dimensions: completeness (required-field non-null rate), uniqueness (duplicate rate), value range (enums and numeric ranges, such as whether a year range is plausible), consistency (cross-table referential integrity), and freshness (update delay distribution). Each needs a baseline and a trend dashboard. Sudden changes in a metric matter more than absolute thresholds, so alert on those first.

6. Versioning, Lineage, and Undo

• Dual time axis: valid_from/valid_to represents the business validity period, and there is a separate record insert/invalidation time that represents the period of the system’s awareness. This answers two different questions: “what the system believed at the time” and “what was true at the time”.
• End-to-end lineage: every attribute keeps its source, fetch time, raw payload hash, and processing version number, so downstream consumers can assess reliability.
• Soft delete and merge undo: merge actions are written to an event stream and support full rollback. Physical deletion is forbidden.
• Change broadcast: entity changes are published as events for search indexes, caches, risk control, and reports to consume, so each downstream system does not have to poll on its own.

7. Compliance and Access Control

• Data classification: separate public information from personal information, and collect only the fields the business strictly requires, following the principle of minimum necessity.
• Field-level and row-level permissions: the query API filters visible fields and entity scope by caller identity, and masking is done on the server side, not in the frontend.
• Audit: queries, exports, and bulk merges are all written to a log service with a defined retention period. Bulk exports should trigger an approval step and rate limiting.
• Source legitimacy: do not use data of unknown provenance or unclear authorization. Cross-border transfers and onward sharing need separate assessment.

8. Service APIs, Observability, and Cost

API contracts should be organized around entities rather than pages: entity detail (by stable ID), entity search (keywords + structured filters + semantic recall + reranking), review queue query and manual adjudication, and merge/split operations (requiring an idempotency key and an operator identity).

Observability metrics:

• Data plane: incremental delay (P50/P99), parse failure rate, dedup hit rate, review queue length and average dwell time, and spot-check results of resolution accuracy.
• Service plane: query QPS, P99 latency, cache hit rate, and error code distribution.
• Business plane: fragmentation rate of the same entity, cross-source conflict rate, and manual acceptance rate.

Cost and architecture: containerize compute jobs and scale them elastically with batch and streaming separated. Sink raw payloads and historical versions to infrequent-access or archive storage tiers, and keep hot data in the standardized layer and cache. On the retrieval side, differentiate the update cadence of the inverted index and the vector index; the vector index can use lower-frequency full rebuilds plus incremental appends.

9. Rollout Steps and Common Anti-Patterns

Recommended steps: scope definition and ontology design (define entity types and relations first) → evaluation of rules and thresholds on labeled samples → shadow run (write only, no external exposure) → gradual ingestion of part of the sources → full cutover plus routine manual review → monthly quality review.

Anti-patterns to avoid at all costs:

1. Using the name as the primary key, which turns every later merge into a data migration incident.
2. Doing exact string matching only, so alias fragments never converge.
3. Collapsing a person and a character into the same entity, which makes the relations unrecoverable.
4. Overwriting physically when merging, which makes the merge neither reversible nor explainable.
5. Keeping only “auto-decided” results, with no manual review and no undo path.
6. Validating only at ingestion time, without monitoring long-term metric trends.

10. Acceptance Checklist

• Every entity has an internal stable identifier, and the name is not used as the primary key.
• Entity types and relations are modeled explicitly, and relations carry attributes.
• All writes are idempotent, and repeated deliveries do not create duplicate entities.
• Conflict resolution rules are explainable, traceable, and auditable.
• The thresholds for the three-way decision are backed by experiments, with review and rollback paths in place.
• Raw payloads are fully retained, so the pipeline can be rerun after the rules change.
• DQC rules are configurable, and metrics and trends are observable on dashboards.
• Field-level permissions, masking, and access auditing are in place.

Summary: upgrading a “person entry” into governable entity data has little to do with how advanced the algorithm is. It comes down to whether six things are engineered into the system: stable identifiers, typed modeling, idempotent ingestion, traceable changes, reversible merges, and observable quality. Guarantee that identifiers and lineage are never lost first, and only then pursue higher disambiguation accuracy. That is the basic order in which systems like this stay controllable over the long term.