我正在开发一个多语言软件。就应用程序代码而言,本地化不是问题。我们可以使用特定于语言的资源,并拥有各种与之配合良好的工具。
但是,定义多语言数据库模式的最佳方法是什么?假设我们有很多表(100个或更多),每个表可以有多个可以本地化的列(大多数nvarchar列应该是可本地化的)。例如,其中一个表可能保存产品信息:
CREATE TABLE T_PRODUCT (
NAME NVARCHAR(50),
DESCRIPTION NTEXT,
PRICE NUMBER(18, 2)
)
我可以想到三种方法来支持NAME和DESCRIPTION列中的多语言文本:
为每种语言单独列
当我们向系统中添加一种新的语言时,我们必须创建额外的列来存储翻译后的文本,如下所示:
CREATE TABLE T_PRODUCT (
NAME_EN NVARCHAR(50),
NAME_DE NVARCHAR(50),
NAME_SP NVARCHAR(50),
DESCRIPTION_EN NTEXT,
DESCRIPTION_DE NTEXT,
DESCRIPTION_SP NTEXT,
PRICE NUMBER(18,2)
)
Translation table with columns for each language
Instead of storing translated text, only a foreign key to the translations table is stored. The translations table contains a column for each language.
CREATE TABLE T_PRODUCT (
NAME_FK int,
DESCRIPTION_FK int,
PRICE NUMBER(18, 2)
)
CREATE TABLE T_TRANSLATION (
TRANSLATION_ID,
TEXT_EN NTEXT,
TEXT_DE NTEXT,
TEXT_SP NTEXT
)
Translation tables with rows for each language
Instead of storing translated text, only a foreign key to the translations table is stored. The translations table contains only a key, and a separate table contains a row for each translation to a language.
CREATE TABLE T_PRODUCT (
NAME_FK int,
DESCRIPTION_FK int,
PRICE NUMBER(18, 2)
)
CREATE TABLE T_TRANSLATION (
TRANSLATION_ID
)
CREATE TABLE T_TRANSLATION_ENTRY (
TRANSLATION_FK,
LANGUAGE_FK,
TRANSLATED_TEXT NTEXT
)
CREATE TABLE T_TRANSLATION_LANGUAGE (
LANGUAGE_ID,
LANGUAGE_CODE CHAR(2)
)
There are pros and cons to each solution, and I would like to know what are your experiences with these approaches, what do you recommend and how would you go about designing a multilanguage database schema.