Esquema para um database multilíngüe

Estou desenvolvendo um software multilíngüe. No que diz respeito ao código da aplicação, a localização não é um problema. Podemos usar resources específicos da linguagem e ter todos os tipos de ferramentas que funcionam bem com eles.

Mas qual é a melhor abordagem na definição de um esquema de database multilíngüe? Digamos que temos muitas tabelas (100 ou mais) e cada tabela pode ter várias colunas que podem ser localizadas (a maioria das colunas nvarchar deve ser localizável). Por exemplo, uma das tabelas pode conter informações sobre o produto:

CREATE TABLE T_PRODUCT ( NAME NVARCHAR(50), DESCRIPTION NTEXT, PRICE NUMBER(18, 2) ) 

Posso pensar em três abordagens para suportar texto multilíngüe nas colunas NAME e DESCRIPTION:

  1. Coluna separada para cada idioma

    Quando adicionamos um novo idioma ao sistema, devemos criar colunas adicionais para armazenar o texto traduzido, assim:

     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) ) 
  2. Tabela de tradução com colunas para cada idioma

    Em vez de armazenar texto traduzido, apenas uma chave estrangeira para a tabela de traduções é armazenada. A tabela de traduções contém uma coluna para cada idioma.

     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 ) 
  3. Tabelas de tradução com linhas para cada idioma

    Em vez de armazenar texto traduzido, apenas uma chave estrangeira para a tabela de traduções é armazenada. A tabela de traduções contém apenas uma chave e uma tabela separada contém uma linha para cada tradução para um idioma.

     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) ) 

Há prós e contras em cada solução, e gostaria de saber quais são suas experiências com essas abordagens, o que você recomenda e como você faria para projetar um esquema de database multilíngüe.

O que você acha de ter uma tabela de tradução relacionada para cada tabela traduzível?

CREATE TABLE T_PRODUCT (pr_id int, NÚMERO DE PREÇO (18, 2))

CREATE TABLE T_PRODUCT_tr (pr_id INT FK, varagecode varchar, texto pr_nome, texto pr_descr)

Dessa forma, se você tiver várias colunas traduzíveis, será necessário apenas uma única associação para obtê-la, já que você não está gerando automaticamente um ID de conversão, pode ser mais fácil importar itens junto com as respectivas traduções.

O lado negativo disso é que, se você tiver um mecanismo de fallback de linguagem complexa, pode ser necessário implementar isso para cada tabela de conversão – se estiver contando com algum procedimento armazenado para fazer isso. Se você fizer isso a partir do aplicativo, isso provavelmente não será um problema.

Deixe-me saber o que você pensa – eu também estou prestes a tomar uma decisão sobre isso para a nossa próxima aplicação. Até agora usamos o seu terceiro tipo.

A terceira opção é a melhor, por alguns motivos:

  • Não requer alterar o esquema do database para novos idiomas (e, portanto, limitar as alterações no código)
  • Não requer muito espaço para idiomas não implementados ou traduções de um item específico
  • Fornece a maior flexibilidade
  • Você não acaba com tabelas esparsas
  • Você não precisa se preocupar com chaves nulas e verificar se está exibindo uma tradução existente em vez de alguma input nula.
  • Se você alterar ou expandir seu database para abranger outros itens / coisas / etc traduzíveis, você pode usar as mesmas tabelas e sistema – isso é muito desacoplado do resto dos dados.

-Adão

Esta é uma questão interessante, então vamos necromance.

Vamos começar pelos problemas do método 1:
Problema: Você está desnormalizando para economizar velocidade.
No SQL (exceto PostGreSQL com hstore), você não pode passar um idioma de parâmetro e dizer:

 SELECT ['DESCRIPTION_' + @in_language] FROM T_Products 

Então você tem que fazer isso:

 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 

O que significa que você precisa alterar TODAS as suas consultas se adicionar um novo idioma. Isso naturalmente leva ao uso de “SQL dynamic”, para que você não precise alterar todas as suas consultas.

Isso geralmente resulta em algo assim (e não pode ser usado em visualizações ou funções com valor de tabela, o que realmente é um problema se você realmente precisa filtrar a data do relatório)

 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 

O problema com isso é
a) A formatação da data é muito específica do idioma, então você tem um problema lá, se você não inserir no formato ISO (o que o programador comum de jardim geralmente não faz, e no caso de um relatório o usuário tem certeza como o inferno não vai fazer por você, mesmo que explicitamente instruído a fazê-lo).
e
b) mais significativamente , você perde qualquer tipo de verificação de syntax . Se alterar o esquema porque, de repente, os requisitos para mudança de asa e uma nova tabela forem criados, o antigo deixado, mas o campo de referência renomeado, você não recebe nenhum tipo de aviso. Um relatório funciona mesmo quando você o executa sem selecionar o parâmetro wing (==> guid.empty). Mas, de repente, quando um usuário real realmente seleciona uma lança ==>. Este método quebra completamente qualquer tipo de teste.


Método 2:
Em suma: “Grande” idéia (aviso – sarcasmo), vamos combinar as desvantagens do método 3 (velocidade lenta quando muitas inputs) com as desvantagens bastante horríveis do método 1.
A única vantagem desse método é que você mantém toda a tradução em uma tabela e, portanto, simplifica a manutenção. No entanto, a mesma coisa pode ser obtida com o método 1 e um procedimento armazenado SQL dynamic e uma tabela (possivelmente temporária) contendo as traduções e o nome da tabela de destino (e é bastante simples supondo que você nomeou todos os campos de texto como mesmo).


Método 3:
Uma tabela para todas as traduções: Desvantagem: Você precisa armazenar n foreign keys na tabela de produtos para n campos que deseja traduzir. Portanto, você precisa fazer n junções para n campos. Quando a tabela de conversão é global, ela possui muitas inputs e as junções ficam lentas. Além disso, você sempre tem que ingressar na tabela T_TRANSLATION n vezes para n campos. Isso é uma sobrecarga. Agora, o que você faz quando precisa acomodar traduções personalizadas por cliente? Você terá que adicionar mais 2x n junções em uma tabela adicional. Se você tem que participar, digamos 10 mesas, com 2x2xn = 4n junções adicionais, que confusão! Além disso, este design permite usar a mesma tradução com 2 tabelas. Se eu alterar o nome do item em uma tabela, eu realmente quero alterar uma input em outra tabela, bem como cada vez único?

Além disso, você não pode mais excluir e reinserir a tabela, pois agora há foreign keys NA (S) TABELA (S) DO PRODUTO … você pode, obviamente, omitir a configuração dos FKs e pode deletar a tabela, e reinseri todas as inputs com newid () [ou especificando o id na inserção, mas tendo o inserto de identidade OFF ], e isso iria (e irá) levar ao lixo de dados (e null exceções de referência) muito em breve.


Método 4 (não listado): Armazenando todos os idiomas em um campo XML no database. por exemplo

 -- 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"?>Hello', 2) --,CONVERT(XML, N'< ?xml version="1.0" encoding="utf-16" standalone="yes"?>Hello', 2) ,CONVERT(XML , N'< ?xml version="1.0" encoding="utf-16" standalone="yes"?>  Deutsch Français Ital&iano English  ' , 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 

Então você pode obter o valor por XPath-Query no SQL, onde você pode colocar a variável string em

 filemeta.value('(/lang//' + @in_language + '/node())[1]', 'nvarchar(MAX)') AS bla 

E você pode atualizar o valor assim:

 UPDATE YOUR_TABLE SET YOUR_XML_FIELD_NAME.modify('replace value of (/lang/de/text())[1] with ""I am a ''value ""') WHERE id = 1 

Onde você pode replace /lang/de/... com '.../' + @in_language + '/...'

Mais ou menos como o PostGre hstore, exceto que, devido à sobrecarga de análise XML (em vez de ler uma input de um array associativo em PG hstore), ele se torna muito lento, mais a codificação xml torna muito doloroso ser útil.


Método 5 (como recomendado por SunWuKung, aquele que você deve escolher): Uma tabela de conversão para cada tabela “Produto”. Isso significa uma linha por idioma e vários campos de “texto”, por isso requer apenas UM (à esquerda) para associar em N campos. Então você pode facilmente adicionar um campo padrão na tabela “Produto”, você pode facilmente excluir e reinserir a tabela de tradução, e você pode criar uma segunda tabela para traduções personalizadas (sob demanda), que você também pode excluir e reinserir), e você ainda tem todas as foreign keys.

Vamos dar um exemplo para ver estas OBRAS:

Primeiro, crie as tabelas:

 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 

Em seguida, preencha os dados

 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 

Em seguida, consulte os dados:

 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 (eg 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 

Se você é preguiçoso, então você também pode usar o ISO-TwoLetterName (‘DE’, ‘EN’, etc.) como chave primária da tabela de idiomas, então você não precisa procurar o id do idioma. Mas se você fizer isso, talvez queira usar a tag IETF-language , o que é melhor, porque você obtém de-CH e de-DE, o que não é o mesmo ortograficamente (double s ao invés de ß em todos os lugares) , embora seja a mesma linguagem de base. Isso, assim como um minúsculo detalhe que pode ser importante para você, especialmente considerando que en-US e en-GB / en-CA / en-AU ou fr-FR / fr-CA têm problemas semelhantes.
Citação: não precisamos disso, só fazemos nosso software em inglês.
Resposta: Sim – mas qual?

De qualquer forma, se você usar um ID inteiro, você é flexível e pode alterar seu método a qualquer momento posterior.
E você deve usar esse número inteiro, porque não há nada mais irritante, destrutivo e problemático do que um projeto Db com falha.

Veja também RFC 5646 , ISO 639-2 ,

E, se você ainda está dizendo “nós” apenas fazemos nossa aplicação para “apenas uma cultura” (como en-US normalmente) – portanto, eu não preciso desse inteiro extra, este seria um bom momento e lugar para mencionar o Tags de idioma da IANA , não é?
Porque eles são assim:

 de-DE-1901 de-DE-1996 

e

 de-CH-1901 de-CH-1996 

(houve uma reforma ortográfica em 1996 …) Tente encontrar uma palavra em um dictionary se for digitada incorretamente; isso se torna muito importante em aplicativos que lidam com portais legais e de serviço público.
Mais importante ainda, há regiões que estão mudando de alfabetos cirílicos para latinos, o que pode ser mais problemático do que o incômodo superficial de alguma reforma ortográfica obscura, e é por isso que essa também pode ser uma consideração importante, dependendo do país em que você vive. De um jeito ou de outro, é melhor ter aquele inteiro lá, por via das dúvidas …

Editar:
E adicionando ON DELETE CASCADE depois

 REFERENCES [dbo].[T_Products] ([PROD_Id]) 

você pode simplesmente dizer: DELETE FROM T_Products , e não obter nenhuma violação de chave estrangeira.

Quanto ao agrupamento, eu faria assim:

A) Ter seu próprio DAL
B) Salve o nome de agrupamento desejado na tabela de idiomas

Você pode querer colocar os agrupamentos em sua própria tabela, por exemplo:

 SELECT * FROM sys.fn_helpcollations() WHERE description LIKE '%insensitive%' AND name LIKE '%german%' 

C) Tenha o nome do agrupamento disponível em sua informação auth.user.language

D) Escreva seu SQL assim:

 SELECT COALESCE(GRP_Name_i18n_cust, GRP_Name_i18n, GRP_Name) AS GroupName FROM T_Groups ORDER BY GroupName COLLATE {#COLLATION} 

E) Então, você pode fazer isso no seu DAL:

 cmd.CommandText = cmd.CommandText.Replace("{#COLLATION}", auth.user.language.collation) 

Que então lhe dará este SQL-Query perfeitamente composto

 SELECT COALESCE(GRP_Name_i18n_cust, GRP_Name_i18n, GRP_Name) AS GroupName FROM T_Groups ORDER BY GroupName COLLATE German_PhoneBook_CI_AI 

Dê uma olhada neste exemplo:

 PRODUCTS ( id price created_at ) LANGUAGES ( id title ) TRANSLATIONS ( id (// id of translation, UNIQUE) language_id (// id of desired language) table_name (// any table, in this case PRODUCTS) item_id (// id of item in PRODUCTS) field_name (// fields to be translated) translation (// translation text goes here) ) 

Eu acho que não há necessidade de explicar, a estrutura descreve a si mesma.

Eu normalmente usaria essa abordagem (não é sql real), isso corresponde à sua última opção.

 table Product productid INT PK, price DECIMAL, translationid INT FK table Translation translationid INT PK table TranslationItem translationitemid INT PK, translationid INT FK, text VARCHAR, languagecode CHAR(2) view ProductView select * from Product inner join Translation inner join TranslationItem where languagecode='en' 

Porque ter todos os textos traduzíveis em um só lugar torna a manutenção muito mais fácil. Às vezes, as traduções são terceirizadas para agências de tradução, dessa forma você pode enviar apenas um grande arquivo de exportação e importá-lo de volta com a mesma facilidade.

Antes de ir para detalhes técnicos e soluções, você deve parar por um minuto e fazer algumas perguntas sobre os requisitos. As respostas podem ter um impacto enorme na solução técnica. Exemplos de tais questões seriam:
– Todas as línguas serão usadas o tempo todo?
– Quem e quando preencherá as colunas com as diferentes versões linguísticas?
– O que acontece quando um usuário precisa de um determinado idioma de um texto e não há nenhum no sistema?
– Apenas os textos devem ser localizados ou há também outros itens (por exemplo, o preço pode ser armazenado em $ e €, porque eles podem ser diferentes)

Eu estava procurando algumas dicas para localização e encontrei este tópico. Eu estava me perguntando por que isso é usado:

 CREATE TABLE T_TRANSLATION ( TRANSLATION_ID ) 

Então você recebe algo como o user39603 sugere:

 table Product productid INT PK, price DECIMAL, translationid INT FK table Translation translationid INT PK table TranslationItem translationitemid INT PK, translationid INT FK, text VARCHAR, languagecode CHAR(2) view ProductView select * from Product inner join Translation inner join TranslationItem where languagecode='en' 

Você não pode simplesmente deixar a tabela de tradução para que você obtenha isso:

  table Product productid INT PK, price DECIMAL table ProductItem productitemid INT PK, productid INT FK, text VARCHAR, languagecode CHAR(2) view ProductView select * from Product inner join ProductItem where languagecode='en' 

Eu concordo com o randomizador. Eu não vejo porque você precisa de uma tabela “tradução”.

Eu acho que isso é o suficiente:

 TA_product: ProductID, ProductPrice TA_Language: LanguageID, Language TA_Productname: ProductnameID, ProductID, LanguageID, ProductName 

A abordagem abaixo seria viável? Digamos que você tenha tabelas em que mais de uma coluna precise ser traduzida. Portanto, para o produto, você pode ter o nome do produto e a descrição do produto que precisam ser traduzidos. Você poderia fazer o seguinte:

 CREATE TABLE translation_entry ( translation_id int, language_id int, table_name nvarchar(200), table_column_name nvarchar(200), table_row_id bigint, translated_text ntext ) CREATE TABLE translation_language ( id int, language_code CHAR(2) ) 

“Qual é o melhor” é baseado na situação do projeto. O primeiro é fácil de selecionar e manter, e também o desempenho é melhor, já que ele não precisa unir tabelas quando selecionar entidade. Se você confirmou que seu poject é apenas suporte a 2 ou 3 idiomas, e não irá aumentar, você pode usá-lo.

O segundo é óbvio, mas é difícil de entender e manter. E o desempenho é pior que o primeiro.

O último é bom em escalabilidade, mas ruim em desempenho. A tabela T_TRANSLATION_ENTRY ficará cada vez maior, é terrível quando você deseja recuperar uma lista de entidades de algumas tabelas.