Лучшие практики для локализации базы данных SQL Server (2005/2008)

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

Это - один из вопросов, на которые трудно ответить, потому что существуют так многие, "он зависит" в ответе :-)

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

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

1. Сохраните локализованные объекты около исполняемого файла (локализованный ресурс dlls)
2. Сохраните локализованные объекты в DB и представьте localeID столбец в таблицах, которые содержат локализованные объекты.

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

Я думаю, что можно придерживаться 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 приближаются в producitve среде?

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

Я предложил бы использовать cultureID-столбец в каждой таблице, которая имеет локализуемые объекты. Тем путем Вам не нужна никакая XML-обработка вообще. У Вас уже есть свои данные в реляционной схеме итак, почему представляют другой слой сложности, когда реляционная схема совершенно способна к решению проблемы?

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

Затем Ваш запрос был бы изменен как

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

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

Это решение я часто видел в локализованных базах данных. Это масштабируется хорошо и с количеством культур и с количеством datarecords. Это старается не XML-анализировать и обрабатывать и легко реализовать.

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

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

Локализация базы данных Локализация 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]

GO

 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, но это одно и то же, только с другим текстом.

I склоняется к решению столбца 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 не так важен, потому что каждое сообщение привязано к разному языку. Если есть транскрибируемые копии, то это тоже прекрасно работает, так как каждый пост уникален для этой культуры и, следовательно, получает уникальный набор комментариев и тому подобное.

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

но также выбрано, что они говорят на американском английском (en-us), тогда запрос будет следующим:

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

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

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

но также выбрано, что они говорят на американском английском (en-us), тогда запрос будет следующим:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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