Я разрабатываю многоязычное программное обеспечение. Что касается кода приложения, то локализуемость не является проблемой. Мы можем использовать языковые ресурсы и иметь всевозможные инструменты, которые хорошо работают с ними.
Но каков наилучший подход при определении схемы многоязыковой базы данных? Скажем, у нас много таблиц (100 или более), и каждая таблица может иметь несколько столбцов, которые могут быть локализованы (большинство столбцов nvarchar должно быть локализуемым). Например, одна из таблиц может содержать информацию о продукте:
Я могу представить три подхода к поддержке многоязычного текста в столбцах NAME и DESCRIPTION:
Для каждого решения есть плюсы и минусы, и я хотел бы знать, каковы ваши опыты с этими подходами, что вы рекомендуете и как бы вы приступили к разработке схемы многоязычной базы данных.
Ответ 3
Это интересный вопрос, так что давай некроманты.
Начнем с задач метода 1:
Проблема: Вы денормализуете, чтобы сохранить скорость.
В SQL (кроме PostGreSQL с hstore) вы не можете передать язык параметров и сказать:
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 @[email protected] + 'AND (Building_UID = ''' + @in_building + ''') '
IF @in_wing <> '00000000-0000-0000-0000-000000000000' SET @[email protected] + 'AND (Wing_UID = ''' + @in_wing + ''') '
EXECUTE (@sql)
END
GO
Проблема с этим
а) Форматирование даты зависит от языка, поэтому у вас возникает проблема, если вы не вводите в формате ISO (чего обычно не делает средний программист, работающий с садом, а в случае отчета пользователь уверен как, черт возьми, не будет делать для вас, даже если явно указано, чтобы сделать это).
а также
б) самое главное, вы теряете любой вид проверки синтаксиса. Если <insert name of your "favourite" person here> изменяет схему, потому что внезапно требования к крылу меняются, и создается новая таблица, старая остается, но поле ссылки переименовывается, вы не получаете никаких предупреждений. Отчет даже работает, когда вы запускаете его без выбора параметра wing (==> guid.empty). Но неожиданно, когда реальный пользователь фактически выбирает крыло ==> бум. Этот метод полностью нарушает любой вид тестирования.
Способ 2:
В двух словах: "Отличная" идея (предупреждение - сарказм), давайте объединим недостатки метода 3 (низкая скорость при большом количестве записей) с довольно ужасными недостатками метода 1.
Единственным преимуществом этого метода является то, что вы храните все переводы в одной таблице и, следовательно, делаете обслуживание простым. Однако то же самое может быть достигнуто с помощью метода 1 и хранимой процедуры динамического SQL, а также (возможно, временной) таблицы, содержащей переводы и имя целевой таблицы (и это довольно просто, если вы назвали все свои текстовые поля так же).
Способ 3:
Одна таблица для всех переводов: Недостаток: Вы должны хранить n внешних ключей в таблице продуктов для n полей, которые вы хотите перевести. Следовательно, вы должны сделать n объединений для n полей. Когда таблица перевода является глобальной, в ней много записей, и соединения становятся медленными. Кроме того, вы всегда должны присоединиться к таблице T_TRANSLATION n раз для n полей. Это довольно накладные расходы. Теперь, что вы делаете, когда вы должны приспособить индивидуальные переводы для каждого клиента? Вам нужно будет добавить еще 2x n объединений на дополнительную таблицу. Если вам нужно объединить, скажем, 10 таблиц с 2x2xn = 4n дополнительных объединений, что за беспорядок! Также этот дизайн позволяет использовать один и тот же перевод с 2 таблицами. Если я изменю имя элемента в одной таблице, действительно ли я хочу изменить запись в другой таблице также КАЖДЫЙ ОДИН РАЗ?
Кроме того, вы больше не можете удалять и повторно вставлять таблицу, потому что в ТАБЛИЦЕ ПРОДУКТА теперь есть внешние ключи... вы, конечно, можете опустить установку FK, а затем <insert name of your "favourite" person here> может удалить таблицу и повторно вставить все записи с помощью newid() [или путем указания идентификатора в вставке, но с идентификационной вставкой OFF ], и это приведет (и приведет) к мусору данных (и нулю Ссылка исключения) очень скоро.
Способ 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
Затем вы можете получить значение с помощью XPath-Query в SQL, где вы можете поместить строковую переменную в
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
Где вы можете заменить /lang/de/... на '.../' + @in_language + '/...'
В некотором роде, как в PostGre hstore, за исключением того, что из-за накладных расходов при разборе XML (вместо чтения записи из ассоциативного массива в PG hstore) он становится слишком медленным, а кодирование xml делает его слишком болезненным, чтобы быть полезным.
Метод 5 (в соответствии с рекомендациями SunWuKung, который вы должны выбрать): одна таблица перевода для каждой таблицы "Продукт". Это означает одну строку на язык и несколько "текстовых" полей, поэтому требуется только ОДНО (слева) соединение по N полей. Затем вы можете легко добавить поле по умолчанию в "Продукт" -table, вы можете легко удалить и повторно вставить таблицу перевода, и вы можете создать вторую таблицу для пользовательских переводов (по запросу), которую вы также можете удалите и вставьте заново), и у вас все еще есть все внешние ключи. Давайте сделаем пример, чтобы увидеть это работает:
Сначала создайте таблицы:
CREATE TABLE dbo.T_Languages
(
Lang_ID int NOT NULL
,Lang_NativeName national character varying(200) NULL
,Lang_EnglishName national character varying(200) NULL
,Lang_ISO_TwoLetterName character varying(10) NULL
,CONSTRAINT PK_T_Languages PRIMARY KEY ( Lang_ID )
);
GO
CREATE TABLE dbo.T_Products
(
PROD_Id int NOT NULL
,PROD_InternalName national character varying(255) NULL
,CONSTRAINT PK_T_Products PRIMARY KEY ( PROD_Id )
);
GO
CREATE TABLE dbo.T_Products_i18n
(
PROD_i18n_PROD_Id int NOT NULL
,PROD_i18n_Lang_Id int NOT NULL
,PROD_i18n_Text national character varying(200) NULL
,CONSTRAINT PK_T_Products_i18n PRIMARY KEY (PROD_i18n_PROD_Id, PROD_i18n_Lang_Id)
);
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
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_Products
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 national character varying(200) NULL
,CONSTRAINT PK_T_Products_i18n_Cust PRIMARY KEY ( PROD_i18n_Cust_PROD_Id, PROD_i18n_Cust_Lang_Id )
);
GO
ALTER TABLE dbo.T_Products_i18n_Cust
ADD CONSTRAINT FK_T_Products_i18n_Cust_T_Languages
FOREIGN KEY(PROD_i18n_Cust_Lang_Id)
REFERENCES dbo.T_Languages (Lang_ID)
ALTER TABLE dbo.T_Products_i18n_Cust CHECK CONSTRAINT FK_T_Products_i18n_Cust_T_Languages
GO
ALTER TABLE dbo.T_Products_i18n_Cust
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' и т.д.) В качестве первичного ключа языковой таблицы, тогда вам не нужно искать идентификатор языка. Но если вы сделаете это, вы, возможно, захотите использовать вместо этого тег IETF-language, что лучше, потому что вы получаете de-CH и de-DE, что на самом деле не одинаково для орфографии (double s вместо ß везде), хотя это тот же базовый язык. Это очень маленькая деталь, которая может быть важна для вас, особенно если учесть, что en-US и en-GB/en-CA/en-AU или fr-FR/fr-CA имеют схожие проблемы.
Цитата: нам это не нужно, мы делаем наши программы только на английском языке.
Ответ: да, но какой?
В любом случае, если вы используете целочисленный идентификатор, вы гибки и можете изменить свой метод в любое время.
И вы должны использовать это целое число, потому что нет ничего более раздражающего, разрушительного и хлопотного, чем неудачный дизайн 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 и не получить никакого нарушения внешнего ключа.
Что касается сопоставления, я бы сделал это так:
А) Имейте свой собственный DAL
Б) Сохраните желаемое имя сопоставления в языковой таблице
Возможно, вы захотите поместить параметры сортировки в их собственную таблицу, например:
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-запрос
SELECT
COALESCE(GRP_Name_i18n_cust, GRP_Name_i18n, GRP_Name) AS GroupName
FROM T_Groups
ORDER BY GroupName COLLATE German_PhoneBook_CI_AI
