多言語対応　DB設計の考察

# まえがき
お空から「多言語システム作れよ」と言われたら設計責任者が目を通すための記事

# 基本設計
こんなデータベース設計が合ったとしよう

resource

| r_id | r_name | r_note | r_num |
|:-:|:-:|:-:|:-:|
| 1  |  机 | 机の注記  | 3  |
| 2  |  椅子 | 椅子の注記  |  12 |
| 3  |  プロジェクター | プロジェクターの注記  |  1 |
# タイプ１　フィールド追加設計
resource

| r_id | r_name | r_name_en | r_note | r_note_en | r_num |
|:-:|:-:|:-:|:-:|:-:|:-:|
| 1  |  机 |desk| 机の注記  | desk_notation  | 3  |
| 2  |  椅子 |chair| 椅子の注記  | chair_notation  |   12 |
| 3  |  プロジェクター |projector| プロジェクターの注記  | projector_notation  |  1 |


```sql:日本語抽出
SELECT `r_id`,`r_name`,`r_note`,`r_num` FROM `resource`
```

```sql:英語抽出
SELECT `r_id`,`r_name_en`,`r_note_en`,`r_num` FROM `resource`
```

- メリット
    - 直感的にわかりやすい
    - データベース操作も非常に簡単

- デメリット
    - 言語が増えるとフィールドを増やさなければならない

# タイプ２ 言語テーブル
たくさんの言語を扱う＆動的な言語の追加がある人向け
resourceテーブル

| r_id | r_num |
|:-:|:-:|
| 1  | 3  |
| 2  | 12 |
| 3  | 1 |

とresource_languageテーブルを作る

| rl_id |rl_r_id|rl_language| rl_name | rl_note | 
|:-:|:-:|:-:|:-:|:-:|
| 1  |1|jp|  机 | 机の注記  |
| 2  |1|en|  desk| desk_notation  |
| 3  |2|jp| 椅子  |椅子の注記  |
| 4  |2|en| chair  | chair_notation  |
| 5  |3|jp| プロジェクター  | プロジェクターの注記  |
| 6  |3|en| projector  | projector_notation  |

```sql:日本語抽出
SELECT `r_id`,`rl_name`,`rl_note`,`r_num` FROM `resource` 
LEFT JOIN `resource_language` 
ON `r_id` = `rl_r_id` AND `rl_language` = 'jp'
```

```sql:英語抽出
SELECT `r_id`,`rl_name`,`rl_note`,`r_num` FROM `resource` 
LEFT JOIN `resource_language` 
ON `r_id` = `rl_r_id` AND `rl_language` = 'en'
```

- メリット
    - 言語が増えてもフィールドを増やすコストはない
- デメリット
    - 対象のテーブルごとに各言語テーブルが必要
    - 対象のフィールドが増えると各言語テーブルにもフィールド追加が必要
    - SELECTにはJOINのコスト、INSERTおよびUPDATEおよびDELETEのコストが倍になる

# タイプ３ 統合言語テーブル
テーブルの数が増えるのは勘弁してほしい＆フィールド追加がめんどくさいという人向け

languageテーブルの作成

| l_id |l_foreign_table|l_foreign_id|l_foreign_feild|l_language| l_data | 
|:-----:|:-----:|:-----:|:-----:|:-----:|:-----:|
| 1  |resource|1|name|jp|  机 |
| 2  |resource|1|name|en|  desk|
| 3  |resource|2|name|jp| 椅子  |
| 4  |resource|2|name|en| chair  |
| 5  |resource|3|name|jp| プロジェクター  |
| 6  |resource|3|name|en| projector  |
| 7  |resource|1|note|jp|  机の注記 |
| 8  |resource|1|note|en|  desk_notation|
| 9  |resource|2|note|jp| 椅子の注記|
| 10  |resource|2|note|en| chair_notation  |
| 11  |resource|3|note|jp| プロジェクターの注記  |
| 12  |resource|3|note|en| projector_notation  |



```sql:日本語抽出
SELECT `r_id`,A.`l_data` as `r_name`,B.`l_data` as `r_note`,`r_num` FROM `resource` 
LEFT JOIN `language` as A 
ON A.`l_foreign_table` = 'resource' 
 AND A.`l_foreign_id` = `r_id` 
 AND A.`l_foreign_feild` = 'name'
 AND A.`l_language` = 'jp'
LEFT JOIN `language` as B
ON B.`l_foreign_table` = 'resource' 
 AND B.`l_foreign_id` = `r_id` 
 AND B.`l_foreign_feild` = 'note'
 AND B.`l_language` = 'jp'
```

```sql:英語抽出
SELECT `r_id`,A.`l_data` as `r_name`,B.`l_data` as `r_note`,`r_num` FROM `resource` 
LEFT JOIN `language` as A
ON A.`l_foreign_table` = 'resource' 
 AND A.`l_foreign_id` = `r_id` 
 AND A.`l_foreign_feild` = 'name'
 AND A.`l_language` = 'en'
LEFT JOIN `language` as B
ON B.`l_foreign_table` = 'resource' 
 AND B.`l_foreign_id` = `r_id` 
 AND B.`l_foreign_feild` = 'note'
 AND B.`l_language` = 'en'

```

- メリット
    - テーブル＆フィールドがいくつになってもlanguageテーブルが一つで大丈夫
- デメリット
    - SELECT時のJOINおよびINSERTおよびUPDATEおよびDELETEのコストがフィールド数分かかる


# まとめ
これという答えはない。
システム規模と要件に合ったものを利用しましょう

# あとがき
フレームワークや外部のサービスで吸収できるならそれがいいと思います。
Web系ならwovnとかあるよ。