CODEWITHSUNDEEP
sqldatabaselocalization
Fionn
Fionn

Reputation: 11265

Database localization

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.

Upvotes: 10

Views: 6795

Answers (6)

Emmanuel
Emmanuel

Reputation: 1

When dealing with this kind of thing, i use to build a product table containing no name at all, and a product_translation table holding only names (and more, obviously).

Then i end up with this kind of query:

SELECT 
    i.id, 
    i.price, 
    it.label 
FROM 
    items i 
    LEFT JOIN items_trans it 
        ON i.id=it.item_id AND it.lang_id=(
            SELECT lang_id
            FROM items_trans
            WHERE item_id=i.id
            ORDER BY
                (lang_id=1) DESC,
                (lang_id=0) DESC
            LIMIT 1
        )

What do you think ?

Upvotes: 0

Jonathan Leffler
Jonathan Leffler

Reputation: 754090

The only complicating factor that others have not mentioned is code sets - will you be able to handle Hebrew, Arabic, Russian, Chinese, Japanese? If everything is Unicode, you only have to worry about GB18030 (Chinese), which is (IIUC) a superset of Unicode.

Upvotes: 0

Chris Shaffer
Chris Shaffer

Reputation: 32575

The only variation I can offer is that you may also want to include country/dialect possibility; eg, instead of just English (en), use English US (en-US). That way you can account for variations all the way (eg, British spellings, French Canadian probably has differences from the French spoken in France, etc).

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89671

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.

Upvotes: 1

Rob Williams
Rob Williams

Reputation: 7921

Looks decent to me.

Obviously you should put the localized name into a Unicode column, which you could opt to put the English default into an ASCII field (assuming the database supports that). It may be best to just do Unicode throughout and "forget" about it.

Upvotes: 0

Daniel Spiewak
Daniel Spiewak

Reputation: 55123

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.

Upvotes: 7

Related Questions