Use arrays to store multilanguage texts

Поиск
Список
Период
Сортировка
От Michal Táborský
Тема Use arrays to store multilanguage texts
Дата
Msg-id 40B8586F.1070706@taborsky.cz
обсуждение исходный текст
Ответы Re: Use arrays to store multilanguage texts  (Greg Stark <gsstark@mit.edu>)
Re: Use arrays to store multilanguage texts  (Joe Conway <mail@joeconway.com>)
Список pgsql-general
I am wondering, if it's effective to use text arrays to store
multilanguage information.

We used to do it like this:

CREATE TABLE product (
    id serial NOT NULL,
    price float4,
    ...
)

CREATE TABLE product_txt (
    product_id integer,
    language_id integer, -- 1=English, 2=Czech, ...
    name text,
    description text,
    ...
    PRIMARY KEY (product_id, language_id)
)

Then in queries for English version we used joins:

SELECT product.*, product_txt.*
FROM product JOIN product_txt ON product.id=product_txt.product_id
WHERE product_txt.language_id=1

It works as is supposed to, but of course there is some database
overhead and mainly it is much more complicated to handle data this way.
Since 7.4 introduced arrays, I was thinking about using them for storing
multilanguage strings. Like this:

CREATE TABLE product (
    id serial NOT NULL,
    price float4,
    name text[],
    description text[]
)

Then I'd just do:

SELECT id, price, name[1], description[1] FROM product

Much simpler and IMHO faster (but I'm not a pg-hacker). I never had time
to test it much, but now we are going to build a new database with
multilanguage strings and I am seriously considering using arrays.

The only question, which remains unanswered is how is it with indexes. I
mean--most of the queries on such table are ordered by name, for
example. Therefore I'd do this:

SELECT id, price, name[1], description[1] FROM product ORDER BY name[1]

Is it possible to build an index, which will be used in such query? I
had no luck with CREATE INDEX product_name1 ON product (r[1]), but maybe
the syntax is just somehow different.

Are there any more drawbacks or limitations, that maybe I am not aware
of, which would discourage you from doing the multilanguage support this
way? Should we consider this or stick to the old ways?

Thanks for your attention.

--
Michal Taborsky
http://www.taborsky.cz


Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: Paul Thomas
Дата:
Сообщение: Re: PostgreSQL delete the blank in the end of the String automatically. how can I avoid it?
Следующее
От: Tamer
Дата:
Сообщение: locating postgresql lib and include files for SuSE 9.0 RPMS?