i'm searching for opinions when the following problem maybe includes a better/different/common solution:


I've got a database for items which consists of what they are called from the items in british (the default language of the application) and that i need translations from the names if available.

Presently i've this setup:

An item table

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

along with 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 that i make use of the following query to retrieve a listing of localized items (german within this situation) with fallback towards the default british 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 within postgres dialect. Information is saved as UTF-8.

Looks good in my experience. The main one factor I would change is how you handle languages: which should most likely be considered a separate table. Thus, you'd 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
)

On top of that, I believe you have nearly the perfect solution.

Looks good - much like my preferred localization technique - how about wide figures (Japanese)? We always used nvarchar to deal with that.

What we should really found, in our worldwide buying operation, was that there is no consistency across worldwide limitations on items, because the providers in every country were different, therefore we internationalized/localized our interface, however the databases were completely distinct.

The only real variation I'm able to offer is you may should also include country/dialect possibility eg, rather than just British (en), use British US (en-US). That method for you to take into account versions completely (eg, British spellings, French Canadian most likely has variations in the French spoken in France, etc).

Jonathan Leffler: You could utilize regular Unicode Chinese, and ignore GB_18030.

Looks decent in my experience.

Clearly you need to place the localized title right into a Unicode column, for you to choose to place the British default into an ASCII area (presuming the database supports that). It might be better to simply do Unicode throughout and "forget" about this.

The only real further complicating component that others haven't pointed out is code sets - are you going to have the ability to handle Hebrew, Arabic, Russian, Chinese, Japanese? If things are Unicode, you simply need to bother about GB18030 (Chinese), that is (IIUC) a superset of Unicode.

When confronted with this type of factor, i personally use to construct an item table that contains no title whatsoever, along with a product_translation table holding only names (and much more, clearly).

I quickly finish track of this type of query:


Choose

    i.id,

    i.cost,

    it.label

FROM

    products i

    LEFT JOIN products_trans it

        ON i.id=it.item_id Also it.lang_id=(

            Choose lang_id

            FROM products_trans

            WHERE item_id=i.id

            ORDER BY

                (lang_id=1) DESC,

                (lang_id=) DESC

            LIMIT 1

        )

What is your opinion ?