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

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

ただいまの
回答率

89.07%

【MySQL】顧客データを「家族」としてまとめる為のテーブル設計

解決済

回答 3

投稿

  • 評価
  • クリップ 1
  • VIEW 2,393

MMY

score 9

テーブル設計について悩んでおります。
お知恵をお借りできたらと思い投稿しました。

乱筆の上長文となりますが
よろしくお願いします。

現在、顧客データを管理するテーブル(以下、顧客テーブル)の設計を行っています。
このテーブルは「1レコード=1人のお客様のデータ」となるような極普通のテーブルです。

また「家族設定」という
複数の顧客データを束ねて「同じ家族」としてグルーピングする機能を実装する必要があります。

例)家族設定
顧客番号:1「山田 一郎」
顧客番号:2「山田 花子」
⇒「この2人が家族だ」というデータをどこかで抱えるようなイメージです
※1人の顧客が複数の家族には所属できない想定です

「家族設定」は
・家族で来店された際、家族で消費された合計額を代表のお一人が支払えるようにする
・「ポイントを共有する」設定を行っている場合、同じ家族が保有するポイントも使用できる
などに利用していこうと考えております。

このようなケースの場合
家族設定を管理するテーブル(以下、家族テーブル)を親テーブルとし
顧客テーブルから、家族テーブルのPKを参照するような作りが自然かと思うのですが

システムの仕様上、WEBから会員登録+予約された際に顧客データが作成されるつくりになっており
顧客データがINSERTされる時点で「そのお客様がどこの家族に所属するか」を特定するのが難しいです。

ですので「家族設定」が設定されるタイミングとしては
・実際に家族で来店された際、店員(システムユーザ)が手動で家族設定を行う
・会員登録フォームにて「紹介者:家族」「紹介者氏名:○○」といった項目がありますので、その情報を元に店員が手動で家族設定を行う
などのように店員が手動で設定してくような運用となります。

また、家族設定が必要となるお客様の数自体も少ないので
出来れば「家族設定が必要な場合のみ、データが作成される」ようにしたいと考えております。

このような要件の場合、どのようなテーブル構成がベターでしょうか?

現時点で考えているのは

【1】
(テーブル構成)
家族テーブルを親、顧客テーブルを子として設計し、顧客テーブルは家族テーブルのPKをFKとして参照する。

(実装)
顧客データがINSERTされるとき、FKを「0」として登録する。
その上で、FK=0の場合は「家族設定なし」とみなすようプログラムを作成する。
家族設定が必要になった時に、家族テーブルに新しいレコードを発行し、顧客テーブルが参照しているFKの値を差し替える。

(懸念していること)
「子テーブルのレコードをまとめるために、親テーブルにレコードを作成する」
というフローが本来とは逆向きになるのが気になります。

【2】
(テーブル構成)
中間テーブルを作成し、「自分のPK」「他の顧客データのPK」を総当たりで登録していく設計。
5人で「家族設定」をする場合20レコード作成する(5人 x 他の4人)

(実装)
自分のPKを条件にSELECTすれば、家族設定している全顧客データのPKが引けるので実装上
これなら親テーブルを必要としないので「必要な時に家族設定のデータを作る」という運用に合っているように思いました。

(懸念している事)
10人家族なら90レコード(10人 x 他の9人)、100人家族の場合は9,900レコード(100人 x 他の99人)と
家族設定に含める人数が多くなればなるほど、レコード数が加速度的に嵩んでいくのが気がかりです。

【3】
(テーブル構成)
顧客テーブルに「家族設定ID」のカラムを設け、家族テーブルを作らない設計。

(実装)
顧客データを作成する際に「家族設定ID」に「自分のPK」代入する。
家族設定を行う場合は、家族設定を行う顧客データの中で「最も若いPK」を特定し、家族設定を行う全顧客データの「家族設定ID」をそのPKで上書きするようにする。
システム上は「家族設定IDが同じ顧客データ」を家族とみなすようにする。
※家族設定を行っていない場合「家族設定ID=自分のPK」となるので、システム的には「自分ひとりの家族=家族設定なし」と判断できる

(懸念している事)
本来であれば親子関係or多対多の関係をつくるべきものを、無理やり1テーブルに押し込んでいるようで忌避感があります。

以上の3案を想定しておりますが、いかがでしょうか?

もし「ここをもっとこうした方がよい」などアドバイスがあれば、お知らせいただけますと幸いです。
また、別のより良い設計思想などございましたらご教示いただけますと、大変助かります。

よろしくお願いいたします。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 3

+3

個人的には【1】 の変形で、「家族に属さない場合には家族IDをNULLにしておく」というのが妥当ではないかと思います(NULLありの列であれば、外部キーの参照に使う列をNULLにしても問題ありません)。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/10/20 15:28

    ご回答ありがとうございます。

    なるほど、やはり親テーブルと子テーブルに分けれる場合はキチッと分けてしまった方がよいのですね。

    【1】案の場合、
    「まず子テーブルにデータが出来て、その後必要に応じて親テーブルのデータが出来る」
    というフローが引っかかっていましたが、その辺りはプログラム側でしっかり制御していこうと思います。

    「0ではなくNULLを使う」という案もありがとうございます。
    プログラム言語によっては0の扱いが真だったり偽だったりするので、実装上でもNULLの方がよさそうです!

    こちらの案も検討させていただきたいと思います。

    キャンセル

checkベストアンサー

+1

きちんとやるなら家族管理用テーブルをつくり「山田家」データの親コードを
各メンバーに埋め込むんで正規化すること、
RDBにおけるデータ管理の基本です

ファミリーを登録するタイプのテーブル構成がむずかしいようなら
テーブルをわけずに同じテーブル内に山田家データをつくるのでもよいでしょう
1テーブルで管理すれば入れ子モデルに変換する際には楽です。

ファミリー自体がだめなら、家族の誰かを論理的な親として、子供となる人に
親コードを埋め込みます。この場合親は自分自身の子供にもなります
よくある契約者とその家族には割引的な管理になります

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/10/20 15:14

    ご回答ありがとうございます。

    追加で数点確認させていただきたいことがあるのですが、よろしいでしょうか?
    お手すきの際で結構ですので、ご回答いただけると幸いです。

    >きちんとやるなら家族管理用テーブルをつくり「山田家」データの親コードを
    >各メンバーに埋め込むんで正規化すること

    の部分なのですが

    1. 顧客テーブルに山田一郎様のデータを登録
    2. 顧客テーブルに山田花子様のデータを登録
    3. 山田一郎様と山田花子様を家族設定する

    という運用を行う場合

    「3.」のフェーズで家族管理用テーブルに「山田家」のレコードをINSERTし、
    作成した「山田家」のPKを、管理テーブルの「山田一郎」「山田花子」のレコードからFKとして参照する
    といったフローになるかと思うのですが、この認識で正しいでしょうか?


    >ファミリー自体がだめなら、家族の誰かを論理的な親として、子供となる人に
    >親コードを埋め込みます。この場合親は自分自身の子供にもなります

    こちらのケースの場合

    (顧客番号, 顧客名, 親コード)
    1, 山田太郎, 1
    2, 山田花子, 1
    3, 佐藤大輔, 3
    4, 佐藤さと子, 3

    こういったテーブル構成になるかと思います。

    上記例の場合
    顧客テーブルのPKである「顧客番号」を、同一テーブルの「親コード」から、まるでFKのように参照している。
    という構成になり「出来るなら使わないようにしたい」テーブル構成であるという認識なのですが、正しいでしょうか?

    ※もしこれが「あまり望ましくない」のであれば、今後も気を付けないといけないと思って確認させていただきました。
    ※質問の本旨とから外れた質問となりすいません……。

    よろしくお願いいたします。

    キャンセル

  • 2016/10/20 15:57

    >(顧客番号, 顧客名, 親コード)
    >1, 山田太郎, 1
    >2, 山田花子, 1
    >3, 佐藤大輔, 3
    >4, 佐藤さと子, 3

    これで問題ありません。
    検索・集計の際は親コードでgroup byすればよく、それに顧客番号をリレーションしてください。

    キャンセル

  • 2016/10/20 17:08

    ご回答ありがとうございます。

    現在のプロジェクトに、どのようなテーブル設計が
    ご回答いただいた皆様のご意見を参考に、もう一度検討してみたいと思います。

    キャンセル

0

・顧客テーブル
顧客ID
顧客名

・家族テーブル
家族ID

・家族-顧客関連テーブル
家族関連ID
家族ID
顧客ID

と言う感じで、家族テーブルと顧客テーブルを紐づける中間テーブルを紐づければよいのでは。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/10/20 15:10

    ご回答ありがとうございます。

    なるほど。顧客テーブル、家族テーブルをつなぐ中間テーブルを作ると良いのですね。
    この設計なら【2】で上げた「家族に含める人数が増えると、レコード数が加速的に増える」という事もなくなるんですね。

    検討してみます!

    キャンセル

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

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

関連した質問

同じタグがついた質問を見る