Database localization

Question

i am looking for opinions if the following problem maybe has a better/different/common solution:

---

I have a database for products which contains the names of the products in english (the default language of this application) and i need translations of the names if available.

Currently i have this setup:

A product table

CREATE TABLE products
(
  id serial NOT NULL,
  "name" character varying(255) NOT NULL,
  CONSTRAINT products_pkey PRIMARY KEY (id)
)

and a product localization table

CREATE TABLE products_l10n
(
  product_id serial NOT NULL,
  "language" character(2) NOT NULL,
  "name" character varying(255) NOT NULL,
  CONSTRAINT products_l10n_pkey PRIMARY KEY (product_id, language),
  CONSTRAINT products_l10n_product_id_fkey FOREIGN KEY (product_id)
      REFERENCES products (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

and i use the following query to retrieve a list of localized products (german in this case) with fallback to the default english names:

SELECT p.id, COALESCE(pl.name, p.name) 
from products p LEFT 
JOIN products_l10n pl ON p.id = pl.product_id AND language = 'de';

The SQL code is in postgres dialect. Data is stored as UTF-8.

Answer 1

Looks good to me. The one thing I might change is the way you handle languages: that should probably be a separate table. Thus, you would have:

CREATE TABLE products_l10n
(
  product_id serial NOT NULL,
  language_id int NOT NULL,
  "name" character varying(255) NOT NULL,
  CONSTRAINT products_l10n_pkey PRIMARY KEY (product_id, language),
  CONSTRAINT products_l10n_product_id_fkey FOREIGN KEY (product_id)
      REFERENCES products (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
  CONSTRAINT products_l10n_language_id_fkey FOREIGN KEY (language_id)
      REFERENCES languages (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

CREATE TABLE languages
)
  id serial not null
  "language" character(2) NOT NULL
)

Besides that, I think you've got just about the best possible solution.

Answer 2

Looks good - similar to my preferred localization technique - what about wide characters (Japanese)? We always used nvarchar to handle that.

What we actually found, however in our international purchasing operation, was that there was no consistency across international boundaries on products, since the suppliers in each country were different, so we internationalized/localized our interface, but the databases were completely distinct.