画板 1

    Film and TV Metadata Modeling and Retrieval System Implementation Guide

    3 hr ago0 views

    This guide provides a practical approach to governing film and TV metadata for search, recommendation, rights management, scheduling, and compliance review. It covers common metadata governance issues, core modeling principles, a normalized relational schema, field standardization, extraction and validation workflows, retrieval architecture, acceptance metrics, and operational controls.

    1. The pain point: why film and TV metadata is hard to govern

    In film and TV content platforms, rights management, promotion and distribution, and data platforms, metadata is the shared foundation for search, recommendation, scheduling, and compliance review. Film and TV metadata is structured data describing a work and its cast and crew, production and release information, dialogue language, color, audio format, rating, and related attributes.

    In practice, this information often appears as semi-structured text composed of a raw title and raw body. Fields such as localized title, original foreign title, genre, production region, director, lead actors, runtime, release date, dialogue language, color, and rating are mixed together without a unified standard.

    Typical problems fall into three categories:

    1. Inconsistent field definitions: Release dates may include multiple regional dates; production time and release year may differ; runtime, language codes, and character encoding lack consistent standards.
    2. Messy entity relationships: Multiple translated titles for the same work, different foreign spellings for the same actor, and cast/role relationships embedded in free text.
    3. Weak quality control: Spelling variants, duplicate records, and invalid enumerated values cannot be fully checked manually, and search hit rate and ranking quality decline as data volume grows.

    2. Design principles: five constraints across the pipeline

    • Entity separation: Store works, people, regional releases, and aliases in separate tables instead of placing all attributes in one large table.
    • Standardization first: Prefer industry-standard encodings for dates, durations, languages, regions, and work identifiers.
    • Traceable and auditable: Each metadata record should retain source system, source page, extraction method, and batch information to support traceback and correction.
    • Extensible: Use controlled vocabularies and extension fields to handle new genres, new regions, and new attributes without frequently changing the main schema.
    • Read/write separation: The write path focuses on validation and governance; the read path focuses on search performance and cache efficiency.

    3. Modeling approach: layered decomposition by entity type

    The model can be decomposed into five entity groups: work, credit relationship, person, regional release, and alias. The following tables implement this model.

    1. -- Work master table: stores stable work-level attributes
    2. CREATE TABLE media_work (
    3.     work_id            BIGINT       PRIMARY KEY,   -- Unique system identifier
    4.     source_system      VARCHAR(32)  NOT NULL,      -- Source system code, e.g., CATALOG_A
    5.     source_external_id VARCHAR(64)  NOT NULL,      -- Original ID in the source system, e.g., EIDR/ISAN
    6.     title_zh           VARCHAR(200),               -- Localized title, nullable
    7.     title_origin       VARCHAR(300),               -- Original foreign title
    8.     genres             VARCHAR(200),               -- Genre, controlled vocabulary, comma-separated
    9.     runtime_min        SMALLINT,                   -- Runtime in minutes
    10.     prod_year          SMALLINT,                   -- Production year
    11.     color_flag         CHAR(1),                    -- Color flag: C=color, B=black-and-white
    12.     audio_mode         VARCHAR(32),                -- Audio mode: MONO/STEREO
    13.     alias_fingerprint  CHAR(32),                   -- Normalized name fingerprint for candidate deduplication
    14.     source_type        VARCHAR(16),                -- Extraction source: MANUAL/RULE/MODEL
    15.     source_page_url    VARCHAR(512),               -- Source page URL or original page address
    16.     rule_version       VARCHAR(32),                -- Extraction rule/model version
    17.     batch_no           VARCHAR(32),                -- Batch number
    18.     confidence         DECIMAL(4,3),               -- Confidence from 0.000 to 1.000
    19.     created_at         TIMESTAMP    NOT NULL,
    20.     updated_at         TIMESTAMP    NOT NULL,
    21.     UNIQUE (source_system, source_external_id)      -- Source-level uniqueness constraint
    22. );
    23. CREATE INDEX idx_work_title ON media_work (title_zh);
    24. CREATE INDEX idx_work_fp    ON media_work (alias_fingerprint);
    26. -- Production region table: one row per region for co-productions
    27. CREATE TABLE media_work_region (
    28.     id          BIGINT PRIMARY KEY,
    29.     work_id     BIGINT      NOT NULL,
    30.     region_code VARCHAR(8)  NOT NULL,   -- ISO 3166-1 alpha-2
    31.     is_main     CHAR(1),                -- Y=main production region
    32.     created_at  TIMESTAMP   NOT NULL,
    33.     updated_at  TIMESTAMP   NOT NULL,
    34.     UNIQUE (work_id, region_code),
    35.     CONSTRAINT fk_region_work FOREIGN KEY (work_id) REFERENCES media_work (work_id)
    36. );
    37. CREATE INDEX idx_region_work_id ON media_work_region (work_id);
    39. -- Dialogue language table: multiple dialogue, subtitle, and dubbing versions
    40. CREATE TABLE media_work_language (
    41.     id         BIGINT PRIMARY KEY,
    42.     work_id    BIGINT      NOT NULL,
    43.     lang_code  VARCHAR(16) NOT NULL,    -- BCP 47 / ISO 639-1
    44.     lang_type  VARCHAR(16),             -- DIALOGUE/SUBTITLE/DUB
    45.     created_at TIMESTAMP   NOT NULL,
    46.     updated_at TIMESTAMP   NOT NULL,
    47.     UNIQUE (work_id, lang_code, lang_type),
    48.     CONSTRAINT fk_lang_work FOREIGN KEY (work_id) REFERENCES media_work (work_id)
    49. );
    50. CREATE INDEX idx_lang_work_id ON media_work_language (work_id);
    52. -- Person table: reusable across works
    53. CREATE TABLE media_person (
    54.     person_id   BIGINT       PRIMARY KEY,
    55.     name_norm   VARCHAR(200) NOT NULL,  -- Canonical name
    56.     name_origin VARCHAR(300),           -- Foreign name
    57.     created_at  TIMESTAMP    NOT NULL,
    58.     updated_at  TIMESTAMP    NOT NULL,
    59.     UNIQUE (name_norm)
    60. );
    62. -- Cast/crew relationship table: many-to-many between works and people
    63. CREATE TABLE media_credit (
    64.     id            BIGINT      PRIMARY KEY,
    65.     work_id       BIGINT      NOT NULL,
    66.     person_id     BIGINT      NOT NULL,
    67.     job           VARCHAR(32) NOT NULL, -- ACTING/DIRECTING/PRODUCING
    68.     role_name     VARCHAR(200),         -- Role name, required when job=ACTING
    69.     billing_order SMALLINT,             -- Billing order
    70.     created_at    TIMESTAMP   NOT NULL,
    71.     updated_at    TIMESTAMP   NOT NULL,
    72.     UNIQUE (work_id, person_id, job, role_name),
    73.     CONSTRAINT fk_credit_work   FOREIGN KEY (work_id)   REFERENCES media_work (work_id),
    74.     CONSTRAINT fk_credit_person FOREIGN KEY (person_id) REFERENCES media_person (person_id)
    75. );
    76. CREATE INDEX idx_credit_work_id   ON media_credit (work_id);
    77. CREATE INDEX idx_credit_person_id ON media_credit (person_id);
    79. -- Alias table: multilingual and translated-title recall
    80. CREATE TABLE media_work_alias (
    81.     id                BIGINT       PRIMARY KEY,
    82.     work_id           BIGINT       NOT NULL,
    83.     alias_text        VARCHAR(300) NOT NULL,
    84.     alias_lang        VARCHAR(16),             -- Language tag
    85.     script_type       VARCHAR(16),             -- HANS/HANT/LATN
    86.     alias_fingerprint CHAR(32)     NOT NULL,   -- Normalized retrieval fingerprint
    87.     created_at        TIMESTAMP    NOT NULL,
    88.     updated_at        TIMESTAMP    NOT NULL,
    89.     UNIQUE (work_id, alias_text),
    90.     CONSTRAINT fk_alias_work FOREIGN KEY (work_id) REFERENCES media_work (work_id)
    91. );
    92. CREATE INDEX idx_alias_fp ON media_work_alias (alias_fingerprint);
    94. -- Regional release table: one work may have multiple regions; one region may have multiple release attributes
    95. CREATE TABLE media_work_release (
    96.     id                 BIGINT PRIMARY KEY,
    97.     work_id            BIGINT     NOT NULL,
    98.     region_code        VARCHAR(8) NOT NULL,
    99.     release_date       DATE,
    100.     rating_code        VARCHAR(32),          -- Value from the rating mapping table for this region
    101.     rating_map_version VARCHAR(16),          -- Rating mapping table version
    102.     created_at         TIMESTAMP  NOT NULL,
    103.     updated_at         TIMESTAMP  NOT NULL,
    104.     UNIQUE (work_id, region_code, release_date),
    105.     CONSTRAINT fk_release_work FOREIGN KEY (work_id) REFERENCES media_work (work_id)
    106. );
    107. CREATE INDEX idx_release_date    ON media_work_release (release_date);
    108. CREATE INDEX idx_release_region  ON media_work_release (region_code);

    The model has four key points:

    1. Work-level attributes and region-level attributes must be separated: Runtime, color, and audio mode are work-level; release dates and ratings are region-level. The same work may have different release dates and ratings in different regions, so mixing them in one table inevitably loses information.
    2. Co-productions and multiple dialogue languages use relationship tables: Production regions and dialogue languages are not stored as single-value columns on the main table. They are expressed by media_work_region and media_work_language. Co-productions mark the main production region, and multiple dialogue versions are split into rows by DIALOGUE/SUBTITLE/DUB.
    3. Cast and crew are associated through relationship tables: Person information is reused across works, reducing redundancy and making it easier to calculate actor overlap.
    4. Aliases have a dedicated table: This keeps the primary title unique while supporting multilingual titles, translated titles, simplified and traditional Chinese, and romanization recall.

    4. Field standardization and example data structure

    Unified encoding is a prerequisite for all subsequent search and statistics. Data must be normalized before ingestion. Use the following standards:

    • Dates use ISO 8601 (YYYY-MM-DD).
    • Runtime is stored as an integer number of minutes.
    • Dialogue and subtitle languages use ISO 639-1 combined with BCP 47, such as fr or zh-Hant.
    • Production and release regions use ISO 3166-1 alpha-2, such as FR or US.
    • Work identifiers should preferentially use EIDR or ISAN.
    • Genre, color, and audio mode use controlled vocabularies.
    • Rating identifiers must be maintained in separate mapping tables by region. For example, France CNC uses TP / -12 / -16 / -18, the United States MPAA uses G / PG / PG-13 / R / NC-17, and Germany FSK uses 0 / 6 / 12 / 16 / 18.
    1. {
    2.   "workId": 100231,
    3.   "sourceSystem": "CATALOG_A",
    4.   "sourceExternalId": "10.5240/XXXX-XXXX-XXXX",
    5.   "title": { "zh": "Sample Localized Title", "origin": "Sample Title", "aliases": ["Alias A"] },
    6.   "genres": ["Drama", "Crime"],
    7.   "prodRegions": [
    8.     { "region": "FR", "isMain": true },
    9.     { "region": "IT", "isMain": false }
    10.   ],
    11.   "runtimeMinutes": 104,
    12.   "productionYear": 1977,
    13.   "dialogueLanguages": [
    14.     { "lang": "fr", "type": "DIALOGUE" },
    15.     { "lang": "it", "type": "DUB" }
    16.   ],
    17.   "color": "C",
    18.   "audioMode": "MONO",
    19.   "releases": [
    20.     { "region": "FR", "releaseDate": "1977-05-11", "rating": "TP" },
    21.     { "region": "DE", "releaseDate": "1977-09-08", "rating": "FSK12" },
    22.     { "region": "US", "releaseDate": "1977-09-08", "rating": "R" }
    23.   ],
    24.   "credits": [
    25.     { "personName": "Actor A", "role": "Lead", "job": "ACTING" },
    26.     { "personName": "Director A", "role": null, "job": "DIRECTING" }
    27.   ]
    28. }

    The example code is illustrative only. Actual values must be checked against the corresponding regional rating mapping table. Rating identifiers must not be reused across regions, otherwise compliance errors may occur.

    5. Extraction: from text to ingestible records

    1. Text segmentation and field recognition: Split text blocks by labels and colons, and use regular expressions and vocabularies to locate field boundaries.
    2. Rule validation: Validate runtime, year, and encoding against range and enumeration rules.
    3. Model-assisted extraction: For free text without obvious labels, use sequence labeling models to identify works, people, regions, and other entities, and output confidence scores. Confidence is a weighted score combining model probability and field-rule weights. Three handling levels apply: >= 0.90 for automatic ingestion, 0.70 to 0.90 for sample inspection, and < 0.70 for full manual review. Thresholds must be calibrated with a manually annotated evaluation set of at least 500 items and set separately by field type. The initial sampling ratio can be 10%; after extraction accuracy remains stable for two weeks, it can be reduced to 3% to 5%. Items involving ratings or release dates require two-person review.
    4. Entity alignment: Map extracted person names and work aliases to person master data and work master data.
    5. Manual sampling and feedback loop: Low-confidence samples enter the review queue, and corrections are fed back as training samples to form a closed loop.
    1. Raw text -> tokenization/sentence segmentation -> field candidate recognition -> rule validation -> entity alignment
    2.         -> confidence scoring -> high-confidence automatic ingestion / low-confidence manual review -> ingestion and index update

    6. Validation: executable quality rules

    • Required fields and enumerations: Work identifier, source system, and original ID are required. Genre, color, and audio mode must be within controlled vocabularies.
    • Range checks: Runtime is limited to 1 to 600 minutes. Production year is limited from 1888 to the current year.
    • Business deduplication keys: work_id is the system primary key. source_system + source_external_id is the business deduplication key with a unique constraint, replacing the original “main title + production year” approach. The candidate deduplication key is “earliest release region + earliest release date + main title + alias fingerprint,” supplemented by cast/crew overlap confirmation with a recommended Jaccard score of at least 0.6.
    • Consistency checks: The rule is min(release_date) < DATE(prod_year || '-01-01') indicates suspicious data. Compare the earliest regional release date with January 1 of the production year. A difference within one year is tolerable due to cross-year release windows, reshoots, or earlier regional releases. A difference of more than one year should be treated as suspicious and confirmed manually before storage.
    • Edge cases for name deduplication: If the localized title is missing, locate the work using the original foreign title and year. Works with the same name must be distinguished by year, cast/crew, and regional releases. Remakes and series sequels may have identical titles, in which case the year constraint may not work; rely on source_external_id and producer information.
    • Null strategy: Explicitly mark missing fields as “unknown” to avoid hiding data gaps with default values.

    7. Storage and retrieval

    • Primary storage: A relational database stores strongly consistent entities such as works, people, and cast/crew relationships.
    • Search layer: Elasticsearch stores works and aliases. For Chinese text, use an IK tokenizer (ik_max_word for indexing and ik_smart for queries); English uses standard tokenization. Before alias ingestion, normalize simplified and traditional Chinese conversion, pinyin, romanization, and full-width/half-width characters, and write alias_fingerprint for exact recall.
    • Synchronization: Prefer CDC incremental synchronization with scheduled full verification. Failed synchronization enters a retry queue; after three retries with exponential backoff, failed records go to a dead-letter table for manual intervention to ensure eventual consistency between the primary database and the index.
    • Cache layer: Popular work details and aggregation results use cache. When metadata updated_at changes, invalidate cache entries by exact key; the fallback TTL is 30 minutes.
    • Object storage: Original materials, cover images, and audio/video files are stored in object storage; metadata only stores reference addresses.
    • Compute and scheduling: Extraction, cleaning, and index synchronization run as offline jobs with incremental scheduling to ensure metadata updates are traceable.
    • Rights and compliance notes: Main metadata sources, such as third-party film databases and platform pages, may be subject to licensing agreements or database rights. Confirm the authorization scope and permitted uses before collection, and record the source and license terms. Cover images, stills, and audio/video assets require rights confirmation, and a takedown/deletion mechanism should be established. If a rights holder files a complaint, the record and object storage reference can be quickly located by work_id and cleaned. Incorrect rating labels may directly affect the protection of minors and lead to regional takedowns, fines, or administrative penalties. Changes involving ratings should retain an approval trail.

    The retrieval side provides a unified query interface that combines name search, genre filtering, regional release filtering, and rating filtering into a composable query DSL:

    1. {
    2.   "match_title": "Sample",
    3.   "genre_in": ["Drama", "Crime"],
    4.   "release_region": ["FR", "DE"],
    5.   "rating_in": ["TP", "-12"],
    6.   "page": 1
    7. }

    8. Acceptance: metric definitions and target values

    Stage Objective Key metric Measurement definition Reference target
    Phase 1 Build model and define standards Field completeness Non-empty required fields / required fields >= 98%
    Phase 1 Build model and define standards Enumeration compliance rate Enumeration values within controlled vocabularies / total enumeration values >= 99.5%
    Phase 2 Extraction and governance Extraction accuracy Field-level accuracy based on a manual annotation set with N >= 500 >= 95%
    Phase 2 Extraction and governance Duplicate rate Duplicate entries / total entries <= 0.5%
    Phase 2 Extraction and governance Manual review volume Manual reviews per 10,000 pending records Decrease over time
    Phase 3 Retrieval and serviceization Hit rate@10 Share of queries where the target work appears in the top 10 results >= 90%
    Phase 3 Retrieval and serviceization Average response time P95 latency of the retrieval API <= 200 ms
    Phase 3 Retrieval and serviceization Cache hit rate Cache-hit requests / total requests >= 70%

    Target values should be set based on business baselines and should not be copied directly. Connect data quality metrics to logging and monitoring/alerting. Set alert thresholds for sudden drops in field completeness and distribution shifts in extraction confidence. New versions should be released via canary: first expose 5% of traffic for 24 hours; if metrics do not degrade, roll out fully. If extraction accuracy drops by more than 3 percentage points or P95 latency exceeds the threshold, roll back immediately to the previous index and rule package.

    9. Frequently asked questions

    What fields does film and TV metadata include?
    It is usually divided into five groups: work-level fields such as title, genre, runtime, production year, color, and audio format; person-level fields such as canonical name, foreign name, and alias; relationship-level fields such as credit job and role; region-level fields such as release date and rating; and alias-level fields such as translated titles and language tags.

    How should multiple translated titles be retrieved?
    Store aliases in a separate table and index them. Combine simplified/traditional Chinese conversion, pinyin, and romanization normalization. Main titles and aliases participate equally in recall, and ranking weights by name type.

    Can rating classifications be reused across countries?
    No. Rating systems and codes differ by region. Each region requires an independent mapping table, and the mapping table version should be recorded.

    What if a localized title is missing?
    title_zh may be empty. Locate the work using the original foreign title and production year. Deduplication should rely on source_external_id rather than the name.

    10. Summary

    The core of film and TV metadata governance is not the number of fields, but three things: whether the entity model is clear, whether field definitions are unified, and whether quality validation forms a closed loop. Following the sequence of layered modeling, standard encoding, extraction and alignment, rule validation, and retrieval serviceization allows a usable search capability to be delivered relatively quickly, while building a solid data foundation for genre recommendation, rights management, and compliance review.

    09040904

    Top articles