Schemat wielojęzycznej bazy danych

Tworzę oprogramowanie wielojęzyczne. Jeśli chodzi o kod aplikacji, lokalizacja nie jest problemem. Możemy korzystać z zasobów specyficznych dla języka i mieć wszelkiego rodzaju narzędzia, które dobrze z nimi współpracują.

Ale jakie jest najlepsze podejście do definiowania wielojęzycznego schematu bazy danych? Załóżmy, że mamy wiele tabel (100 lub więcej) i każda tabela może mieć wiele kolumn, które można zlokalizować (większość kolumn nvarchar powinna być zlokalizowana). Na przykład jedna z tabel może zawierać informacje o produkcie:

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

Mogę wymyślić trzy podejścia do obsługi tekstu wielojęzycznego w kolumnach NAME i DESCRIPTION:

1. Oddzielna kolumna dla każdego języka

   Kiedy dodajemy nowy język do systemu, musimy utworzyć dodatkowe kolumny do przechowywania przetłumaczonego tekstu, takie jak to:

   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 tłumaczeń z kolumnami dla każdego języka

   Zamiast przechowywać przetłumaczony tekst, przechowywany jest tylko klucz obcy do tabeli tłumaczeń. Tabela tłumaczeń zawiera kolumnę dla każdego języka.

   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. Tabele tłumaczeń z wierszami dla każdego języka

   Zamiast przechowywać przetłumaczony tekst, przechowywany jest tylko klucz obcy do tabeli tłumaczeń. Tabela tłumaczeń zawiera tylko klucz, a osobna tabela zawiera wiersz dla każdego tłumaczenia na język.

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

Każde rozwiązanie ma wady i zalety. Chciałbym wiedzieć, jakie są twoje doświadczenia z tymi podejściami, co polecasz i jak poszedłbyś na temat projektowania wielojęzycznego schematu bazy danych.

Co sądzisz o powiązanej tabeli tłumaczeń dla każdej tabeli możliwej do przetłumaczenia?

UTWÓRZ TABELĘ T_PRODUCT (pr_id int, NUMER CENY (18, 2))

CREATE TABLE T_PRODUCT_tr (pr_id INT FK, vanguar languagecode, tekst pr_name, tekst pr_descr)

W ten sposób, jeśli masz wiele kolumn do przetłumaczenia, wystarczy tylko jedno połączenie, aby je uzyskać +, ponieważ nie generujesz automatycznie translacji, może być łatwiej zaimportować elementy wraz z powiązanymi z nimi tłumaczeniami.

Negatywną stroną tego jest to, że jeśli masz złożony mechanizm zastępczy języka, może być konieczne zaimplementowanie go dla każdej tabeli tłumaczeń - jeśli polegasz na jakiejś procedurze przechowywanej, aby to zrobić. Jeśli zrobisz to z aplikacji, prawdopodobnie nie będzie to problemem.

Daj mi znać, co myślisz - zamierzam również podjąć decyzję w tej sprawie przy kolejnej aplikacji. Do tej pory używaliśmy twojego 3. typu.

To interesująca kwestia, więc zróbmy nekrologię.

Zacznijmy od problemów z metody 1:
Problem: Jesteś denormalizowany, aby zaoszczędzić prędkość.
W SQL (oprócz PostGreSQL z hstore) nie można przekazać języka parametrów i powiedzieć:

SELECT ['DESCRIPTION_' + @in_language]  FROM T_Products

Musisz to zrobić:

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 

Co oznacza, że ​​musisz zmienić WSZYSTKIE swoje zapytania, jeśli dodasz nowy język. To oczywiście prowadzi do korzystania z „dynamicznego SQL”, więc nie musisz zmieniać wszystkich swoich zapytań.

Zwykle powoduje to coś takiego (i nie można jej użyć w widokach lub funkcjach wycenianych w tabeli, co jest naprawdę problemem, jeśli faktycznie trzeba filtrować datę raportu)

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

Problem polega na tym, że
a) Formatowanie daty jest bardzo specyficzne dla języka, więc pojawia się problem, jeśli nie wprowadzisz formatu ISO (czego zwykle nie robi przeciętny programista odmiany ogrodu, a w przypadku zgłoś użytkownika, który z całą pewnością nie zrobi dla ciebie piekła, nawet jeśli zostanie to wyraźnie polecone).
i
b) co najważniejsze , tracisz jakiekolwiek sprawdzanie składni . Jeśli <insert name of your "favourite" person here>zmieni się schemat, ponieważ nagle zmieniają się wymagania dotyczące zmiany skrzydła, i tworzona jest nowa tabela, stara w lewo, ale zmieniono nazwę pola referencyjnego, nie pojawi się żadne ostrzeżenie. Raport działa nawet po uruchomieniu bez wybrania parametru wing (==> guid.empty). Ale nagle, gdy faktyczny użytkownik faktycznie wybiera skrzydło ==> boom boom . Ta metoda całkowicie przełamuje wszelkie testy.

---

Metoda 2:
W skrócie: „Świetny” pomysł (ostrzeżenie - sarkazm), połączmy wady metody 3 (niska prędkość przy wielu wejściach) z dość okropnymi wadami metody 1.
Jedyną zaletą tej metody jest zachowanie wszystkie tłumaczenia w jednej tabeli, dzięki czemu konserwacja jest prosta. To samo można jednak osiągnąć za pomocą metody 1 i dynamicznej procedury składowanej SQL oraz (ewentualnie tymczasowej) tabeli zawierającej tłumaczenia oraz nazwy tabeli docelowej (i jest to dość proste, zakładając, że wszystkie pola tekstowe zostały nazwane podobnie).

---

Metoda 3:
Jedna tabela dla wszystkich tłumaczeń: Wada: Musisz przechowywać n kluczy obcych w tabeli produktów dla n pól, które chcesz przetłumaczyć. Dlatego musisz wykonać n połączeń dla n pól. Gdy tabela translacji jest globalna, zawiera wiele pozycji, a sprzężenia stają się wolne. Ponadto zawsze musisz dołączyć do tabeli T_TRANSLATION n razy dla n pól. To jest dość narzut. Co teraz robisz, gdy musisz uwzględnić niestandardowe tłumaczenia dla każdego klienta? Będziesz musiał dodać kolejne 2x n złączenia do dodatkowego stołu. Jeśli musisz się przyłączyć, powiedzmy 10 tabel, z 2x2xn = 4n dodatkowych złączeń, co za bałagan! Ponadto ten projekt umożliwia użycie tego samego tłumaczenia z 2 tabelami. Jeśli zmienię nazwę elementu w jednej tabeli, czy naprawdę chcę zmienić wpis w innej tabeli KAŻDY JEDEN CZAS?

Ponadto nie można już usuwać i ponownie wstawiać tabeli, ponieważ w tabelach produktów znajdują się teraz klucze obce ... można oczywiście pominąć ustawienie FK, a następnie <insert name of your "favourite" person here>usunąć tabelę i ponownie wstawić wszystkie wpisy za pomocą newid () [lub poprzez podanie identyfikatora we wstawce, ale z wyłączonym identyfikatorem wstawiania ], a to (i doprowadzi) do śmieci (i wyjątków zerowych) naprawdę wkrótce.

---

Metoda 4 (nie wymieniona): Przechowywanie wszystkich języków w polu XML w bazie danych. na przykład

-- 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&amp;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 

Następnie możesz uzyskać wartość przez XPath-Query w SQL, gdzie możesz umieścić zmienną łańcuchową

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

Możesz zaktualizować wartość w następujący sposób:

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

Gdzie można zastąpić /lang/de/...z'.../' + @in_language + '/...'

Coś w rodzaju magazynu PostGre, z wyjątkiem tego, że z powodu narzutu podczas analizowania XML (zamiast odczytywania wpisu z tablicy asocjacyjnej w PG hstore) staje się on zbyt wolny, a kodowanie xml sprawia, że ​​jest zbyt bolesne, aby było przydatne.

---

Metoda 5 (zalecana przez SunWuKung, ta, którą należy wybrać): Jedna tabela tłumaczeń dla każdej tabeli „Produkt”. Oznacza to jeden wiersz na język i kilka pól „tekstowych”, więc wymaga tylko JEDNEGO (lewego) łączenia na N polach. Następnie możesz łatwo dodać pole domyślne w tabeli „Produkt”, możesz łatwo usunąć i ponownie wstawić tabelę tłumaczeń, a także utworzyć drugą tabelę dla tłumaczeń niestandardowych (na żądanie), którą możesz również usunąć i włóż ponownie), a nadal masz wszystkie klucze obce.

Zróbmy przykład, aby zobaczyć to DZIAŁA:

Najpierw utwórz tabele:

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

Następnie wprowadź dane

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

Następnie prześlij zapytanie do danych:

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

Jeśli jesteś leniwy, możesz również użyć ISO-TwoLetterName („DE”, „EN” itp.) Jako klucza podstawowego tabeli językowej, nie musisz szukać identyfikatora języka. Ale jeśli to zrobisz, być może chcesz zamiast tego użyć tagu języka IETF , co jest lepsze, ponieważ dostajesz de-CH i de-DE, co tak naprawdę nie jest takie samo pod względem ortografii (wszędzie podwójne s zamiast ß) , chociaż jest to ten sam język podstawowy. To tak mały drobiazg, który może być dla ciebie ważny, szczególnie biorąc pod uwagę, że en-US i en-GB / en-CA / en-AU lub fr-FR / fr-CA ma podobne problemy.
Cytat: nie potrzebujemy tego, robimy nasze oprogramowanie tylko w języku angielskim.
Odpowiedź: Tak - ale który?

W każdym razie, jeśli użyjesz identyfikatora liczb całkowitych, będziesz elastyczny i możesz zmienić metodę w dowolnym momencie.
I powinieneś użyć tej liczby całkowitej, ponieważ nie ma nic bardziej irytującego, destrukcyjnego i kłopotliwego niż nieudany projekt Db.

Zobacz także RFC 5646 , ISO 639-2 ,

A jeśli nadal mówiąc „my” tylko uczynić nasz wniosek o „tylko jednej kultury” (jak en-US zazwyczaj) - więc nie muszę, że dodatkowy całkowitą, to będzie czas i miejsce, aby wspomnieć o dobrym Tagi językowe IANA , prawda?
Ponieważ idą w ten sposób:

de-DE-1901
de-DE-1996

i

de-CH-1901
de-CH-1996

(w 1996 r. przeprowadzono reformę ortografii). Spróbuj znaleźć słowo w słowniku, jeśli jest ono błędne; staje się to bardzo ważne w aplikacjach związanych z portalami prawnymi i publicznymi.
Co ważniejsze, istnieją regiony, które zmieniają się z alfabetu cyrylicy na alfabety łacińskie, co może być po prostu bardziej kłopotliwe niż powierzchowne utrudnienia związane z jakąś niejasną reformą ortografii, dlatego może to być również ważna kwestia, w zależności od kraju, w którym mieszkasz. Tak czy inaczej, lepiej mieć tam liczbę całkowitą, na wszelki wypadek ...

Edycja:
i dodając ON DELETE CASCADE po

REFERENCES dbo.T_Products( PROD_Id )

możesz po prostu powiedzieć: DELETE FROM T_Productsi nie uzyskać naruszenia klucza obcego.

Jeśli chodzi o zestawienie, zrobiłbym to w ten sposób:

A) Miej swój własny DAL
B) Zapisz żądaną nazwę sortowania w tabeli językowej

Możesz umieścić zestawienia w osobnej tabeli, np .:

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

C) Miej nazwę sortowania dostępną w informacji o języku auth.user.language

D) Napisz swój SQL w ten sposób:

SELECT 
    COALESCE(GRP_Name_i18n_cust, GRP_Name_i18n, GRP_Name) AS GroupName 
FROM T_Groups 

ORDER BY GroupName COLLATE {#COLLATION}

E) Następnie możesz to zrobić w swoim DAL:

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

Który da ci to doskonale skomponowane zapytanie 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

Trzecia opcja jest najlepsza z kilku powodów:

• Nie wymaga zmiany schematu bazy danych dla nowych języków (a tym samym ograniczenia zmian kodu)
• Nie wymaga dużo miejsca na niezaimplementowane języki lub tłumaczenia określonego elementu
• Zapewnia największą elastyczność
• Nie kończysz na rzadkich stołach
• Nie musisz się martwić kluczami zerowymi i sprawdzaniem, czy wyświetlasz istniejące tłumaczenie zamiast niektórych pozycji zerowych.
• Jeśli zmienisz lub rozszerzysz bazę danych, aby objąć inne elementy / rzeczy do przetłumaczenia itp., Możesz użyć tych samych tabel i systemu - jest to bardzo niezwiązane z resztą danych.

-Adam

Spójrz na ten przykład:

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

Myślę, że nie trzeba wyjaśniać, struktura sama się opisuje.

Zwykle wybrałbym takie podejście (nie rzeczywiste sql), to odpowiada twojej ostatniej opcji.

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'

Ponieważ posiadanie wszystkich tekstów do przetłumaczenia w jednym miejscu znacznie ułatwia konserwację. Czasami tłumaczenia są zlecane biurom tłumaczeń, w ten sposób możesz wysłać im tylko jeden duży plik eksportu i równie łatwo zaimportować go z powrotem.

Zanim przejdziesz do szczegółów technicznych i rozwiązań, powinieneś zatrzymać się na chwilę i zadać kilka pytań na temat wymagań. Odpowiedzi mogą mieć ogromny wpływ na rozwiązanie techniczne. Przykładami takich pytań są:
- Czy wszystkie języki będą używane przez cały czas?
- Kto i kiedy wypełni kolumny różnymi wersjami językowymi?
- Co się stanie, gdy użytkownik będzie potrzebował określonego języka tekstu i nie będzie go w systemie?
- Tylko teksty mają być zlokalizowane lub są też inne elementy (na przykład CENA może być przechowywana w $ i €, ponieważ mogą być różne)

Szukałem wskazówek dotyczących lokalizacji i znalazłem ten temat. Zastanawiałem się, dlaczego jest to używane:

CREATE TABLE T_TRANSLATION (
   TRANSLATION_ID
)

Otrzymujesz coś takiego, co sugeruje user39603:

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'

Czy nie możesz po prostu pominąć tłumaczenia Tłumaczenie, aby uzyskać:

    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'

Zgadzam się z randomizatorem. Nie rozumiem, dlaczego potrzebujesz tabeli „tłumaczenie”.

Myślę, że to wystarczy:

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

Czy poniższe podejście byłoby wykonalne? Załóżmy, że masz tabele, w których więcej niż 1 kolumna wymaga tłumaczenia. Tak więc dla produktu możesz mieć zarówno nazwę produktu, jak i opis produktu, które wymagają tłumaczenia. Czy możesz wykonać następujące czynności:

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

„Który jest najlepszy” zależy od sytuacji w projekcie. Pierwszy z nich jest łatwy do wybrania i utrzymania, a także wydajność jest najlepsza, ponieważ nie trzeba łączyć tabel przy wyborze encji. Jeśli potwierdziłeś, że twój poemat obsługuje tylko 2 lub 3 języki i nie wzrośnie, możesz go użyć.

Drugi jest w porządku, ale jest trudny do zrozumienia i utrzymania. A wydajność jest gorsza niż pierwsza.

Ten ostatni jest dobry w skalowalności, ale zły w wydajności. Tabela T_TRANSLATION_ENTRY będzie się powiększać, to okropne, gdy chcesz pobrać listę bytów z niektórych tabel.

W tym dokumencie opisano możliwe rozwiązania oraz zalety i wady każdej metody. Wolę „lokalizację wiersza”, ponieważ podczas dodawania nowego języka nie trzeba modyfikować schematu DB.