Store translated content per record and language

How to do it

A column per language needs a schema change for every new language and hides which texts are missing. A translations table with a unique key on record and language adds languages as data. Read with the fallback chain from Fall back to the closest supported locale, and report missing rows like missing messages. Mark which language is the source, so a changed source flags its translations as Mark translations stale when the source changes says.

Examples

SQL example

CREATE TABLE product_translations (
  product_id bigint NOT NULL REFERENCES products(id),
  language   text   NOT NULL,
  name       text   NOT NULL,
  is_source  boolean NOT NULL DEFAULT false,
  PRIMARY KEY (product_id, language)
);

JavaScript example

const nameFor = (rows, chain) => chain.map(tag => rows.find(row => row.language === tag)).find(Boolean)?.name;
nameFor(rows, ['pt-BR', 'pt', 'en']);

Python example

def name_for(rows: list[dict], chain: list[str]) -> str | None:
    by_language = {row["language"]: row["name"] for row in rows}
    return next((by_language[tag] for tag in chain if tag in by_language), None)

How to check

A query lists records that lack a translation in a published language. The list is empty, or each missing row falls back to the default language in the API.

Related rules

• LOC-23 Fall back to the closest supported locale in Localization
• LOC-20 Mark translations stale when the source changes in Localization
• LOC-43 Show product safety information in the buyer's language in Localization