顧客管理システムのデータベース設計に関して。

Question

前提・実現したいこと

飲食店向けの会員の顧客管理システムを作成しています。
データーベース設計(MySQL)で、困っているので相談に乗ってください。
データベース設計初心者ですので、そもそも根本からして間違っている可能性があり
自信が持てていないので、間違っている箇所を指摘、若しくはあっている場合の肯定も嬉しく思います。
よろしくお願いいたします。

以下のように、店舗,顧客マスタがあります。

storesテーブル
|id |name 　 |tel 　　 |
|int|varchar(255)|varchar(11)|
|1 |東京店 |0300000000 |
|2 |大阪店 |0300000001 |
|3 |名古屋店 |0300000002 |

customersテーブル
|id |name |store_id|tel |
|int|varchar(255)|int |varchar(11)|
|1 |田中太郎　　　|1 |0300000003|
|2 |田中花子　　　|1 |08000000001|
|3 |田中二郎　　　|2 |08000000002|
|4 |田中三郎　　　|2 |0300000004|
|5 |田中四郎　　　|3 |0300000005|

上記のように、簡単な店舗マスタ,顧客マスタテーブルがあるとします。
店舗ごとに顧客データとして、独自の項目を設けられるようにしたいのです。
例えば、東京店では顧客の誕生日を登録できるようにしたいが、他の店舗では必要ない。
大阪店では、顧客の身長を登録したいが、他の店舗では必要ない。
という具合です。後々各店舗が好きに登録情報を追加,削除できるような柔軟性の高いシステムを作りたいと考えています。

考えついた方法

customsテーブル
|id |store_id|name |
|int|int |varchar(255)|
|1 |1 |誕生日 |
|2 |2 |身長 |

custom_dataテーブル
|id |customer_id|custom_id|value|
|int|int |int | ??? |
|1 |2 |1 |2018/06/21|
|2 |4 |2 |173.5cm|

上記のような方法を無理やり考えたのですが、あまりこのやり方が美しいとは思えませんし、
custom_dataテーブルのvalueカラムのデータ型をどうした物かと困ってしまいます。

なにかよい方法はありませんでしょうか？

Answer 1

そもそものアプリケーションの要件を再整理したほうが良いかと。

店舗単位で顧客管理をするのであれば、全店舗分の統一した顧客マスターは無駄だと思う。
東京店と大阪店に登録のある山田太郎さん（同一人物）を、マスター上別人物として扱わなければならない設計なので、データ解析にも使えない。

そもそもデータ解析等、統一的にデータを扱う要件がないのであれば、店舗ごとに別テーブルを設けると管理もパフォーマンスも良くなるので、そういった構造も視野に入れる必要があるのでは？

個人的に気になるのは、個人情報の収集粒度を各店舗ごとに任せるって、コンプライアンス的に危ないなぁってとこですね＾＾；

Answer 2

店舗固有の顧客情報をもたせた上で、その情報を活用するその先のことも予見しないといけません。

【なんのために顧客情報を登録して管理するのか】
→「顧客を絞り込んで個別訴求したいから」

誕生日を登録したら、毎月バースデーカードを送ったりするかもしれない。
身長や足のサイズなど登録したら、特定の体型の方に向けて商品紹介情報を送るかもしれない。
そういう絞り込みができるようにしておかないと再設計するハメになります。

〈各店舗が好きに登録情報を追加,削除できる〉といっても、
数日間考え抜けばだいたい登録しておきたい情報は洗い出せるものなので、
登録しておきたい情報の洗い出しをしっかりやって、
その上でテーブル設計するべきだと思います。

まぁ、生年月日、身長や体重、B/W/H、足のサイズ、
結婚してるしてない、子供がいるいない、
ケータイの番号、メールアドレス、
TwitterやFacebookやInstagramやLINEをやってるやってないとか、
質問者さんがある程度案を出してから各店舗に打診して、
もっと登録しておきたい情報はないかと問い合わせすればいいです。

その他に、本当に予見できない情報として、
例えば数字用の領域、文字列の領域、日付情報の領域などを
一定数予備領域をもたせる、
予備領域につけるネーミングは別途管理する、
って線が無難じゃないでしょうか。

そこを考えるのを省いて柔軟にしようとしたために、
データベースの設計がグダグダになっては意味がありませんので。

Answer 3

そういう要望はあって、対応しているシステムもあったりはします。
でもそれは、突き詰めると個人ごとのニーズです。

決められた項目だけでは足りないというのはどういった場面でも有るので、汎用的に対応するための項目として、備考欄を持たせます。

実際設定で変更可能な汎用項目を設計したことはありますが、大体は備考欄で十分です。
備考欄のメリットは、仮に汎用的な項目を追加できる仕組みであったとしても、入力時点で別項目が必要となっても、内容を書き込める点です。

汎用的な設計に於いての項目属性は文字型にして、その項目の書式や属性は別に定義し、表示や入力の際にその定義を経由します。また、項目がフリーと言うことは画面のレイアウトもそれに合わせた設定ができるというニーズも、大体セットで付いてきます。

そこまでして、という感じですので、まずは備考で対応して、重要度が高まったら仕様変更という対応が良いと思います。

追記

汎用的に動的に項目を定義していく方法とは別に、
・想定される項目を全て定義しておき、不要なものを隠す
という方法もあります。
こちらは機能を全て実装しておき、必要に応じて機能をONにするというものです。
顧客管理に関して言うと、sysjojoさんがコメントして下さったようにCRMといった括りが既に存在していますから、そちらを参考にすることである程度効率化は図れると思います。

但し、CRMのパッケージは予め汎用的に使用することを目的にしているからであり、あるといいな程度のニーズに答えるために、大掛かりなものを作り込むというのは見合ったものではありません。

行うとしても、ニーズのある項目を準備し、表示/非表示で切り替える程度が良いのではないかと思います。

Comments

[sysjojo]

> そういう要望はあって、対応しているシステムもあったりはします。
そういうつくりになっている仕組みを参考にする、というのも手、ですね。
SugerCRM / vTigerCRM / F-RevoCRMとかOTRSとか。

[sazi]

汎用であるということは、専用と比べて何かを犠牲にしなければなりませんので、それが受け入れられるかですね。
相手が受け入れないなら、対応するためにはコストが掛かりますけど、汎用であるが故にコストは割高になります。
ローカルなニーズにそういったコストを割くユーザーには出会ったことはありませんけどね。

[sysjojo]

語弊があったかもしれないですが、汎用的な作りにすることを肯定しているわけではないです。
強く否定もしませんが、コスト高になった上でパフォーマンスにいい影響を与えるとも思いませんので、saziさんのおっしゃる通りかと思います。
柔軟に変えれるように、はよく考えてない裏返しであるとも思いますしね。

[sazi]

汎用的な作りを否定はしません。寧ろ共通化するという意味では肯定派です。
コメントは質問者さんへの経験者からの情報提供を意識したものですので、sysjojoさんへの否定的な返信に見えてしまったのなら、申し訳ありません。

[sysjojo]

すみません。私も情報提供、という意味でそういうつくりになっている仕組みのキーワードでもあれ調べられるかな~？程度でしたので。
肯定意見／否定意見とかあんまり気にしてないので、私のコメントも気になっていたら失礼いたしました。

顧客管理システム、という切り口だと、そういう仕組みにアドオンする、という方法もなくはないかもしれません。(先に挙げたxxxCRMは顧客管理システム)
話がそれましたね。失礼。

Answer 4

テーブル設計の基本にひとつのカラムには一つの意味しか持たせてはならない、って掟があります。

custom_dataテーブルの生年月日や身長は別のカラムとするべきでしょう。
誕生月に割引券を贈るサービスをやろうとすると、あるカラムが生年月日かチェックして該当月のデータを抜き出す必要が出てきます。

Answer 5

飲食店向けの業務系システムを開発したことがあります

店舗と顧客は直接紐づくべきではない

顧客マスタが店舗の主キーを持つことで紐づいていますが、顧客は店舗ではなく店舗の運営会社と紐づくものです。
理由は2点あります。

• 顧客情報は店舗横断で使用されるものです。顧客とは運営会社に紐づくもので、店舗に紐づくべきは"来店情報"です。
• 飲食店は店舗リニューアル(居酒屋からイタリアンへ業態変更)、統廃合や買収などが極めて多い業態です(3年以内にほとんどが消えるか買収されます)。そのため店舗の運営会社が変わることなども他の業態に比べるととても多いです。

顧客には「予約者」と「来店者」の2種類がある

食べログやHotpepperなどのシステムを見ればわかるのですが、 一般的に飲食店向けシステムで「顧客」といった場合、
「予約者」と「来店者」の2種類の情報があります。
untan.r様のシステムでは「顧客」とは何かを詰めておいた方が良いと思います。

顧客データのカスタマイズ

店舗ごとに顧客データとして、独自の項目を設けられるようにしたいのです。
例えば、東京店では顧客の誕生日を登録できるようにしたいが、他の店舗では必要ない。
大阪店では、顧客の身長を登録したいが、他の店舗では必要ない。
という具合です。後々各店舗が好きに登録情報を追加,削除できるような柔軟性の高いシステムを作りたいと考えています。

飲食店向けの業務システムを10以上は見てきましたが,「登録情報の"項目"を店舗ごとにカスタマイズできる」という機能は必要ないと思います。
まずは既存の飲食店向けの予約,顧客管理システムがどのような顧客データを保存しているかをリサーチしてはいかがでしょうか。

• 食べログ
• ぐるなび
• Hotpepper
• 一休
• OZmall
• Toreta
• TableSolution
• エビソル
• ヨヤクノート
• レストランボード

簡単なシステムならば食べログかToreta、複雑なシステムならばTableSolutionが参考になると思います。

本当に必要な機能

おそらくuntan.r様が本当に必要なのは以下の2点ではないかと思っています。

• 必須入力項目を店舗ごとにON/OFF出来る（顧客情報の項目自体は共通）
• WordPressのタグ機能のようなもの(例: 日本酒好き、オーナーの知人、芸能人、いつも違う女性と来る、要注意など)

Answer 6

考え方は4つあります。

1. カスタム性がさほど高くないならユーザーテーブルに予め項目だけつくっておいて使わない店では使わない
2. 誕生日、身長などテーブルをわけて、検索時に必要に応じてleft joinしていく
3. カスタムテーブル自体は1つで、レコードに属性（誕生日、身長）をカラムとしてもつ(実質1番をテーブル分割しただけ)
4. カスタムテーブル自体は1つで、レコードに属性を属性としてもつ

検索性や管理のしやすさは1>2>3>4、効率的なデータ管理としては1<2<3<4

Answer 7

custom_dataテーブルで持たせようとしている項目が、本当に店舗独自なのかどうか、という視点も必要かと。
例えば、例として挙げられている「誕生日」は別の店舗でも持ちたいことがあるのでは？
複数店舗で持ちたい項目はcustomersテーブルの属性として用意して、必須ではなくする、という方法でもよいと思います。
挙げられている例が悪いと思いますが、「誕生日」や「身長」は何に関連する属性かを考れば、店舗と関係して持たせるということは、店舗ごとに同じ顧客に対して複数の値を持つことになっておかしなことになりますよね？

例えばその店舗が発行するクーポンを持っているか、とかその店舗でしか意味のない情報を登録するテーブル、になるのであれば、customsテーブルの考え方は私は悪くないと思いますよ。型で悩むならstore_idとcustomer_idをキーにjsonデータを保持するテーブル、とかしてもよいと思いますし。

Answer 8

DBは専門じゃないですが、自分だったら顧客ごとの身長を管理するcustomers_tallテーブルと生年月日を管理するcustomers_birthdayテーブルを作って、店舗ごとにSQLで必要なテーブルを結合させることを考えますね。

考えた方法だと、後で身長と誕生日の両方が必要になった時に頭を抱えそうな気がします。