Локализация базы данных
Я ищу мнения, если следующая проблема может иметь лучшее / другое / общее решение:
У меня есть база данных для продуктов, которая содержит названия продуктов на английском языке (язык по умолчанию для этого приложения), и мне нужны переводы названий, если они доступны.
В настоящее время у меня есть эта настройка:
Таблица продуктов
CREATE TABLE products
(
id serial NOT NULL,
"name" character varying(255) NOT NULL,
CONSTRAINT products_pkey PRIMARY KEY (id)
)
и таблица локализации продуктов
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
)
и я использую следующий запрос для получения списка локализованных продуктов (в данном случае на немецком языке) с возвратом к английским именам по умолчанию:
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';
Код SQL написан на диалекте postgres. Данные хранятся в кодировке UTF-8.
Ответы 6
Мне нравится. Единственное, что я мог бы изменить, - это способ работы с языками: вероятно, это должна быть отдельная таблица. Таким образом, у вас будет:
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
)
Кроме того, я думаю, у вас есть лучшее из возможных решений.
Я думал точно так же. Однако, на первый взгляд, использование дополнительного уровня имеет некоторые преимущества: можно перечислить все доступные / настроенные языки (полезно в графическом интерфейсе), вы можете удалить весь язык (с ограничениями БД), и это предотвращает «опечатки». Хотя последнее не имеет значения при использовании 2-х символьных кодов;)
Смотрится мне прилично.
Очевидно, вы должны поместить локализованное имя в столбец Unicode, который вы можете выбрать, чтобы поместить английский язык по умолчанию в поле ASCII (при условии, что база данных поддерживает это). Возможно, лучше всего просто использовать Unicode и «забыть» об этом.
Отредактировали вопрос - данные всегда в Unicode (UTF-8).
Выглядит хорошо - похоже на мою любимую технику локализации - а как насчет широких символов (японских)? Для этого мы всегда использовали nvarchar.
Однако в ходе наших международных закупок мы обнаружили отсутствие согласованности в отношении продуктов через международные границы, поскольку поставщики в каждой стране были разными, поэтому мы интернационализировали / локализовали наш интерфейс, но базы данных были совершенно разными.
Отредактировали вопрос - данные всегда в Unicode (UTF-8).
Единственный вариант, который я могу предложить, это то, что вы также можете включить возможность страны / диалекта; например, вместо английского (en) используйте английский (США) (en-US). Таким образом, вы можете полностью учесть вариации (например, британское написание, французский канадский, вероятно, имеет отличия от французского, на котором говорят во Франции и т. д.).
Единственный усложняющий фактор, о котором не упоминали другие, - это кодовые наборы. Сможете ли вы работать с ивритом, арабским, русским, китайским, японским? Если все в Unicode, вам нужно беспокоиться только о GB18030 (китайский), который (IIUC) является надмножеством Unicode.
Имея дело с подобными вещами, я использую для создания таблицы продуктов, не содержащей вообще имени, и таблицы product_translation, содержащей только имена (и многое другое, очевидно).
Тогда я получаю такой запрос:
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
)
Что вы думаете ?
Мне не нравится этот вариант, потому что в этом случае вам не гарантируется наличие хотя бы значения по умолчанию для не переведенных имен.
Разве база данных не должна в любом случае оптимизировать символьное поле (2) как целое, потому что языковые коды ISO 639-1 более читабельны по сравнению с целыми числами, если есть проблемы.