多言語対応 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

日本語抽出

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とかあるよ。