International Address Database Design: Fields, Raw Values, and Versioning

Separate input, parsed components, and rendered output

Raw input is evidence, parsed fields support search and business logic, and rendered lines follow country order. Keeping only one prevents later audits and parser upgrades.

Store rawAddressLines, parsedComponents, normalizedAddress, and normalization provider/version. A normalization failure should not destroy the raw record.

Identifiers that look numeric are still text

Postcodes and house numbers can contain leading zeroes, letters, spaces, hyphens, and ranges. Integer types lose data and force country-specific patches.

Use Unicode text throughout. Derive length limits from real datasets and business boundaries, not average English samples.

type PostalAddress = {
  countryCode: string;
  addressLines: string[];
  adminArea1?: string;
  adminArea2?: string;
  adminArea3?: string;
  locality?: string;
  postalCode?: string;
  rawAddress: unknown;
};

Do not mix empty strings, null, and missing

Define whether an absent second line is omitted, null, or empty, then normalize at the boundary. Otherwise querying, deduplication, and exports treat one state as three.

Never fill missing lines with N/A or a dash; placeholders leak into labels.

Use snapshots for orders and audit records

An address book entry can change, while an order or invoice must preserve its historical address. Referencing only a current addressId mutates history.

Snapshot rendered lines, components, verification status, and timestamp.

• Orders do not change with the address book
• Record normalizer provider and version
• Timestamp verification status
• Do not log complete real addresses unnecessarily

Verify migrations with cross-country fixtures

Round-trip fixtures containing a UK named premises, Canadian alphanumeric postcode, German Unicode, Japanese source text, and leading zeroes. Compare semantic fields and raw JSON, not row counts alone.

A migration rehearsal should count truncated values, decoding failures, and empty-string-to-null changes. Database success is not enough if the application cannot reconstruct the original lines after reading them back. Produce a structural diff between old and new records and prepare a replay path from retained rawAddress data for irreversible transformations.

Run this suite against both a clean test database and a sanitized copy of production-shaped schemas. Realistic indexes, constraints, and legacy nulls often reveal behavior that an ideal empty schema cannot reproduce.

Measure the application read path as well as the migration job. An ORM serializer, search index, cache, or analytics export may still assume the previous column shape after the database accepts the new one. For every representative record, compare what the customer sees, what fulfilment receives, and what an audit export preserves. Release only when those consumers agree on the same address version, and keep a rollback decision point before old columns or raw payloads are removed. Document the final comparison alongside the migration release record.

• Compare Unicode strings and JSON structure
• Count truncation, encoding, and null conversions
• Read through the application layer
• Keep a raw-address replay tool