Как поддерживать многоязычный подход в схеме базы данных?

Мне пришлось столкнуться с этим в базе данных анкет. Необходимо было перевести несколько вопросников на несколько языков (английский, японский, китайский).

Сначала мы определили все текстовые столбцы, которые будут распечатаны на вопросниках. Для всего этого нам нужно иметь возможность хранить перевод. Для каждой таблицы с текстовыми столбцами, которые требуют перевода, мы затем создали таблицу _translations, имея внешний ключ, указывающий на первичный ключ исходной таблицы, внешний ключ на нашу языковую таблицу, а затем столбец Unicode для каждого текстового поля. это потребует перевода. В этих текстовых столбцах мы будем хранить переводы для каждого нужного нам языка.

Таким образом, типичный запрос будет выглядеть так:

select     p.id
,          pt.product_name
,          pt.product_description
from       product                  p
inner join product_translations pt
on         p.id = pt.product_id
and        'fr' = pt.language_code

Таким образом, всегда достаточно одного дополнительного соединения (для каждой таблицы), чтобы получить переводы.

Я должен отметить, что нам приходилось иметь дело только с ограниченным количеством таблиц, поэтому не было большой проблемой поддерживать несколько дополнительных таблиц %_translations.

Мы рассматривали возможность добавления столбцов для нового языка, но отказались от этого по нескольким причинам. Во-первых, количество поддерживаемых языков было неизвестно, но могло быть значительным (10, 20 языков или, может быть, больше). В сочетании с тем фактом, что в большинстве таблиц было по крайней мере 3 отдельных столбца, понятных человеку, нам пришлось бы добавить много-много текстовых столбцов, что привело бы к очень широким строкам. Поэтому мы решили этого не делать.

Другой подход, который мы рассматривали, заключался в создании одной большой таблицы «меток», имеющей столбцы:

( имя_таблицы , идентификатор_таблицы , имя_столбца , идентификатор_языка , переведенный_текст )

эффективно иметь одну таблицу для хранения всех переводов в любом месте базы данных. Мы отказались и от этого, потому что это усложнило бы написание запросов (поскольку каждый «обычный» столбец приводил бы к одной строке в таблице перевода, что приводило бы к эффективному присоединению и без того большой таблицы перевода к обычной таблице несколько раз (один раз для каждого переведенный столбец).Для вашего примера таблицы вы получите такие запросы:

select     product.id 
,          product_name.translated_text product_name
,          product_description.translated_text product_description
from       product p
inner join translations product_name
on         p.id = product_name.id
and        'product'      = product_name.table_name
and        'product_name' = product_name.column_name
and        'fr'           = product_name.language
inner join translations product_description
on         p.id = product_name.id
and        'product'      = product_description.table_name
and        'product_description' = product_description.column_name
and        'fr'           = product_description.language

как вы можете видеть, по сути это похоже на дизайн сущности-атрибута-значения, что делает запрос громоздким.

Еще одна проблема последнего подхода заключается в том, что это затруднит, если не сделает невозможным, наложение ограничений на переведенный текст (в нашем случае в основном ограничения уникальности). С отдельной таблицей для переводов вы можете легко и чисто решить эти проблемы.

Я только что реализовал то, что работает очень хорошо.

Как и у многих других, у меня не было чистого листа, и все таблицы были приспособлены для хранения английского текста.

Мне не особенно нравилось удваивать количество таблиц или столбцов для получения многоязычной базы данных.

Я решил использовать XML как способ хранения всех переводов в одном поле:

<text>
  <translation lang="EN-GB">good day</translation>
  <translation lang="FR-FR">bonjour</translation>
</text>

Так, например, я начал с таблицы, которая содержит только английский язык:

ProductId INT
ProductName varchar(200)
ProductDescription varchar(1000)

Затем я создал многоязычные поля:

ProductId INT
ProductNameTranslation xml
ProductDescriptionTranslation xml

Если вы хотите получить значение только для чтения для исходных полей, вы можете просто добавить сохраняемые вычисляемые столбцы:

ProductId INT
ProductNameTranslation xml
ProductDescriptionTranslation xml
ProductNameDefaultLang = dbo.GetDefaultLanguage(ProductNameTranslation)
ProductDescriptionDefaultLang = dbo.GetDefaultLanguage(ProductDescriptionTranslation)

На уровне бизнеса/данных поле XML преобразуется в бизнес-класс словарного типа с ключом, являющимся перечислением языка:

ProductName {
  get {return this.ProductNameTranslation[selectedLanguage];}
  set {...}
}

Этот подход позволил мне избежать необходимости полностью переделывать базу данных. Это также означает меньшее взаимодействие с базой данных, сохранение одной строки вместо одной основной и 3 строки перевода.

Кроме того, это позволяет избежать необходимости обрабатывать случаи, когда в основной строке нет строк перевода.

Следует отметить, что XML имел схему и XML-индексы для повышения производительности.

Этот подход очень полезен, когда вы хотите выполнять преобразование языка постепенно, т. е. по одному полю за раз.