Schéma d'une base de données multilingue

Question

Je suis le développement d'un logiciel multilanguage. Aussi loin que le code de l'application va, adaptabilité n'est pas un problème. Nous pouvons utiliser des ressources linguistiques spécifiques et ont toutes sortes d'outils qui fonctionnent bien avec eux.

Mais quelle est la meilleure approche dans la définition d'un schéma de base de données multilingue? Disons que nous avons beaucoup de tables (100 ou plus), et chaque table peut avoir plusieurs colonnes qui peuvent être localisés (plus de nvarchar colonnes doivent être localisable). Par exemple, l'une des tables susceptible de détenir des informations sur le produit:

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

Je peux penser à trois approches à l'appui du texte multilingue dans le NOM et la DESCRIPTION des colonnes:

1. Colonne séparée pour chaque langue

   Quand on ajoute une nouvelle langue pour le système, nous devons créer des colonnes supplémentaires pour stocker le texte traduit, comme ceci:

   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. Traduction de table avec des colonnes pour chaque langue

   Au lieu de stocker le texte traduit, seulement une clé étrangère pour les traductions table est stockée. Les traductions de la table contient une colonne pour chaque langue.

   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. Les tables de traduction avec des lignes pour chaque langue

   Au lieu de stocker le texte traduit, seulement une clé étrangère pour les traductions table est stockée. Les traductions de la table contient uniquement une clé, et une autre table contient une ligne pour chaque traduction d'une langue.

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

Il y a des avantages et des inconvénients de chaque solution, et je voudrais savoir quelles sont vos expériences avec ces approches, que recommandez-vous et comment vous allez sur la conception d'un schéma de base de données multilingue.

Answer 1

La troisième option est la meilleure pour plusieurs raisons:

• Ne nécessite pas de modifier le schéma de base de données pour de nouvelles langues (et donc de limiter les changements de code)
• Ne nécessite pas beaucoup d'espace pour lettre morte langues ou de traduction d'un article en particulier
• Offre la plus grande flexibilité
• Vous ne finissent pas avec peu de tables
• Vous n'avez pas à vous soucier de clés null, et en vérifiant que vous êtes de l'affichage d'une traduction existante au lieu d'une valeur null.
• Si vous modifiez ou développez votre base de données pour englober d'autres traduisible articles/choses/etc vous pouvez utiliser le même système et de tables - c'est très découplé du reste des données.

Answer 2

Jetez un oeil à cet exemple:

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

Je pense qu'il n'y a pas besoin d'expliquer, la structure se décrit.

Answer 3

Je voudrais habituellement aller pour cette approche (pas réel sql), cela correspond à votre dernière option.

 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'
 

Parce que tous les textes traduisibles en un seul endroit rendent la maintenance beaucoup plus facile. Parfois, les traductions sont externalisées vers des bureaux de traduction, de cette façon vous pouvez leur envoyer un seul gros fichier d'exportation, et les importer tout aussi facilement.

Answer 4

Je cherchais quelques conseils pour la localisation et trouvé ce sujet. Je me demandais pourquoi cela est utilisé:

 CREATE TABLE T_TRANSLATION (
   TRANSLATION_ID
)
 

Donc, vous obtenez quelque chose comme user39603 suggère:

 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'
 

Ne pouvez-vous pas laisser la traduction de la table pour que vous obteniez ceci:

     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'