質問: お世話になります。 この度、自身初めてLaravelを使用してWEBシステムを構築しております。 Laravelでは、DBのキー項目は常にシーケンスで自動採番するサロゲートキーの使用が推奨されているため、 初めてサロゲートキーを用いたDBを構築しております。 そのなかで、通常の複合キーを用いて正規化する設計と考え方が異なると感じている部分が多々あり、 どのような構成がベストなのかわからず疑問がでてきました。 サロゲートキーでのDB設計、多言語対応(i18n/l10n)の経験がある方のご意見をお聞かせ願います。 (また、このような悩みを解決できるようなDB設計の技術書があればぜひ教えてください!) 以下が悩みのもととなったER設計です。 ``` 【言語マスタ】 id integer - ID name varchar - 名称 【商品マスタ】 id integer - ID language_id integer - 言語ID name varchar - 名称 spec varchar - 仕様 price integer - 価格 maker_id integer - メーカーID 【メーカーマスタ】 id integer - ID name varchar - 名称 ・言語についてはブラウザのLocaleで初期設定、その後画面から選択できるような仕様をイメージしています ・価格情報について、DB上は日本円の情報のみ保持します ``` 1マスタでもサロゲートキー? マスタのキーをサロゲートキーで持つ必要性はあるのでしょうか? また、上記の例でいうと、商品マスタの外部キーである言語IDが代理キーだと、 マスタ定義上紐付けて登録することが難しくなると感じていますが、どのように登録するのが良いでしょうか? 2言語に依存しない項目は別テーブル化するべき? 上記の例でいうと、商品マスタの価格やメーカーIDがこれにあたりますが、これらの項目は別テーブルで管理した方が良いのでしょうか?
回答: サロゲートキーに対するのはナチュラルキーです。 何れも複合項目であるかどうかは本来関係はありません。 > > 1マスタでもサロゲートキー? > > 2言語に依存しない項目は別テーブル化するべき? > > > 要件次第です。 備考やメモ的な扱いなら、別テーブルでの管理の必要はないでしょう。 ただ、体系的に管理出来そうなものは、別テーブルでサロゲートキーでの管理とした方が、後々の拡張性はあります。 ですが、管理しないよりは、作りこみにコストが掛かりますので、これも要件次第でしょう。 追記 質問にある【商品マスタ】はサロゲートキーでの管理とはなっていない様に見えます。 サロゲートキーを`ID`とすると、一般的なナチュラルキーは、`メーカーID+商品コード+言語ID`になると思われます。 また、質問にある【商品マスタ】はm17nでの、表示する言語に対応するものに思え、別途、`メーカーID+商品コード`で管理される別テーブルが必要に思えます。 追記2 ナチュラルキーとしては、**製品メーカー**とそのメーカーで管理される**商品コード**が良いと思います。 ``` 【商品マスタ】サロゲートキー:商品ID、ナチュラルキー:メーカーID+商品コード id integer - 商品ID maker_id integer - メーカーID product_code --商品コード price integer - 価格 【商品言語別マスタ】サロゲートキー:ID、ナチュラルキー:商品ID+言語ID id integer - ID product_id integer - 商品ID language_id integer - 言語ID name varchar - 名称 spec varchar - 仕様 ``` ※過去の情報まで管理するようなシステムの場合は、履歴情報として日付をキーの一部にする事が多いですね。その場合に、その日付をサロゲートキーに含めるかどうかは悩ましいところですが。 > > 商品マスタの外部キーである言語IDが代理キーだと、 > > マスタ定義上紐付けて登録することが難しくなると感じていますが、どのように登録するのが良いでしょうか? > > > 画面でメンテナンスする要件があるとの事なので、プルダウンで選択などの方法や、チェックなどにも使用できるので、言語はマスタ化されていた方が良いでしょう。 前提としてそのデータが既に存在するものを外部キーとするのですから、一緒に登録するという事を考えるものではありません。