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.