Рекомендации по локализации базы данных SQL Server (2005/2008)

Я не понимаю, зачем вам нужно несколько текстовых таблиц. Одной текстовой таблицы с «глобальным» уникальным текстовым идентификатором должно быть достаточно. Таблица будет иметь идентификатор, язык, текстовые столбцы, и вы всегда будете получать текст только на том языке, который вам нужно представить (или, возможно, не извлекать текст вообще). Соединение должно быть достаточно эффективным, поскольку комбинация (ID, язык) является первичным ключом.

Это один из вопросов, на который сложно ответить, потому что в ответе так много «зависит от обстоятельств» :-)

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

В общих чертах я обычно использую один из двух подходов:

1. Храните локализованные элементы рядом с исполняемым файлом (локализованные библиотеки DLL с исходным кодом)
2. Сохраните локализованные элементы в базе данных и введите столбец localeID в таблицы, содержащие локализованные элементы.

Преимущество первого способа - хорошая поддержка VisualStudio. Преимущество второго - централизованное развертывание.

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

Я бы предложил использовать столбец CultureID в каждой таблице, в которой есть локализуемые элементы. Таким образом, вам вообще не понадобится обработка XML. У вас уже есть данные в реляционной схеме, так зачем вводить еще один уровень сложности, если реляционная схема вполне способна справиться с проблемой?

Скажем, "sv-SE" имеет CultureID = 1, а "en-EN" - 2.

Тогда ваш запрос будет изменен как

SELECT *
From Product
Where Product.ID = 10 AND Product.cultureID = 1

для шведского клиента.

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

И еще один момент: XML-решение дает вам гибкость, в которой вы не нуждаетесь: вы можете, например, взять значение «sv-SE» из столбца «Имя» и значение «en-EN» из столбца « Описание "-колонка. Однако вам это не нужно, поскольку ваш клиент будет запрашивать только одну культуру за раз. За гибкость обычно приходится платить. В этом случае вам нужно проанализировать все столбцы по отдельности, в то время как с помощью решения CultureID вы получите всю запись со всеми значениями, соответствующими запрошенной культуре.

Я думаю, вы можете придерживаться XML, который позволяет сделать дизайн более чистым. Я бы пошел дальше и воспользовался атрибутом xml:lang, который предназначен для этого использования:

<l10n>
  <text xml:lang = "sv-SE">Detta är ett namn</text>
  <text xml:lang = "en-EN">This is a name</text>
</l10n>

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

SELECT Name.value('(l10n/text[lang() = "en"])[1]', 'NVARCHAR(MAX)')
  FROM Product
  WHERE Product.ID=10;

Обратите внимание, что это решение было бы элегантным, но менее эффективным решением, чем решение с отдельной таблицей. Что может быть нормально для некоторых приложений.

Мне нравится подход XML, потому что решение с отдельной таблицей НЕ вернет результат, если, например, нет шведского перевода (cultureID = 1), если вы не выполняете внешнее соединение. Но, тем не менее, вы НЕ можете вернуться к английскому языку. Используя подход XML, вы просто можете вернуться к английскому языку. Есть новости о подходе XML в производственной среде?

Вот несколько моментов в блоге Рика Страла:

Локализация базы данныхЛокализация JavaScript

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

CREATE TABLE [dbo].[Lang_en_US_Msg](
    [MsgId] [int] IDENTITY(1,1) NOT NULL,
    [MsgKey] [varchar](200) NOT NULL,
    [MsgTxt] [varchar](2000) NOT NULL,
    [MsgDescription] [varchar](2000) NOT NULL,
 CONSTRAINT [PK_Lang_US-us__Msg] PRIMARY KEY CLUSTERED 
(
    [MsgId] 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].[User](
    [UserId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [MiddleName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [DomainName] [varchar](50) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[UserSetting](
    [UserSettingId] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [CultureInfo] [varchar](50) NOT NULL,
    [GuiLanguage] [varchar](10) NOT NULL,
 CONSTRAINT [PK_UserSetting] PRIMARY KEY CLUSTERED 
(
    [UserSettingId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ИДТИ

 ALTER TABLE [dbo].[UserSetting] ADD  CONSTRAINT [DF_UserSetting_CultureInfo]  DEFAULT ('fi-FI') FOR [CultureInfo]
 GO

 CREATE TABLE [dbo].[Lang_fi_FI_Msg](
    [MsgId] [int] IDENTITY(1,1) NOT NULL,
    [MsgKey] [varchar](200) NOT NULL,
    [MsgTxt] [varchar](2000) NOT NULL,
    [MsgDescription] [varchar](2000) NOT NULL,
    [DbSysNameForExpansion] [varchar](50) NULL,
 CONSTRAINT [PK_Lang_Fi-fi__Msg] PRIMARY KEY CLUSTERED 
(
    [MsgId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE PROCEDURE [dbo].[procGui_GetPageMsgs]
@domainUser varchar(50) ,           -- the domain_user performing the action  
@msgOut varchar(4000) OUT,        -- the (error) msg to be shown to the user   
@debugMsgOut varchar(4000) OUT ,   -- this variable holds the debug msg to be shown if debug level is enabled   
@ret int OUT                  -- the variable indicating success or failure 

AS                            
BEGIN -- proc start                            
 SET NOCOUNT ON;                            

declare @procedureName varchar(200)        
declare @procStep varchar(4000)  


set @procedureName = ( SELECT OBJECT_NAME(@@PROCID))        
set @msgOut = ' '     
set @debugMsgOut = ' '     
set @procStep = ' '     


BEGIN TRY        --begin try                  
set @ret = 1 --assume false from the beginning                  

--===============================================================
 --debug   set @procStep=@procStep + 'GETTING THE GUI LANGUAGE FOR THIS USER '
--===============================================================

declare @guiLanguage nvarchar(10)




if ( @domainUser is null)
    set @guiLanguage = (select Val from AppSetting where Name='guiLanguage')
else 
    set @guiLanguage = (select GuiLanguage from UserSetting us join [User] u on u.UserId = us.UserId where u.DomainName=@domainUser)

set @guiLanguage = REPLACE ( @guiLanguage , '-' , '_' ) ;


--===============================================================
set @procStep=@procStep + ' BUILDING THE SQL QUERY '
--===============================================================

DECLARE @sqlQuery AS nvarchar(2000)
SET @sqlQuery = 'SELECT  MsgKey , MsgTxt FROM dbo.lang_' + @guiLanguage + '_Msg'


--===============================================================
set @procStep=@procStep + 'EXECUTING THE SQL QUERY'
--===============================================================
print @sqlQuery

    exec sp_executesql @sqlQuery

    set @debugMsgOut = @procStep
    set @ret = @@ERROR                  


END TRY        --end try                  

BEGIN CATCH                        
 PRINT 'In CATCH block.                         
 Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) + '                        
 Error message: ' + ERROR_MESSAGE() + '                        
 Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) + '                        
 Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + '                        
 XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10));                        

set @msgOut = 'Failed to execute ' + @sqlQuery             
set @debugMsgOut = ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) +               
 'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) +               
 'Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + 'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10))                        

--record the error in the database                        
--debug    
 --EXEC [dbo].[procUtils_DebugDb]
    --  @DomainUser = @domainUser,
    --  @debugmsg = @debugMsgOut,
    --  @ret = 1,
    --  @procedureName = @procedureName ,
    --  @procedureStep = @procStep

 -- set @ret = 1                       

END CATCH                        


return  @ret                                   
END --procedure end                             

Я вижу общий разделитель - у вас есть одна сущность, которую вы должны представить как один экземпляр (например, один ProductID со значением «10»), но у вас есть несколько локализованных текстов разных столбцов / свойств. Это сложный вопрос, и я действительно вижу необходимость в POS-системах, в которых вы хотите отслеживать только один ProductID = 10, а не несколько продуктов с разными ProductID, но это одно и то же, только с другим текстом.

Я бы склонился к решению столбца XML, которое вы и другие уже описали здесь. Да, это больше данных, передаваемых по проводам, но это упрощает работу и может быть отфильтровано с помощью XElement, если пакетный сайт становится проблемой.

Основным недостатком является количество данных, передаваемых по проводам из БД на уровень сервиса / UI / App. Я бы попытался выполнить некоторые преобразования в конце SQL, прежде чем возвращать результат, чтобы вернуть только один пользовательский интерфейс культуры. Вы всегда можете просто ВЫБРАТЬ текущую культуру через xml в sproc и вернуть ее как обычный текст.

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

Мой подход к сценарию Post был бы аналогичен подходу TToni, за исключением моделирования данных с точки зрения Домена (и немного BDD). С учетом сказанного, сосредоточьтесь на том, чего вы хотите достичь:

Given a users culture is "sv-se"
When the user views a post list
It should list posts only in "sv-se" culture

Это означает, что пользователь должен видеть список сообщений только для своей культуры. Мы реализовали это раньше, передав набор культур для запроса на основе того, что мог видеть пользователь. Если пользователь установил sv-se в качестве основного, но также выбрал, что они говорят на американском английском (en-us), то запрос будет следующим:

SELECT * FROM Post WHERE CultureUI IN ('sv-se', 'en-us')

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

Но возвращаясь к 1-й части моего ответа, ваша потребность проистекает из требования наличия одного экземпляра с несколькими текстами. Столбец Xml подходит для этого.

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

Я использовал аналогичный подход при создании нескольких тем для моего веб-сайта электронной коммерции. Некоторые продукты имеют логотип производителя, который также должен соответствовать тематике веб-сайта. Поскольку нет реальной поддержки тем в базе данных, у меня возникла проблема. Решение, которое я придумал, заключалось в том, чтобы использовать токен в базе данных для идентификации ClientID изображения, а не хранить URL-адрес изображения (который будет зависеть от темы).

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

Label1.Text = GetLocalResourceObject("TokenStoredInDatabase").ToString()

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

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

Кстати, если вы запускаете магазин электронной коммерции и действительно хотите проиндексировать свои локализованные страницы, вам нужно немного отклониться от, казалось бы, естественного способа, созданного Microsoft. Существует явное несогласие между практическим и логическим процессом проектирования и тем, что Google рекомендует для SEO. Действительно, некоторые веб-мастера жаловались, что их страницы не индексировались поисковыми системами ни для чего, кроме культуры «по умолчанию», потому что поисковые системы индексируют только один URL-адрес один раз, даже если он изменяется в зависимости от культуры браузера.

К счастью, есть простой способ обойти это: разместить ссылки на странице, чтобы перевести ее на другие языки на основе параметра строки запроса. Можно найти пример этого (ой, они не позволят мне опубликовать еще одну ссылку !!), и если вы проверите, каждая культура страницы была проиндексирована как Google, так и Yahoo (но не Bing). Более продвинутый подход может использовать переопределение URL-адресов в сочетании с некоторыми необычными регулярными выражениями, чтобы ваша единственная локализованная страница выглядела так, как будто она имеет несколько каталогов, но вместо этого на самом деле передает на страницу параметр строки запроса.

Индексирование становится проблемой. Я не думаю, что вы можете индексировать xml, и, конечно, вы не можете проиндексировать его, если сохраните его как строку, потому что каждая строка будет начинаться с <localization> <text culture = "...">.

Вот как я это сделал. Я не использую для этого LINQ или SP, потому что запрос слишком сложен и создается динамически, и это всего лишь отрывок из запроса.

У меня есть таблица товаров:

* id
* price
* stocklevel
* active
* name
* shortdescription
* longdescription

и таблица products_globalization:

* id
* products_id
* name
* shortdescription
* longdescription

Как видите, таблица продуктов также содержит все столбцы глобализации. Эти столбцы содержат язык по умолчанию (таким образом, возможность пропустить соединение при запросе культуры по умолчанию - НО я не уверен, стоит ли это того, я имею в виду, что соединение между двумя таблицами основано на индексе. .. - дайте мне обратную связь по этому поводу).

Я предпочитаю иметь параллельную таблицу, а не глобальную таблицу ресурсов, потому что в определенных ситуациях вам может потребоваться выполнить, например, ПОИСКПОЗ (MySQL) по паре столбцов, например ПОИСКПОЗ (имя, краткое описание, длинное описание) ПРОТИВ (' Что-то здесь ').

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

Псевдо:

string query = "";
if (string.IsNullOrEmpty(culture)) {
   // No culture specified, no join needed.
   query = "SELECT p.price, p.name, p.shortdescription FROM products p WHERE p.price > ?Price";
} else {
   query = "SELECT p.price, case when pg.name is null then p.name else pg.name end as 'name', case when pg.shortdescription is null then p.shortdescription else pg.shortdescription end as 'shortdescription' FROM products p"
   + " LEFT JOIN products_globalization pg ON pg.products_id = p.id AND pg.culture = ?Culture"
   + " WHERE p.price > ?Price";
}

Я бы выбрал COALESCE вместо CASE ELSE, но не в этом дело.

Ну, вот и мое мнение. Не стесняйтесь критиковать мое предложение ...

С уважением, Ричард