Database localization

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

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
)

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';

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
)