はじめに

今回の記事は、「実際に世に出ている有名アプリのデータベース設計を勝手に妄想して取り組んでみたので設計フローをまとめよう！」というコンセプトです。
背景としては、達人に学ぶDB設計徹底指南書という人気書籍を読破したので、アウトプットの1つとして取り組みました。

今回の対象：Starbucksアプリ

・最近インストール使い始めたアプリかつ
・UI事例がまとまったサイトUIPocketに画面のスクショがあった
という理由で選定しました！

論理設計って何？？

早速、Starbucksアプリの設計をしていきますが、、
とその前に、論理設計が何かを解説していきます。

1つのアプリケーションを構築する上で、どんなデータが必要になるのか、データをどんな構造で管理すればいいのか、データ間の関連性はどのようになっているのかを決めていく必要があります。この作業が論理設計です。

論理設計は、以下のフローで実施されます。

エンティティの抽出
エンティティの定義
正規化
ER図の作成

1. エンティティ抽出

超簡単に言うと、どんなデータを扱えばいいかの集合体を抽出します。

例えば、「顧客」、「社員」、「商品」、「注文履歴」など
扱うべきデータの集合体を抽出していく作業です。
つまり、データベースにおける1テーブル単位で抽出していく過程になります。

ある程度アプリケーションの機能や要件が固まってきたタイミングで実施していきます。
今回の記事では、アプリのUIからエンティティの抽出をしていきます。

2. エンティティ定義

先ほど抽出したエンティティに対して、具体的にどんなデータを保持する必要があるかを定義していく作業です。

「顧客」というエンティティを抽出している状態だとします。これだけでは、「顧客」エンティティに対して具体的にどのようなデータを保持しているかまでは決定していません。

「顧客」には、
・氏名
・連絡先
・企業名
などのデータを含んでいると考えられます。
このように、具体的にエンティティで管理する必要があるデータを定義していく作業を指します。

3. 正規化

正規化は、データの冗長性を排除したり、データ更新に伴う整合性の担保を目的とした作業です。

複数のエンティティで同じデータ内容を保持してしまうケースも多くあると思います。この時、複数箇所で同じデータを所持することで以下のデメリットがあります。
・データ領域を無駄使いしている
・データ更新漏れなどにより整合性が無くなる

これを排除するためにテーブルを分割していく作業が正規化です。
正規化の詳細については、別記事でまとめていますので合わせて見てください！

4. ER図の作成

正規化を行うと、エンティティの数が増えていきます。
これに伴い、データの関連性が分かりにくくなってしまうケースが頻繁にあります。

これを解決するために、エンティティ間の関連性を図にしたものがER図です。
（イメージとなる記事）

Starbucksアプリの論理設計をしてみる！

論理設計を進めていく上でインプットとなる情報については、先ほども記載したUIPocketから特定のアプリ画面を使用していきます。

主な対象画面としては、
・会員登録画面
・Home画面
・Rewards画面
・eTicket画面
・Stores画面
・Order画面

としてます。

会員登録画面

Home画面

Rewards画面

eTickets画面

Stores画面

Order画面

1. エンティティの抽出

データベースに保存すべきエンティティの抽出を画面ごとに実施していきます！！

会員登録画面

・ユーザー
・カード情報
・カード種別

会員登録画面では、上記の3種類のエンティティが必要かと思います。

登録時に会員情報を登録するための「ユーザー」、
ユーザーが所持しているか保存する「カード情報」、
どんなカードの種類を所持しているかを保存する「カード種別」

などを抽出しました。

Home画面

・リワード
・ホームカード

Home画面では、上記2種類のエンティティが必要かと思います。

ユーザーが保持するポイント数を保存するための「リワード」（ユーザーorカード情報に集約できるかも）、
ホーム画面に表示する広告などのカードUIを保存する「ホームカード」

などを抽出しました。

Rewards画面

・リワード履歴
・eTicket種別

Rewards画面では、上記2種類のエンティティが必要かと思います。

獲得or使用したポイントの取引履歴を保存するための「リワード履歴」、
交換可能なeTicketを保存するための「eTicket一覧」

などを抽出しました。

eTicket画面

・eTicket種別
・取得eTicket

eTicket画面では、上記2種類のエンティティが必要かと思います。

交換可能なeTicketを保存するための「eTicket一覧」（Rewards画面と同様）、
ユーザーが取得しているチケットを保存するための「取得eTicket」

などを抽出しました。

Stores画面

・店舗情報

Stores画面では、上記1種類のエンティティが必要かと思います。

店舗に関する情報を保存するための「eTicket一覧」

を抽出しました。

Order画面

・店舗情報
・お気に入り店舗
・商品一覧
・カスタマイズ一覧

Order画面では、上記4種類のエンティティが必要かと思います。

店舗に関する情報を保存するための「eTicket一覧」（Stores画面と同様）、
ユーザーがお気に入り保存している店舗を保存するための「お気に入り店舗」、
スタバで取り扱っている商品を保存するための「商品一覧」、
各商品のカスタマイズ内容を保存するための「カスタマイズ一覧」

などを抽出しました。

以上でエンティティの抽出は完了です。

2. エンティティの定義

次に、抽出した各エンティティがどのようなデータを所持する必要があるかを定義していきます。

1. 「ユーザー」エンティティ

2. 「カード情報」エンティティ（「リワード」を吸収）

3. 「カード種別」エンティティ

4. 「ホームカード」エンティティ

5. 「リワード履歴」エンティティ

6. 「リワード獲得種別」エンティティ（新たに追加）

7. 「eTicket種別」エンティティ

8. 「取得eTicket」エンティティ

9. 「店舗情報」エンティティ

10. 「お気に入り店舗」エンティティ

11. 「商品一覧」エンティティ

12. 「店舗別販売商品」（新たに追加）

13. 「商品別カスタマイズ」（新たに追加）

14. 「カスタマイズ一覧」

以上でエンティティの定義を完了です。アプリ機能を実現するために各エンティティで所持すべきデータを定義しました。

また、エンティティの抽出時点では考慮できていなかった、
・取引したリワードの修理
・店舗別で取り扱う商品
・商品別のカスタマイズ種類

を6,12,13のエンティティで管理できるように追加しました。

3. 正規化

これまで、エンティティの抽出と定義を実施してきました。
アプリ機能を実現するためのデータは管理できるようになっています。しかし、正規化が実施されていないため、データに冗長性が残っています。

このパートでは、正規化の実施をメインテーマに取り扱い、正規化以外にも問題点が残されている箇所の改善も同時に行っていきたいと思います。
正規化については、別記事でも過去に取り上げましたので合わせて見ていただく嬉しいです。

定義したエンティティ14種類それぞれに対して実施していきます！

1. 「ユーザー」エンティティ

こちらのエンティティには以下の修正点があります。
・以下の推移的関数従属が存在する
　　　{メールアドレス} → {郵便番号} → {都道府県, 市区町村, 番地}

・カード所持のカラムに関してデータに冗長性がある（カード番号の値の有無で特定できるため）

・主キーが可変な値になっている
　　　→ 主キーの値が変更したときに、他テーブルへのデータ更新処理が発生してデータ不整合のリスクが生じる
　　　→ メアドはユーザーによる設定変更が比較的容易に起こりうる

上記の修正点を改良した新たなエンティティは以下になります。

ユーザーテーブル

郵便番号テーブル

2. 3. 4. 「カード情報」, 「カード券面」, 「ホームカード」エンティティ

正規化やテーブルの修正は不要。
（逆に何か助言あれば教えてください。）

5. 「リワード履歴」エンティティ

こちらのエンティティには以下の修正点があります。
・主キーが一意ではない
　　　→ リワードの獲得や利用履歴はユーザー１人につき1レコードではない

・以下の推移的関数従属が存在する
　　　{ユーザーID} → {取引年, 取引月, 取引日} → {失効年, 失効月, 失効日}
　　　→ 取引年に＋3年したものが失効年だと分かる

・有効期間が途中で変わる可能性が考慮されていない

・リワードが失効しているか否かがすぐに判定できない
　　　→ アプリ内でリワード取引履歴などを載せる際に現在日と失効日を比較する計算がアプリ側に必要
　　　→ 失効済フラグなどで失効状態を管理することで計算不要で一目瞭然となる

上記の修正点を改良した新たなエンティティは以下になります。

リワード履歴テーブル

有効年数or失効年はどちらかだけ存在すれば良いので冗長であるが、以下の利点があるため許容する。

・有効年数があることにより、途中で有効年数が変化した際を考慮できる
・失効年があることにとり、特定の日付のデータを直接取得できる（失効が近いポイントなどを容易に取得可）

一方で、同一情報の二重管理によるデータの不整合リスクなども懸念としてあり得る。
1つに統一したい場合は、有効年数を排除して失効年のみを残す方針が良いと考えられます（有効年数だけでは失効年の再計算が頻繁に発生する可能性があり負担が大きい）。

6. 7. 8. 「リワード獲得種別」, 「eTickets種別」, 「取得eTicket」エンティティ

正規化やテーブルの修正は不要。
（逆に何か助言あれば教えてください。）

9. 「店舗情報」エンティティ

こちらのエンティティには以下の修正点があります。
・主キーが可変の値になっている
　　　→ 店舗移転などに緯度経度が変わる可能性がある

上記の修正点を改良した新たなエンティティは以下になります。

店舗情報テーブル

10. 「お気に入り」エンティティ

正規化やテーブルの修正は不要。
（逆に何か助言あれば教えてください。）

11. 「商品一覧」エンティティ

こちらのエンティティには以下の修正点があります。
・主キーが可変の値になっている
　　　→ 商品名はリニューアルすることもあり得る

・第1正規化が実施されていない
　　　→ 新しいサイズが追加されたときにテーブル構成を修正する必要が発生してしまう
　　　→ HOT_ICEDカラムには、1テーブルに2つの意味を持ってしまっている
　　　→ NULLが多く存在し無駄なデータ領域を取りすぎてしまう

上記の修正点を改良した新たなエンティティは以下になります。

商品一覧テーブル

サイズ一覧テーブル

商品価格一覧テーブル

提供形態一覧テーブル

商品提供一覧テーブル

12. 13. 「店舗別販売商品」, 「商品別カスタマイズ」エンティティ

正規化やテーブルの修正は不要。
（逆に何か助言あれば教えてください。）

14. 「カスタマイズ一覧」エンティティ

こちらのエンティティには以下の修正点があります。
・主キーが可変の値になっている
　　　→ カスタマイズ名と選択肢は名称が変わる可能性がある

・一部カスタマイズの選択肢名称だけ変更した場合に対応できない
　　　→ 「氷の量」の「普通」が「標準」に変更した際に選択肢名を修正すると、別のカスタマイズの「普通」も変わってしまう
　　　→ 他カスタマイズにも影響が出ないようにカスタム名称を設定できるようにしたい

上記の修正点を改良した新たなエンティティは以下になります。

カスタマイズ選択肢一覧テーブル

カスタマイズ名テーブル

選択肢名テーブル

以上で、正規化やテーブルの修正は完了です。

4. ER図の作成

最後に、ER図を作っていきます。

先ほどまでは、各テーブル（エンティティ）の正規化・修正を実施してきました。
特に正規化は、結果としてテーブル分割を行うことになるので、当初よりもテーブル数が増加しています。
（今回は計21テーブル）

多くのテーブルになってくるとテーブル間の関連性が理解しにくくなってしまいます。アプリケーション設計・実装をしていく際も、ある機能を実装するためにどのテーブルを参照して、どのデータを抽出し、どのデータを結合すれば良いのかが見えてきません。

そこで、ER図を作成してテーブルの全体像を視認性を向上していきます。

作成方法や記法の説明は今回は省略させていただき（参考：イメージとなる記事）
アウトプットのみ以下にまとめます。

まとめ

データベースの論理設計のアウトプットとして、実際のアプリを例に勝手に論理設計をしてみた！
という感じで、締めさせていただきます。

ではまた！！！