Alex Rivera | Logout

Database localization

Asked 2008-10-10T00:21:54.397
10

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.

sql database localization
Edit
Report

2 Answers

7

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.

answered 2008-10-10T00:28:51.497
0

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 ?

answered 2010-02-04T07:55:16.570

Your Answer