Reference Data, Code Set, and Master Data Governance Model

1. Core Idea

Reference data adalah data kecil yang sering diremehkan tetapi dipakai di seluruh sistem.

Examples:

• status code,
• reason code,
• country,
• currency,
• unit of measure,
• tax category,
• product category,
• order action,
• billing cycle,
• payment term,
• approval type,
• fallout reason,
• cancellation reason,
• serviceability status,
• customer segment,
• sales channel,
• resource type.

Mental model:

Reference data is the shared vocabulary of enterprise systems. If it is inconsistent, every service speaks a slightly different business language.

Dalam CPQ / Quote / Order / Billing / Telco BSS/OSS, reference data governance menentukan apakah reporting, integration, validation, approval, billing, and support memiliki semantics yang sama.

2. Why Reference Data Matters

Masalah umum:

• service A memakai CANCELLED, service B memakai CANCELED,
• order status external tidak match internal status,
• invoice reason code tidak dikenal reporting,
• discount reason free-text sehingga analytics kacau,
• unit GB dan GIGABYTE dianggap beda,
• country/currency tidak valid,
• tax category dipakai tanpa owner,
• approval type berubah tanpa workflow update,
• reason code dihapus padahal historical records masih menggunakannya,
• external billing code mapping tidak terdokumentasi,
• UI localization mengubah meaning code.

Reference data yang buruk menyebabkan mismatch kecil yang menyebar ke seluruh quote-to-cash.

3. Reference Data vs Master Data vs Transaction Data

Do not model every small table as master data. But reference data still needs ownership and lifecycle.

4. Code Set

Code set is a governed group of codes.

Examples:

ORDER_ACTION_CODE
QUOTE_STATUS_CODE
CURRENCY_CODE
UOM_CODE
CANCELLATION_REASON_CODE
FULFILLMENT_FALLOUT_REASON_CODE
APPROVAL_TYPE_CODE

Code set fields:

code_set
- id
- code_set_name
- description
- owner_group
- lifecycle_status
- version
- effective_from
- effective_to

Code value fields:

code_value
- id
- code_set_id
- code
- display_name
- description
- status
- sort_order
- effective_from
- effective_to

5. Code Value Lifecycle

Code values should have lifecycle.

Statuses:

DRAFT
ACTIVE
DEPRECATED
RETIRED
DISABLED

Rules:

• active code can be used for new transactions,
• deprecated code remains valid for old data but not new data,
• retired code should not be used but historical records retain it,
• do not hard-delete code values used in historical records.

Example:

Cancellation reason OLD_PRODUCT_MIGRATION deprecated after new reason taxonomy launched.
Historical orders still show it.

6. Free-Text Reason Anti-Pattern

Bad:

cancel_reason = "customer changed mind"
cancel_reason = "Customer Changed Mind"
cancel_reason = "Cust changed"
cancel_reason = "no budget"

Good:

reason_code = CUSTOMER_REQUEST
reason_text = optional additional detail

Code supports reporting; text supports human context.

Use both:

reason_code required
reason_text optional

7. Status Code Governance

Status code semantics are critical.

Example:

ORDER_STATUS:
  CAPTURED
  VALIDATED
  SUBMITTED
  IN_PROGRESS
  FULFILLED
  COMPLETED
  CANCELLED
  FAILED
  FALLOUT

For each status, document:

• meaning,
• allowed transitions,
• terminal/non-terminal,
• visible to customer,
• billing impact,
• fulfillment impact,
• reporting category,
• external mapping,
• owner.

Do not add status code casually to "fix one case". It may affect workflow, reporting, SLA, and API clients.

8. Reason Code Taxonomy

Reason codes should be hierarchical where useful.

Example fallout reasons:

DATA_ERROR
  MISSING_ADDRESS
  INVALID_BILLING_ACCOUNT
  INVALID_PRODUCT_CONFIG

DOWNSTREAM_REJECTION
  OSS_REJECTED
  BILLING_REJECTED
  PAYMENT_REJECTED

TIMEOUT
  OSS_TIMEOUT
  BILLING_TIMEOUT

Fields:

parent_code
category
severity_default
owner_group
retryable_default

Taxonomy enables reporting and routing.

9. Unit of Measure

UOM is critical for pricing, usage, rating, and inventory.

Examples:

• GB,
• MB,
• minute,
• second,
• request,
• seat,
• user,
• device,
• Mbps,
• item,
• month.

Fields:

uom_code
display_name
base_unit
conversion_factor
dimension
precision
active

Unit conversion must be governed.

Failure mode:

Usage event says 1024 MB but rating treats as 1024 GB.

10. Currency Code

Currency seems simple but affects money correctness.

Fields:

currency_code
minor_unit
active
effective_from
effective_to

Consider:

• precision,
• currency conversion source,
• historical exchange rate,
• rounding,
• invoice currency,
• quote currency,
• reporting currency.

Do not store amount without currency.

11. Country, Region, and Locale

Country/region reference affects:

• address validation,
• tax,
• currency,
• legal entity,
• serviceability,
• localization,
• reporting.

Fields:

country_code
country_name
region_code
active
locale_defaults

Avoid free-text country names in transaction tables.

Use stable code + display/localization.

12. Product and Service Taxonomy

Product category taxonomy supports:

• pricing,
• reporting,
• approval routing,
• eligibility,
• tax,
• fulfillment decomposition,
• product catalog navigation.

Examples:

Connectivity
  Broadband
  Mobile
  Dedicated Internet

Managed Services
  Managed Firewall
  Managed Router

Taxonomy changes affect reporting trend. Effective-date taxonomy if historical reporting matters.

13. Reference Data Mapping

External systems have different codes.

Model mapping:

reference_data_mapping
- id
- code_set_name
- internal_code
- external_system
- external_code
- mapping_direction
- effective_from
- effective_to
- status

Example:

internal ORDER_STATUS COMPLETED
external billing status CLOSED
external OSS status DONE

Mappings must be versioned/effective-dated.

14. Code Translation and Localization

Display name differs by locale.

Do not use display string as code.

Model:

code_value_translation
- code_value_id
- locale
- display_name
- description

Example:

code = CUSTOMER_REQUEST
en = Customer request
id = Permintaan pelanggan

Code remains stable. Display changes/localizes.

15. Effective Dating Reference Data

Reference data changes over time.

Examples:

• currency disabled,
• product category renamed,
• cancellation reason deprecated,
• tax category changed,
• approval reason added,
• mapping changed for external billing.

Use:

effective_from
effective_to
status

Transaction records should store code used at time. Historical reporting should interpret code based on historical mapping if necessary.

16. Reference Data Ownership

Each code set needs owner.

Examples:

Owner approves changes and communicates impact.

17. Reference Data Change Governance

Change process should include:

• request,
• impact analysis,
• owner approval,
• compatibility check,
• API/event/report impact,
• localization update,
• external mapping update,
• deployment/versioning,
• deprecation plan,
• communication.

Do not update reference data in production manually without audit.

18. Reference Data as Configuration

Some reference data is static enough to deploy with code.

Some must be runtime-configurable.

Be careful with Java enum for codes that business frequently changes.

Changing enum may require deploy and migration.

19. API Contract and Reference Data

APIs should expose stable codes.

Example:

{
  "status": "IN_PROGRESS",
  "statusDisplay": "In Progress"
}

Clients should not send localized display name.

If API allows client to query code sets:

GET /reference-data/code-sets/ORDER_STATUS

Response should include:

• code,
• display name,
• status,
• effective date,
• sort order,
• deprecation flag.

20. Events and Reference Data

Events should use stable codes, not display names.

Example:

{
  "eventType": "OrderCancelled",
  "cancellationReasonCode": "CUSTOMER_REQUEST"
}

If event consumers need description, they can map code or receive snapshot if contract requires.

Do not change code meaning without event contract version impact analysis.

21. Reporting and Reference Data

Reporting needs code grouping.

Examples:

Cancellation reason group:
  Customer-driven
  Provider-driven
  Technical failure
  Fraud/risk

Model:

code_group
- code_set
- group_code
- code
- effective_from/to

This supports stable reporting buckets.

If grouping changes historically, use effective dating.

22. Master Data Governance

Master data includes:

• customer,
• account,
• organization,
• product catalog,
• billing account,
• site,
• contact.

Governance questions:

• who owns source of truth?
• how duplicates are resolved?
• how merge/split handled?
• how external IDs mapped?
• what fields are authoritative?
• how updates propagate?
• how conflicts resolved?
• what quality checks exist?

Master data needs more than reference table. But reference data governance principles still apply.

23. Duplicate and Matching Rules

Master data often needs matching.

Examples:

• duplicate customer,
• duplicate site/address,
• duplicate contact,
• duplicate product code,
• duplicate external account mapping.

Matching metadata:

match_rule
- entity_type
- rule_code
- match_fields
- confidence_threshold
- owner_group

Match result:

potential_duplicate
- entity_type
- entity_id_1
- entity_id_2
- confidence
- status
- resolution_action

Do not auto-merge critical master data without review unless rule is highly trusted.

24. Golden Record and Survivorship

If multiple sources provide same master data, decide survivorship.

Example customer fields:

Model:

field_authority_rule
- entity_type
- field_path
- authoritative_source
- fallback_source
- conflict_policy

This avoids random last-write-wins.

25. PostgreSQL Physical Design

Code set:

create table code_set (
  id uuid primary key,
  code_set_name text not null unique,
  description text,
  owner_group text,
  lifecycle_status text not null,
  version integer not null default 1,
  effective_from timestamptz,
  effective_to timestamptz,
  created_at timestamptz not null,
  updated_at timestamptz not null
);

Code value:

create table code_value (
  id uuid primary key,
  code_set_id uuid not null references code_set(id),
  code text not null,
  display_name text not null,
  description text,
  status text not null,
  parent_code_value_id uuid,
  sort_order integer,
  effective_from timestamptz,
  effective_to timestamptz,
  metadata jsonb,
  created_at timestamptz not null,
  updated_at timestamptz not null,
  unique (code_set_id, code)
);

Mapping:

create table reference_data_mapping (
  id uuid primary key,
  code_set_name text not null,
  internal_code text not null,
  external_system text not null,
  external_code text not null,
  mapping_direction text not null,
  status text not null,
  effective_from timestamptz not null,
  effective_to timestamptz,
  created_at timestamptz not null,
  unique (code_set_name, internal_code, external_system, external_code, mapping_direction)
);

Translation:

create table code_value_translation (
  id uuid primary key,
  code_value_id uuid not null references code_value(id),
  locale text not null,
  display_name text not null,
  description text,
  unique (code_value_id, locale)
);

Indexes:

create index idx_code_value_set_status
on code_value (code_set_id, status, sort_order);

create index idx_ref_mapping_external
on reference_data_mapping (external_system, external_code, code_set_name);

create index idx_ref_mapping_internal
on reference_data_mapping (code_set_name, internal_code, external_system);

26. Java/JAX-RS Backend Implications

Possible APIs:

GET /reference-data/code-sets
GET /reference-data/code-sets/{codeSetName}
GET /reference-data/code-sets/{codeSetName}/values
POST /reference-data/code-sets/{codeSetName}/values
POST /reference-data/mappings

Service rules:

• validate code uniqueness,
• prevent deleting used codes,
• allow deprecation instead,
• require owner approval for critical code set,
• publish reference data change event,
• update caches,
• audit changes.

Use reference data service or module rather than hard-coded scattered constants when business-managed.

27. Cache Strategy

Reference data is often cached.

Use:

• versioned cache,
• short/medium TTL,
• invalidation on change,
• startup warmup for stable code sets,
• fallback behavior if reference data unavailable.

Cache key:

refdata:{codeSetName}:v:{version}:locale:{locale}

Do not let stale reference data allow invalid new transaction after code retired if correctness matters.

28. Data Quality Checks

Examples:

-- Active transaction using retired code for new records
-- Conceptual; adjust per entity.
select id, cancellation_reason_code
from product_order
where created_at > :retired_at
  and cancellation_reason_code = :retired_code;

-- External mapping duplicates
select code_set_name, external_system, external_code, count(*)
from reference_data_mapping
where status = 'ACTIVE'
group by code_set_name, external_system, external_code
having count(*) > 1;

-- Code value invalid interval
select id, code
from code_value
where effective_to is not null
  and effective_to <= effective_from;

29. Failure Modes

30. PR Review Checklist

When reviewing reference/master data changes, ask:

• Is this a stable enum or governed reference data?
• Who owns the code set?
• Is code stable and display localized separately?
• Is lifecycle status supported?
• Can used code be deprecated instead of deleted?
• Does this affect API/event contracts?
• Does this affect reporting grouping?
• Does this need external system mapping?
• Does this need effective dating?
• Does this need cache invalidation?
• Are validation rules updated?
• Are tests updated for unknown/deprecated codes?
• Does this code appear in historical data?
• Is there a data quality check?
• Is master data source of truth clear?

31. Internal Verification Checklist

Verify these in the internal CSG/team context:

• Existing reference data/code set ownership.
• Whether status/reason codes are DB-managed, enum-managed, or external-managed.
• Whether codes are localized separately from stable code.
• Whether deprecated/retired code lifecycle exists.
• Whether external code mapping exists for billing/OSS/CRM/TMF APIs.
• Whether UOM/currency/country reference data is centralized.
• Whether approval/fallout/cancellation reason taxonomies exist.
• Whether code changes are audited.
• Whether code changes publish invalidation events.
• Whether master data survivorship rules exist for customer/account/contact/site.
• Whether duplicate customer/site/contact detection exists.
• Whether incidents mention unknown code, broken mapping, reporting mismatch, or deleted historical code.

32. Summary

Reference data is the shared vocabulary of enterprise systems.

A strong model must define:

• code set,
• code value,
• lifecycle,
• owner,
• display/localization,
• reason taxonomy,
• status semantics,
• UOM/currency/country governance,
• external mapping,
• effective dating,
• API/event compatibility,
• reporting grouping,
• master data ownership,
• duplicate/matching rules,
• survivorship,
• cache/invalidation,
• audit and data quality.

The key principle:

If reference data is unmanaged, every service eventually invents its own language. Enterprise correctness requires shared, governed, versioned, and mappable codes.