まえがき
お空から「多言語システム作れよ」と言われたら設計責任者が目を通すための記事
基本設計
こんなデータベース設計が合ったとしよう
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 |
日本語抽出
SELECT `r_id`,`r_name`,`r_note`,`r_num` FROM `resource`
英語抽出
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 |
日本語抽出
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'
英語抽出
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 |
日本語抽出
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'
英語抽出
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とかあるよ。