質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.54%

サロゲートキーでのDB設計

解決済

回答 1

投稿

  • 評価
  • クリップ 1
  • VIEW 1,228

byth_net

score 10

お世話になります。
この度、自身初めて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上は日本円の情報のみ保持します

①マスタでもサロゲートキー?
マスタのキーをサロゲートキーで持つ必要性はあるのでしょうか?
また、上記の例でいうと、商品マスタの外部キーである言語IDが代理キーだと、
マスタ定義上紐付けて登録することが難しくなると感じていますが、どのように登録するのが良いでしょうか?

②言語に依存しない項目は別テーブル化するべき?
上記の例でいうと、商品マスタの価格やメーカーIDがこれにあたりますが、これらの項目は別テーブルで管理した方が良いのでしょうか?

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • m.ts10806

    2018/11/05 11:43

    言語が違った場合、同じ商品でどこがどう変わるのでしょうか

    キャンセル

  • m.ts10806

    2018/11/05 11:44

    あと特定の言語にしかない商品はあるのでしょうか

    キャンセル

  • byth_net

    2018/11/05 17:07

    不備があり、申し訳ありません。概ね解決はしましたが、後から見る人のためにも後程修正させていただきます。ご指摘ありがとうございます。

    キャンセル

回答 1

checkベストアンサー

+3

サロゲートキーに対するのはナチュラルキーです。
何れも複合項目であるかどうかは本来関係はありません。

①マスタでもサロゲートキー?
②言語に依存しない項目は別テーブル化するべき?

要件次第です。
備考やメモ的な扱いなら、別テーブルでの管理の必要はないでしょう。

ただ、体系的に管理出来そうなものは、別テーブルでサロゲートキーでの管理とした方が、後々の拡張性はあります。
ですが、管理しないよりは、作りこみにコストが掛かりますので、これも要件次第でしょう。

追記

質問にある【商品マスタ】はサロゲートキーでの管理とはなっていない様に見えます。
サロゲートキーを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が代理キーだと、
マスタ定義上紐付けて登録することが難しくなると感じていますが、どのように登録するのが良いでしょうか?

画面でメンテナンスする要件があるとの事なので、プルダウンで選択などの方法や、チェックなどにも使用できるので、言語はマスタ化されていた方が良いでしょう。
前提としてそのデータが既に存在するものを外部キーとするのですから、一緒に登録するという事を考えるものではありません。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/11/05 13:27 編集

    >メーカーをまたいで商品コードが重複する可能性を考慮している?
    そうです。
    >これを考慮しない場合は、商品コードのみがナチュラルキーでOK?
    OKです。
    >言語idの場合は、サロゲートキー=ナチュラルキーとして管理する今のままの形で正しい解釈しました。
    ナチュラルキーとして、言語コード(ISO 639)などを使用するのであれば問題ないと思います。

    >サロゲートキー設計でDBのマスタを客先へリリースする場合、一般的にどのような方法がとられるのでしょうか?
    環境に依存します。
    ユーザーがメンテナンスするものではなく、システムの定義情報としてという解釈したとして、プログラムの配布の一部がDBのテーブル内容という事ですから。

    キャンセル

  • 2018/11/05 13:30 編集

    サロゲートキーを採用するということは、ナチュラルキーをサロゲートキーに対するメタ情報にすると言う事です。
    システム的な定義情報でない限り、サロゲートキーを採用して、情報の変更による影響を受けないようにする方が良いのではないかと思います。
    ただ、サロゲートキーの場合、それが外部キーとして使用されているような場合には、データ補正等の際に容易に復元できなかったりする側面もありますが。

    キャンセル

  • 2018/11/05 17:06

    > >言語idの場合は、サロゲートキー=ナチュラルキーとして管理する今のままの形で正しい解釈しました。
    > ナチュラルキーとして、言語コード(ISO 639)などを使用するのであれば問題ないと思います。
     確認してよかった、ちゃんと理解できていなかったです。
     あくまでも言語マスタには、ID(言語ID)と言語コードのように、サロゲートキーとナチュラルキーの両方を持つのが普通なんですね。
     ありがとうございます。
     慣れるまでは、ナチュラルキーのみで設計したあとに、主キーと外部キーをサロゲートキーに置き換えるように考えてみようと思います。

    マスタのリリースについて、今回の言語マスタと商品言語別マスタの関係をどう登録していこうかな~とまだもやもやしてはいますが、あとは環境に合わせて自分なりに最善の案を考えてみようと思います。

    長々とありがとうございました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.54%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる