---
title: "How should I structure a database for multilingual content?"
description: "Keep UI strings in resource files, not in tables. For user or editorial content, use a translation table per entity, such as `product_translations(product_id, locale, name, description)` with a unique key on `(product_id, locale)`, BCP 47 locale tags and a fallback locale in the query layer."
category: general
date: 2026-10-09
canonical: https://localizely.com/i18n-questions/general/how-should-i-structure-a-database-for-multilingual-content/
---

# How should I structure a database for multilingual content?

First decide what is actually multilingual. Interface strings (button labels, validation messages, email templates) belong in resource files that ship with the code and are translated in a TMS such as Localizely; they do not need tables. What belongs in the database is content that users or editors create per language: product names, articles, category descriptions.

For that content there are three schema patterns.

A translation table per entity is the usual recommendation. The parent row holds language-neutral data and a child table holds one row per locale:

```sql
CREATE TABLE products (
  id          bigint PRIMARY KEY,
  sku         text NOT NULL,
  price_cents integer NOT NULL
);

CREATE TABLE product_translations (
  product_id  bigint NOT NULL REFERENCES products (id) ON DELETE CASCADE,
  locale      text NOT NULL,   -- BCP 47 tag: 'en', 'de', 'pt-BR'
  name        text NOT NULL,
  description text,
  PRIMARY KEY (product_id, locale)
);
```

Adding a language is an insert, not a migration; `NOT NULL` works per field; and you can index, search and sort per locale with ordinary SQL. The cost is one join per query and some ORM boilerplate.

One column per language (`name_en`, `name_de`, `name_fr`) is the simplest to query and fine for two or three languages that will never change, but every new language is a schema migration across every translated table, and rows get wide quickly.

A JSON column (`name jsonb` holding `{"en": "Chair", "de": "Stuhl"}`) is flexible and keeps the row count flat. PostgreSQL's `jsonb` can be indexed with GIN, but you give up per-locale constraints such as `NOT NULL` or uniqueness, need an expression index for every key you sort or search on, and queries get harder to express in an ORM. Reasonable for loosely structured content, weak for catalogs.

Whichever pattern you pick:

- Store locales as BCP 47 tags (`en`, `de-AT`, `zh-Hant`) in one consistent casing, the same tags your app uses for its UI locales.
- Implement fallback in the query layer, not in the schema: fetch the requested locale and the default locale, take the first non-null value, and an untranslated product still renders.
- Record which locale is the source text, so you know what to retranslate when an editor changes it.
- For a website, also decide the URL strategy per language (`/de/...` or `de.example.com`) and emit `hreflang` links between the versions; the [hreflang generator](/hreflang-generator/) produces the tag set.

Source: https://localizely.com/i18n-questions/general/how-should-i-structure-a-database-for-multilingual-content/