質問: テーブル設計について悩んでおります。 お知恵をお借りできたらと思い投稿しました。 乱筆の上長文となりますが よろしくお願いします。 現在、顧客データを管理するテーブル(以下、顧客テーブル)の設計を行っています。 このテーブルは「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案を想定しておりますが、いかがでしょうか? もし「ここをもっとこうした方がよい」などアドバイスがあれば、お知らせいただけますと幸いです。 また、別のより良い設計思想などございましたらご教示いただけますと、大変助かります。 よろしくお願いいたします。
回答: 個人的には【1】 の変形で、「家族に属さない場合には家族IDを**NULL**にしておく」というのが妥当ではないかと思います(NULLありの列であれば、外部キーの参照に使う列をNULLにしても問題ありません)。