多语言数据库的架构
我正在开发一个多语言软件。 就应用程序代码而言，可定位性不是问题。 我们可以使用特定于语言的资源，并拥有各种适合他们的工具。
但是，定义多语言数据库模式的最佳方法是什么？ 假设我们有很多表（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)
)
翻译表与每种语言的列
不存储翻译的文本，只存储翻译表的外键。 翻译表包含每种语言的列。
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
)
用每种语言的行翻译表格
不存储翻译的文本，只存储翻译表的外键。 翻译表仅包含一个关键字，并且一个单独的表包含每个翻译语言的一行。
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)
)
每种解决方案都有优点和缺点，我想知道您对这些方法有什么经验，您有什么建议以及如何设计多语言数据库模式。
您如何看待每个可翻译表的相关翻译表？
CREATE TABLE T_PRODUCT（pr_id int，PRICE NUMBER（18，2））
CREATE TABLE T_PRODUCT_tr（pr_id INT FK，languagecode varchar，pr_name text，pr_descr text）
这样，如果你有多个可翻译的列，它只需要一个单独的连接来获得它+，因为你不是自动生成翻译版本，它可能更容易导入项目及其相关翻译。
不利的一面是，如果你有一个复杂的语言回退机制，你可能需要为每个转换表实现 - 如果你依靠某个存储过程来做到这一点。 如果你从应用程序这样做，这可能不会是一个问题。
让我知道你的想法 - 我也将为我们的下一个应用做出决定。 到目前为止，我们已经使用了第三种类型
第三种选择是最好的，原因如下：
-亚当
这是一个有趣的问题，所以让我们来看看死神。
让我们从方法1的问题开始：
问题：为了节省速度，你在反规范化。
在SQL中（除了带有hstore的PostGreSQL），你不能传递参数语言，并说：
SELECT ['DESCRIPTION_' + @in_language] FROM T_Products
所以你必须这样做：
SELECT
Product_UID
,
CASE @in_language
WHEN 'DE' THEN DESCRIPTION_DE
WHEN 'SP' THEN DESCRIPTION_SP
ELSE DESCRIPTION_EN
END AS Text
FROM T_Products
这意味着如果添加新语言，则必须更改所有查询。 这自然会导致使用“动态SQL”，因此您不必更改所有查询。
这通常会导致类似的情况（并且它不能在视图或表值函数中使用，如果您确实需要过滤报告日期，则确实存在问题）
CREATE PROCEDURE [dbo].[sp_RPT_DATA_BadExample]
@in_mandant varchar(3)
,@in_language varchar(2)
,@in_building varchar(36)
,@in_wing varchar(36)
,@in_reportingdate varchar(50)
AS
BEGIN
DECLARE @sql varchar(MAX), @reportingdate datetime
-- Abrunden des Eingabedatums auf 00:00:00 Uhr
SET @reportingdate = CONVERT( datetime, @in_reportingdate)
SET @reportingdate = CAST(FLOOR(CAST(@reportingdate AS float)) AS datetime)
SET @in_reportingdate = CONVERT(varchar(50), @reportingdate)
SET NOCOUNT ON;
SET @sql='SELECT
Building_Nr AS RPT_Building_Number
,Building_Name AS RPT_Building_Name
,FloorType_Lang_' + @in_language + ' AS RPT_FloorType
,Wing_No AS RPT_Wing_Number
,Wing_Name AS RPT_Wing_Name
,Room_No AS RPT_Room_Number
,Room_Name AS RPT_Room_Name
FROM V_Whatever
WHERE SO_MDT_ID = ''' + @in_mandant + '''
AND
(
''' + @in_reportingdate + ''' BETWEEN CAST(FLOOR(CAST(Room_DateFrom AS float)) AS datetime) AND Room_DateTo
OR Room_DateFrom IS NULL
OR Room_DateTo IS NULL
)
'
IF @in_building <> '00000000-0000-0000-0000-000000000000' SET @sql=@sql + 'AND (Building_UID = ''' + @in_building + ''') '
IF @in_wing <> '00000000-0000-0000-0000-000000000000' SET @sql=@sql + 'AND (Wing_UID = ''' + @in_wing + ''') '
EXECUTE (@sql)
END
GO
这个问题是
a）日期格式是非常特定于语言的，因此如果您没有以ISO格式输入（一般花园类程序员通常不会这样做，并且在报告中用户确定即使明确指示这样做，也不会为你做任何事情）。
和
b） 最重要的是 ，你没有任何语法检查 。 如果<insert name of your "favourite" person here>会改变模式，因为突然要求换翅膀，并创建一个新表格，旧的表格将被删除，但引用字段将被重新命名，您不会收到任何警告。 当你运行它时没有选择wing参数 （==> guid.empty），报告甚至可以工作。 但突然间，当一个实际用户实际选择一个机翼==> 繁荣 。 这种方法彻底打破了任何一种测试。
方法2：
简而言之：“好”的想法（警告 - 讽刺），让我们将方法3（许多条目时速度较慢）的缺点与方法1的相当可怕的缺点结合起来。
这种方法的唯一优点是您可以将所有翻译保留在一张表格中，因此使维护变得简单。 然而，使用方法1和动态SQL存储过程以及包含翻译的（可能是临时的）表以及目标表的名称可以实现同样的目的（假设您将所有文本字段命名为相同）。
方法3：
所有翻译的一张表：缺点：您必须在产品表中为要翻译的n个字段存储n个外键。 因此，您必须对n个字段进行n次连接。 当翻译表是全局的时候，它有很多条目，连接变慢。 此外，您总是必须将n个字段的T_TRANSLATION表加入n次。 这是相当的开销。 现在，当您必须为每位客户提供定制翻译时，您会做什么？ 你必须在另外一个表上添加另外2个n连接。 如果您必须加入10个表格，并且需要2x2xn = 4n个额外的连接，那真是一团糟！ 此外，这种设计可以使用2个表格的相同翻译。 如果我在一个表格中更改项目名称，是否真的要更改另一个表格中的条目以及每次单独使用？
另外，您不能删除并重新插入表格，因为现在在产品表格中有外键...您当然可以省略设置FK，然后<insert name of your "favourite" person here>可以删除表格，然后用newid（）重新插入所有条目[或者通过在插入中指定id，但是具有标识插入OFF ]，并且会（并且将）导致数据垃圾（并且null - 引用例外）真的很快。
方法4（未列出）：将所有语言存储在数据库的XML字段中。 例如
-- CREATE TABLE MyTable(myfilename nvarchar(100) NULL, filemeta xml NULL )
;WITH CTE AS
(
-- INSERT INTO MyTable(myfilename, filemeta)
SELECT
'test.mp3' AS myfilename
--,CONVERT(XML, N'<?xml version="1.0" encoding="utf-16" standalone="yes"?><body>Hello</body>', 2)
--,CONVERT(XML, N'<?xml version="1.0" encoding="utf-16" standalone="yes"?><body><de>Hello</de></body>', 2)
,CONVERT(XML
, N'<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<lang>
<de>Deutsch</de>
<fr>Français</fr>
<it>Ital&iano</it>
<en>English</en>
</lang>
'
, 2
) AS filemeta
)
SELECT
myfilename
,filemeta
--,filemeta.value('body', 'nvarchar')
--, filemeta.value('.', 'nvarchar(MAX)')
,filemeta.value('(/lang//de/node())[1]', 'nvarchar(MAX)') AS DE
,filemeta.value('(/lang//fr/node())[1]', 'nvarchar(MAX)') AS FR
,filemeta.value('(/lang//it/node())[1]', 'nvarchar(MAX)') AS IT
,filemeta.value('(/lang//en/node())[1]', 'nvarchar(MAX)') AS EN
FROM CTE
然后，您可以通过SQL中的XPath-Query获取值，您可以在其中放入字符串变量
filemeta.value('(/lang//' + @in_language + '/node())[1]', 'nvarchar(MAX)') AS bla
你可以像这样更新值：
UPDATE YOUR_TABLE
SET YOUR_XML_FIELD_NAME.modify('replace value of (/lang/de/text())[1] with ""I am a ''value ""')
WHERE id = 1
你可以用'.../' + @in_language + '/...'替换/lang/de/... '.../' + @in_language + '/...'
有点像PostGre hstore，除了由于解析XML的开销（而不是从PG hstore中的关联数组中读取条目），它变得太慢了，再加上xml编码使得它太痛苦而无用。
方法5（根据SunWuKung的建议，您应该选择一个）：每个“产品”表的一个转换表。 这意味着每种语言只有一行，还有几个“文本”字段，所以它只需要在N个字段上进行一次（左）连接。 然后，您可以在“产品”表中轻松添加默认字段，您可以轻松地删除并重新插入翻译表，并且可以创建第二个自定义翻译表（按需），您也可以删除并重新插入），并且您仍然拥有所有外键。
让我们举个例子来看看这个作品：
首先，创建表格：
CREATE TABLE [dbo].[T_Languages](
[Lang_ID] [int] NOT NULL,
[Lang_NativeName] [nvarchar](200) NULL,
[Lang_EnglishName] [nvarchar](200) NULL,
[Lang_ISO_TwoLetterName] [varchar](10) NULL,
CONSTRAINT [PK_T_Languages] PRIMARY KEY CLUSTERED
(
[Lang_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_Products](
[PROD_Id] [int] NOT NULL,
[PROD_InternalName] [nvarchar](255) NULL,
CONSTRAINT [PK_T_Products] PRIMARY KEY CLUSTERED
(
[PROD_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T_Products_i18n](
[PROD_i18n_PROD_Id] [int] NOT NULL,
[PROD_i18n_Lang_Id] [int] NOT NULL,
[PROD_i18n_Text] [nvarchar](200) NULL,
CONSTRAINT [PK_T_Products_i18n] PRIMARY KEY CLUSTERED
(
[PROD_i18n_PROD_Id] ASC,
[PROD_i18n_Lang_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- ALTER TABLE [dbo].[T_Products_i18n] WITH NOCHECK ADD CONSTRAINT [FK_T_Products_i18n_T_Products] FOREIGN KEY([PROD_i18n_PROD_Id])
ALTER TABLE [dbo].[T_Products_i18n] WITH CHECK ADD CONSTRAINT [FK_T_Products_i18n_T_Products] FOREIGN KEY([PROD_i18n_PROD_Id])
REFERENCES [dbo].[T_Products] ([PROD_Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[T_Products_i18n] CHECK CONSTRAINT [FK_T_Products_i18n_T_Products]
GO
ALTER TABLE [dbo].[T_Products_i18n] WITH CHECK ADD CONSTRAINT [FK_T_Products_i18n_T_Languages] FOREIGN KEY([PROD_i18n_Lang_Id])
REFERENCES [dbo].[T_Languages] ([Lang_ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[T_Products_i18n] CHECK CONSTRAINT [FK_T_Products_i18n_T_Languages]
GO
CREATE TABLE [dbo].[T_Products_i18n_Cust](
[PROD_i18n_Cust_PROD_Id] [int] NOT NULL,
[PROD_i18n_Cust_Lang_Id] [int] NOT NULL,
[PROD_i18n_Cust_Text] [nvarchar](200) NULL,
CONSTRAINT [PK_T_Products_i18n_Cust] PRIMARY KEY CLUSTERED
(
[PROD_i18n_Cust_PROD_Id] ASC,
[PROD_i18n_Cust_Lang_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[T_Products_i18n_Cust] WITH CHECK ADD CONSTRAINT [FK_T_Products_i18n_Cust_T_Languages] FOREIGN KEY([PROD_i18n_Cust_Lang_Id])
REFERENCES [dbo].[T_Languages] ([Lang_ID])
GO
ALTER TABLE [dbo].[T_Products_i18n_Cust] CHECK CONSTRAINT [FK_T_Products_i18n_Cust_T_Languages]
GO
--ALTER TABLE [dbo].[T_Products_i18n_Cust] WITH NOCHECK ADD CONSTRAINT [FK_T_Products_i18n_Cust_T_Products] FOREIGN KEY([PROD_i18n_Cust_PROD_Id])
ALTER TABLE [dbo].[T_Products_i18n_Cust] WITH CHECK ADD CONSTRAINT [FK_T_Products_i18n_Cust_T_Products] FOREIGN KEY([PROD_i18n_Cust_PROD_Id])
REFERENCES [dbo].[T_Products] ([PROD_Id])
GO
ALTER TABLE [dbo].[T_Products_i18n_Cust] CHECK CONSTRAINT [FK_T_Products_i18n_Cust_T_Products]
GO
然后填写数据
DELETE FROM T_Languages;
INSERT INTO T_Languages (Lang_ID, Lang_NativeName, Lang_EnglishName, Lang_ISO_TwoLetterName) VALUES (1, N'English', N'English', N'EN');
INSERT INTO T_Languages (Lang_ID, Lang_NativeName, Lang_EnglishName, Lang_ISO_TwoLetterName) VALUES (2, N'Deutsch', N'German', N'DE');
INSERT INTO T_Languages (Lang_ID, Lang_NativeName, Lang_EnglishName, Lang_ISO_TwoLetterName) VALUES (3, N'Français', N'French', N'FR');
INSERT INTO T_Languages (Lang_ID, Lang_NativeName, Lang_EnglishName, Lang_ISO_TwoLetterName) VALUES (4, N'Italiano', N'Italian', N'IT');
INSERT INTO T_Languages (Lang_ID, Lang_NativeName, Lang_EnglishName, Lang_ISO_TwoLetterName) VALUES (5, N'Russki', N'Russian', N'RU');
INSERT INTO T_Languages (Lang_ID, Lang_NativeName, Lang_EnglishName, Lang_ISO_TwoLetterName) VALUES (6, N'Zhungwen', N'Chinese', N'ZH');
DELETE FROM T_Products;
INSERT INTO T_Products (PROD_Id, PROD_InternalName) VALUES (1, N'Orange Juice');
INSERT INTO T_Products (PROD_Id, PROD_InternalName) VALUES (2, N'Apple Juice');
INSERT INTO T_Products (PROD_Id, PROD_InternalName) VALUES (3, N'Banana Juice');
INSERT INTO T_Products (PROD_Id, PROD_InternalName) VALUES (4, N'Tomato Juice');
INSERT INTO T_Products (PROD_Id, PROD_InternalName) VALUES (5, N'Generic Fruit Juice');
DELETE FROM T_Products_i18n;
INSERT INTO T_Products_i18n (PROD_i18n_PROD_Id, PROD_i18n_Lang_Id, PROD_i18n_Text) VALUES (1, 1, N'Orange Juice');
INSERT INTO T_Products_i18n (PROD_i18n_PROD_Id, PROD_i18n_Lang_Id, PROD_i18n_Text) VALUES (1, 2, N'Orangensaft');
INSERT INTO T_Products_i18n (PROD_i18n_PROD_Id, PROD_i18n_Lang_Id, PROD_i18n_Text) VALUES (1, 3, N'Jus d''Orange');
INSERT INTO T_Products_i18n (PROD_i18n_PROD_Id, PROD_i18n_Lang_Id, PROD_i18n_Text) VALUES (1, 4, N'Succo d''arancia');
INSERT INTO T_Products_i18n (PROD_i18n_PROD_Id, PROD_i18n_Lang_Id, PROD_i18n_Text) VALUES (2, 1, N'Apple Juice');
INSERT INTO T_Products_i18n (PROD_i18n_PROD_Id, PROD_i18n_Lang_Id, PROD_i18n_Text) VALUES (2, 2, N'Apfelsaft');
DELETE FROM T_Products_i18n_Cust;
INSERT INTO T_Products_i18n_Cust (PROD_i18n_Cust_PROD_Id, PROD_i18n_Cust_Lang_Id, PROD_i18n_Cust_Text) VALUES (1, 2, N'Orangäsaft'); -- Swiss German, if you wonder
然后查询数据：
DECLARE @__in_lang_id int
SET @__in_lang_id = (
SELECT Lang_ID
FROM T_Languages
WHERE Lang_ISO_TwoLetterName = 'DE'
)
SELECT
PROD_Id
,PROD_InternalName -- Default Fallback field (internal name/one language only setup), just in ResultSet for demo-purposes
,PROD_i18n_Text -- Translation text, just in ResultSet for demo-purposes
,PROD_i18n_Cust_Text -- Custom Translations (e.g. per customer) Just in ResultSet for demo-purposes
,COALESCE(PROD_i18n_Cust_Text, PROD_i18n_Text, PROD_InternalName) AS DisplayText -- What we actually want to show
FROM T_Products
LEFT JOIN T_Products_i18n
ON PROD_i18n_PROD_Id = T_Products.PROD_Id
AND PROD_i18n_Lang_Id = @__in_lang_id
LEFT JOIN T_Products_i18n_Cust
ON PROD_i18n_Cust_PROD_Id = T_Products.PROD_Id
AND PROD_i18n_Cust_Lang_Id = @__in_lang_id
如果你很懒，那么你也可以使用ISO-TwoLetterName（'DE'，'EN'等）作为语言表的主键，那么你不必查找语言ID。 但是如果你这样做了，你可能会想用IETF语言标记来代替，这样更好，因为你得到的是de-CH和de-DE，这与真正的皮层摄影术不同（在任何地方都是双s而不是ß） ，尽管它是相同的基本语言。 这只是一个很小的细节，可能对您很重要，特别是考虑到en-US和en-GB / en-CA / en-AU或fr-FR / fr-CA存在类似的问题。
Quote：我们不需要它，我们只用英文做我们的软件。
答：是的 - 但哪一个？
无论如何，如果你使用整数ID，你很灵活，并且可以在以后更改你的方法。
你应该使用这个整数，因为没有什么比讨厌的Db设计更令人讨厌，破坏性和麻烦了。
另见RFC 5646，ISO 639-2，
而且，如果你仍然在说“我们”只是提出“只有一种文化”的申请（通常像en-US） - 因此我不需要额外的整数，这将是一个很好的时间和地点提及IANA语言标签，不是吗？
因为他们是这样的：
de-DE-1901
de-DE-1996
和
de-CH-1901
de-CH-1996
（1996年进行了拼写改革......）如果拼写错误，请尝试在字典中找到一个单词; 这在处理法律和公共服务门户的应用程序中变得非常重要。
更重要的是，有些地区正在从西里尔文变成拉丁文字母，这可能比一些模糊的正字法改革的表面麻烦更麻烦，这也是为什么这也可能是一个重要考虑因素，这取决于您居住在哪个国家。无论如何，最好在那里存放这个整数，以防万一......
编辑：
之后加入ON DELETE CASCADE
REFERENCES [dbo].[T_Products] ([PROD_Id])
你可以简单地说： DELETE FROM T_Products ，并且没有违反外键。
至于整理，我会这样做：
A）有你自己的DAL
B）在语言表中保存所需的排序规则名称
您可能希望将排序规则放在自己的表中，例如：
SELECT * FROM sys.fn_helpcollations()
WHERE description LIKE '%insensitive%'
AND name LIKE '%german%'
C）在auth.user.language信息中提供排序规则名称
D）像这样写你的SQL：
SELECT
COALESCE(GRP_Name_i18n_cust, GRP_Name_i18n, GRP_Name) AS GroupName
FROM T_Groups
ORDER BY GroupName COLLATE {#COLLATION}
E）然后，您可以在DAL中执行此操作：
cmd.CommandText = cmd.CommandText.Replace("{#COLLATION}", auth.user.language.collation)
然后它会给你这个完美组合的SQL-Query
SELECT
COALESCE(GRP_Name_i18n_cust, GRP_Name_i18n, GRP_Name) AS GroupName
FROM T_Groups
ORDER BY GroupName COLLATE German_PhoneBook_CI_AI