Designing a Regional Entity Data Modeling and Query Service

1. Background: Starting from a “Regional Profile”

In information system development, there is a typical type of foundational data: a single record describing an administrative region, with scattered fields but high information density. Taking a county-level administrative region as an example, its profile information roughly includes:

• Postal Code: 122300
• Famous Scenic Spots: Kazuo Longyuan Tourist Area, Longfeng Mountain Scenic Area, Yulong Valley Hot Spring Resort
• Train Station: Kazuo Station
• License Plate Prefix: Liao N
• Gross Domestic Product (GDP): 13.0 billion CNY (2025)

While this data seems simple, turning it into a stable and reliable query service immediately exposes a set of typical engineering challenges: highly heterogeneous field types (codes like postal code and license plate, multi-value text like scenic spot lists, associated entities like train stations, and numerical values with units and time dimensions like GDP); drastically different update frequencies for each field (license plates rarely change, GDP is released annually, and scenic spots are added sporadically); and scattered query dimensions, ranging from exact lookups by code to batch retrieval filtered by specific metrics. This article uses this scenario as a sample to comprehensively break down the entire process from data modeling to service implementation for regional entities.

2. Field Characteristic Analysis Before Modeling

Before creating database tables, it is recommended to conduct a two-dimensional inventory of each field based on “Change Frequency × Data Form,” as this directly determines the subsequent modeling and storage strategy.

1. Highly stable fields: Postal code, license plate prefix. These are bound to the administrative division itself, with change cycles measured in years or even decades. They are suitable as primary keys or parts of unique indexes and can be safely cached for long periods.
2. Periodically updated fields: GDP. This is essentially not “a single number,” but a triplet of “value + unit + statistical year.” 13.0 billion CNY corresponds to the year 2025. If only a numerical column is stored, the same nominal metric for different years will overwrite itself, making history untraceable. The metric must explicitly carry a time dimension.
3. Multi-value text fields: Famous scenic spots. A region corresponds to zero or more scenic spots, which will continuously increase. If they are crammed into a single string column separated by commas, subsequent structured queries, deduplication, and individual maintenance of scenic spot details will be very painful. They should be modeled as a one-to-many relationship or an array type.
4. Associated entity fields: Train station. A station itself is an independent entity with its own name, railway lines, and geographical location. The relationship between a region and a station is a reference rather than subordinate text. Modeling it as an association allows future support for reverse requirements such as “querying all regions a specific railway line passes through.”

3. Conceptual Model and Schema Design

3.1 Entity Partitioning

Based on the above analysis, it is recommended to extract three core entities:

• Region: Carries code and descriptive base attributes.
• RegionIndicator: Carries metrics like GDP that change yearly, using (Region, Indicator Name, Statistical Year) as the unique key.
• PointOfInterest: Carries enumerable associated objects like scenic spots and stations, differentiated by a type field (e.g., scenic, station).

3.2 Illustrative Table Structure

CREATE TABLE region (
  region_code   VARCHAR(12) PRIMARY KEY,  -- Administrative division code
  name          VARCHAR(64) NOT NULL,
  zip_code      CHAR(6)     NOT NULL,     -- Postal code
  plate_prefix  VARCHAR(2)  NOT NULL,     -- License plate prefix
  updated_at    TIMESTAMP   NOT NULL
);
CREATE TABLE region_indicator (
  region_code   VARCHAR(12)   NOT NULL,
  indicator     VARCHAR(32)   NOT NULL,   -- e.g., gdp
  stat_year     SMALLINT      NOT NULL,   -- Statistical year
  value         DECIMAL(18,2) NOT NULL,
  unit          VARCHAR(8)    NOT NULL,   -- Unit of measurement
  PRIMARY KEY (region_code, indicator, stat_year)
);
CREATE TABLE region_poi (
  id            BIGINT       PRIMARY KEY,
  region_code   VARCHAR(12)  NOT NULL,
  poi_type      VARCHAR(16)  NOT NULL,    -- scenic / station / ...
  name          VARCHAR(128) NOT NULL
);

After partitioning, “querying a region’s 2025 GDP” and “listing all scenic spots in a region” become two clear, non-interfering query paths. Adding new metric types no longer requires altering the table structure.

3.3 External Aggregate View

When targeting frontend or open APIs, the three tables are typically aggregated into a flattened document for output:

{
  "name": "Kazuo Mongolian Autonomous County",
  "zipCode": "122300",
  "platePrefix": "Liao N",
  "indicators": [
    { "indicator": "gdp", "statYear": 2025, "value": 13.0, "unit": "billion CNY" }
  ],
  "pointsOfInterest": [
    { "type": "scenic",  "name": "Kazuo Longyuan Tourist Area" },
    { "type": "scenic",  "name": "Longfeng Mountain Scenic Area" },
    { "type": "scenic",  "name": "Yulong Valley Hot Spring Resort" },
    { "type": "station", "name": "Kazuo Station" }
  ]
}

3.4 Why a Single “Wide Table” is Not Recommended

Some teams tend to cram all fields into a single table: concatenating scenic spots with commas and leaving only a single numerical column for GDP. While this saves effort in the short term, it incurs a triple cost in the long run: the scenic spot list cannot participate in structured queries and deduplication; GDP overwriting leads to the loss of historical years, making it impossible to answer “what was it last year?”; and adding new indicators (e.g., resident population, forest coverage) requires frequent schema changes. The separation into metric tables and association tables is a direct application of the dimensional modeling philosophy: “facts are additive, dimensions change slowly.”

4. Storage and Caching Strategy

4.1 Tiered Storage Selection

• Core attributes and metrics: Placed in relational databases, utilizing transactions and unique constraints to ensure “only one record exists for the same region, same metric, same year.”
• Aggregate views: Output after aggregation by the service layer. Under heavy read pressure, the JSON document can be synced to a document database to reduce multiple round trips at the interface layer.
• Static resources: Large objects like scenic spot images and map tiles are placed in object storage, with only reference keys retained in the database.
• Cache: Regional profiles are typical read-heavy, write-light data, making them highly suitable for caching aggregated results to support high-concurrency reads.

4.2 Cache-Aside Read Pattern

// Pseudocode: Cache-Aside pattern for reading aggregate documents
function getRegionProfile(regionCode):
    key = "region:profile:" + regionCode
    doc = cacheGet(key)
    if doc == null:
        doc = aggregateFromDb(regionCode)   // Aggregate main, indicator, and association tables
        cacheSet(key, doc, ttl = 3600)      // Align TTL with data update cadence
    return doc

Two details are worth emphasizing. First, TTL selection must match the field update cadence: code fields can be cached for a long time, whereas if the “latest annual GDP” is exposed via a separate interface, the related cache should be actively invalidated after the annual data is published, rather than passively waiting for expiration. Second, the write path should delete the cache instead of updating it after modifying the database, using the “rebuild on next read” approach to reduce the probability of dirty data caused by concurrent writes.

5. Query Interface Design

It is recommended to provide two types of external interfaces:

1. Entity details: GET /regions/{regionCode}, returning the aggregate document from section 3.3.
2. Conditional search: GET /regions?platePrefix=Liao&minGdp=10&statYear=2025&page=1&size=20, supporting pagination and field projection (e.g., fields=name,gdp).

Three disciplines should be strictly followed in the design: metric filtering must explicitly carry statYear, otherwise the semantics of “minimum GDP 10 billion” are incomplete; multi-value fields (scenic spots) should provide contains/exists semantics rather than exact value matching; the external document structure must maintain backward compatibility—new fields should only be added, not renamed or have their meanings altered. This is a fundamental requirement for the long-term evolution of open APIs.

6. Data Update and Quality Governance

6.1 Pre-Ingestion Validation

Dirty data often enters through the entry point. Rule validation should be performed before writing:

// Pseudocode: Field format validation
RULES = {
  "zipCode":     "^\d{6}$",                 // Postal code: 6 digits
  "platePrefix": "^[\u4e00-\u9fa5][A-Z]$"   // License plate prefix: Province abbreviation + uppercase letter
}
function validate(field, value):
    return match(RULES[field], value) != null

For metrics like GDP, in addition to format, business rules must be validated: the value is non-negative, the unit is within a whitelist (e.g., billion CNY, million CNY), the statistical year is a valid year, and a uniqueness conflict check is performed on (Region, Metric, Year). In case of a conflict, handle it according to established strategies such as “overwriting requires approval” or “reject write.”

6.2 Batch Update Pipeline

The release of annual statistical data is a batch event. The following pipeline is recommended: source data files land in object storage → a validation task parses and writes to the database → an “indicator update” event is broadcast via a message queue → downstream services receive the event and invalidate the corresponding caches to refresh aggregate documents. The core value of this pipeline lies in decoupling “data arrival” from “cache invalidation”: any failed component can replay messages to catch up, without relying on manual data patching. Additionally, full-field changes are recommended to be logged in an audit trail (who, when, what changed), which satisfies traceability requirements and provides material for subsequent data quality dashboards.

7. Observability

Structured data services equally require comprehensive observability:

• Metric monitoring: API call volume, P99 latency, cache hit rate, validation failure rate.
• Logging: Query and write logs recorded in separate channels, adopting structured logging for easy search and aggregation.
• Alerting: Beyond conventional threshold alerts, set up “freshness alerts” for annual data. If no new annual GDP is ingested after the publication window closes, trigger a prompt to patch the data promptly.

If deployed in the cloud, the aforementioned monitoring, alerting, message queues, and object storage components can be built using standard cloud environment product categories (for example, Baidu AI Cloud and other mainstream cloud environments provide such standard capabilities). Teams can focus their efforts on the data model and business rules themselves.

8. Conclusion

A seemingly ordinary regional profile covers almost all classic topics of entity data modeling: heterogeneous field partitioning, handling slowly changing dimensions, expressing multi-value relationships, cache design for read-heavy/write-light scenarios, and decoupled pipelines for batch data updates. Solidifying a set of fields like “postal code + scenic spots + train stations + license plate + annual GDP” allows you to distill a reusable regional entity data service template. This can be smoothly migrated to any scenario involving “entity + metrics + associated objects,” such as cities, industrial parks, or retail stores.