[解決済み】多言語データベースのためのスキーマ

質問

多言語のソフトウエアを開発しています。アプリケーションのコードに関しては、ローカライズ可能性は問題ではありません。言語固有のリソースを使用することができ、それらとうまく連動するあらゆる種類のツールがあります。

しかし、多言語データベーススキーマを定義する上で、どのようなアプローチが最適なのでしょうか。例えば、多くのテーブル（100以上）があり、各テーブルはローカライズ可能な複数のカラムを持つことができるとします（nvarcharカラムのほとんどはローカライズ可能であるべきです）。例えば、あるテーブルには製品情報が格納されているとします。

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

NAME列とDESCRIPTION列の多言語テキストをサポートするために、3つのアプローチを考えることができます。

1. 言語ごとにカラムを分ける

   新しい言語をシステムに追加した場合、翻訳されたテキストを格納するために、次のように追加のカラムを作成する必要があります。

   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. 各言語の列を持つ翻訳テーブル

   翻訳されたテキストを格納するのではなく、translationsテーブルへの外部キーのみが格納される。翻訳テーブルには、各言語のカラムが含まれています。

   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. 各言語の行を持つ翻訳テーブル

   翻訳されたテキストを格納するのではなく、translationsテーブルの外部キーのみが格納される。翻訳テーブルにはキーだけが格納され、別のテーブルには各言語への翻訳のための行が格納されます。

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

それぞれのソリューションには長所と短所がありますが、これらのアプローチについて、皆さんの経験やお勧めする方法、多言語データベース・スキーマの設計について教えてください。

どのように解決するのか？

翻訳可能な各テーブルに関連する翻訳テーブルを持つことについてどう思いますか？

CREATE TABLE T_PRODUCT (pr_id int, PRICE NUMBER(18, 2))

CREATE T_PRODUCT_tr (pr_id INT FK, languagecode varchar, pr_name text, pr_descr text)

この方法では、複数の翻訳可能なカラムがある場合、それを取得するために1つの結合しか必要としません+翻訳IDを自動生成していないので、関連する翻訳と一緒にアイテムをインポートする方が簡単かもしれません。

このマイナス面は、複雑な言語フォールバック・メカニズムがある場合、各翻訳テーブルに対してそれを実装する必要があることです（ストアドプロシージャに依存している場合）。もし、ストアドプロシージャに依存するのであれば、この問題は発生しないでしょう。

私も次のアプリケーションのために、このことについて決定しようとしているところです。 これまでのところ、私たちはあなたの3番目のタイプを使用しています。