実践データベース設計サブ資料：③物理データモデリング

2024年度リクルート エンジニアコース新人研修の講義資料です

Transcript

1. サブ資料③：物理データモデリング（R書店） 物理データモデルについて 物理データモデルとは特定のデータベース（MySQL, Oracle, DB2, SQL, Server…etc）を前提とした 物理的なデータ仕様を定義したデータモデルで、テーブル、属性、キー制約、別名、インデック ス、ビューといった実際のデータベース構築に必要な詳細情報を定義する ・Step1 物理データモデルの作成 ・Step2 プロセス分析 ・Step3 データアクセス分析 ・Step4 属性の物理名定義 ・Step5 各種ルールの定義 ・Step6 インデックス、トリガー等の定義 ・Step7 非正規化および導出項目の追加 物理データモデリングで行うこと 論理データモデル、アーキテクチャ設計書等を基に物理データモデルを作成する 最初に物理データモデルに対し、人工キーへの変換を行う。また、マスタの管理方針と削除方針を 決定して反映するとともに、例えば削除フラグやタイムスタンプなど、物理目的の属性を追加する ※個別の物理モデルについては後ほど詳述 Step1 物理データモデルの作成 1 ページ
2. パフォーマンスが重要となるプロセスを抽出して、以下の事項を整理する ・処理形態（リアルタイム処理 / バッチ処理 / オンラインバッチ） ・処理件数 ・連動して更新する他のデータ（エンティティ） ・連動して参照する他のデータ（エンティティ） Step2 プロセス分析 前期Step2の整理内容に基づき、アクセス効率や検索の容易性を検討する Step3 データアクセス分析 属性を物理名に変更する。併せて、論理データモデルで定義したデータタイプの分類に基づき、 データタイプと桁数を定義する 物理目的のみに使用する属性についても追加する ※個別の物理モデルについては後ほど詳述 Step4 属性の物理名定義 「参照整合性制約」「一意性制約」「主キー制約」「NOT NULL制約」「値制約」等の制約ルールに ついて定義する Step5 各種ルールの定義 インデックス、トリガーの定義を行う（※今回は省略） Step6 インデックス、トリガー 非正規化の実施には慎重を要する。このフェーズではたとえば以下のような作業を実施する ・番号採番データを追加（物理目的エンティティ、物理目的属性を追加） 例えばサロゲートキーなど ・区分名称データを追加（物理目的エンティティ、物理目的属性を追加） 例えばフラグ類や区分など ・導出情報、処理制御情報を追加（物理目的エンティティ、物理目的属性） ・1対N関係にあるエンティティ間において、1にあたるエンティティの属性をN側に重複して持たせ る（冗⾧化） ・複合キーがある場合、人工的なキー（サロゲートキー）へ変換する..etc Step7 非正規化および導出項目の追加 2 ページ
3. 補足：命名の重要性 命名はDB設計の質を左右するため、テーブル名や属性名はしっかりと吟味して付け、 安易に省略しない（省略する場合も一般的な略称を使用） 概念の理解が非常に重要 ※ローカルルール多用によるメンタルマッピング（言葉の翻訳や置き換え）の負担を減らす ※データベースの命名はアプリケーション設計にも影響する一方で、一度物理設計して構築してし まうと後で変更しずらい 補足：テーブル名の作法 テーブル名は基本"複数形" テーブルには複数のレコードが格納される前提であるため、usersやordersのように複 数形を使用するのが一般的 ※但し情報（Infomation）やデータ（data）等の複数形が無い場合は例外 プレフィックスについて 主に業務系システムの開発現場においてマスタ系（リソース系）はM、トランザク ション系（イベント系）はTをそれぞれテーブル名の前に付ける慣習があるが、現在は その縛りはない。必要性のないプレフィックスやサフィックスはつけない方が無難で あるが、プロジェクトの状況や内部ルールとの兼ね合いになる 個々のクラスや関数、プロパティが持つ名前はアプリケーションの構造を支える重要な要素 両者とも日本語では"状態"を表すが、意味合いが異なるので注意 Stateは主に内部状態を表し、Statusは外部から客観的に確認できる状況や位置づけを表す 命名の一例：状態（StatusとState） 名前 名前 名前 名前 名前 アプリケーション 名前 名前 名前 名前 名前 名前 データベース 名前 3 ページ
4. 補足：データの桁数の指定について 不要に桁数を冗⾧に取り過ぎない（リソース効率の観点以外にも担当者が交代になる際に「何故こ の桁なのか」と混乱を招いたりする） 補足：具体的なテーブル名か抽象的なテーブル名か 利点（＝明確性） 例えば"商品テーブル"を例に挙げるとproductsに対して、customers や books のような具 体的な名前は、そのテーブルが何を表しているのかが明確になる。これにより、開発者 や新しいチームメンバーがデータモデルを理解しやすくなる（＝明確性） また、具体的な名前は、そのアプリケーションに特有のコンテキストやビジネスドメイ ンに適合しているため、その領域に特化した操作やクエリがしやすくなる（＝コンテキ ストの特定） 欠点（＝柔軟性の欠如） テーブル名が非常に具体的である場合、将来的に異なる種類のデータを格納する必要が 生じたときに、名前が不適切になる可能性がある。例えば、books テーブルに雑誌や新 聞を含めたくなった場合や単一のテーブルで文具や家電等の他のカテゴリーの商品を扱 いたくなった場合に名前が適切でなくなる可能性がある 具体的なテーブル名の利点と欠点 利点（＝柔軟性） users や products のような一般的な名前は、多種多様なデータ項目を含む可能性がある ため、将来的な変更に対して柔軟といえる。これにより、アプリケーションの拡張も容 易になる 欠点（＝曖昧さ） 一般的な名前は、テーブルの内容が何であるかを具体的に示さないため、データモデル の理解が難しくなる（人によっては理解がズレたりもする）ことがあり、特に大規模な データベースで問題となることもある 一般的なテーブル名の利点と欠点 補足：NULL値やデフォルト値の許容について それぞれ⾧短があるため、必要に応じて選択する NULL値の許容について NULL値はデータの集計やクエリ処理において、予期しない結果が生じる可能性があるため、なるべく NULLが生じない設計が一般的に良いとされているが、無理に具体的な値や空文字等で埋めるとデータが 汚れたり、不整合が生じる原因になるので、設計時には注意 （次ページへ続く） 4 ページ
5. 補足：大規模システムにおけるID設計 複数のインスタンスとDBが前提 大規模システムでは複数台のサーバとDB分割（垂直・水平）を組み合わせるため、UUID（注：複数 バージョンあり）等を使用して異なるインスタンスやタイムゾーンで重複しないIDを作成する必要があ る デフォルト値について Pros：データの整合性が取れやすくなる他、デフォルト値設定によるデータ挿入を省略することで、ア プリケーション側の設計もシンプルになることもある Cons：デフォルト値の必要性がない場合、データが汚れてしまう等のデメリットがある 補足：状態（ステータス）やコード値の型について 「ステータス値が1や2の場合、直感的に理解しにくいので文字列で」という見解もあるが、ソース コードの可読性とデータベースに保存する値の形式は別の問題 安易に文字列を使用すると、値の幅が広がり、想定外の値（例えば"Completed"と"Compled"等の typo等）が入ってデータの整合性がとりにくくなる等の運用上の問題が生じたり、その調査に時間 がかかったりする可能性がある 補足：トレーサビリティの考慮 トレーサビリティ（追跡可能性）は、データがどのようにして生成され、どのように変化したのか を明らかにし、データの正確性、完全性、および信頼性を保証する上で非常に重要。特に、データ が多くの手を経て操作される場合や、規制が厳しい業界（金融、医療、政府など）等では、データ の変更記録を厳密に行っていることが多い。また、各テーブルにcreated_at と updated_atをデフォ ルトで生成するフレームワーク等も少なくない。 なお、履歴を保持するために、UPDATE文をなるべく使用しない（INSERTを用いる）、イミュータブ ルなテーブル設計も推奨されるが、テーブル数やレコード量がかさむ等のデメリットもあるため、 今回はUPDATE文を使用した設計とした （次ページへ続く） 5 ページ
6. 途中でキャストされると桁落ちするリスクがある。 例えば"001"のような先頭に0が付いた数値のみの文字列は数値にキャストされ、桁落ち（例え ば"001"が1になる）するリスクがあり、文字列として不完全であるため、IDとして使用するの を避けるのが無難 受け手側の環境（フレームワークやライブラリ、スプレッドシート等のアプリ..etc）に依存す るID設計にはしないことが大事。0から始める場合は、必ず数値以外の文字列を含めたり、頭 を0以外の数字にする 0から始まる数字のみの文字列は使用しない 処理途中で競合する更新によってデータ不整合が起こらないようにDBでロックをする。例えば以下 のような場合 ・顧客のステータス変更時：顧客のレコードのロック ・商品在庫更新時：商品在庫のレコードのロック （次ページへ続く） システムのプロセス上、処理途中で競合する更新によってデータ不整合が起こるリスクがあるため、 排他制御で競合が起こらない仕組みにする必要がある。例えば以下のような場合（顧客起因の操作に おいては対応は必須） ・ステータス変更時 ・商品在庫 ・顧客情報のSelect時等 補足：大規模システムにおけるDBの排他制御について 排他制御の方法 大規模システムのおける排他制御の必要性 排他制御は大きく二つに分かれる ・アプリケーション上の機構（セマフォやMutex等） ・DBのロック アプリケーションを複数インスタンスで立てる場合は、通常データベースのロックを使用する DBレコードのロック 6 ページ
7. MyBatisでは、ロック付きでデータを取得するにはFOR UPDATE句を使用することができる PostgreSQLの場合、MyBatisでロック付きでデータを取得する場合のクローズ処理は、通常の クエリ実行と同様に行われる。MyBatisは、クエリの実行が完了した後に自動的にリソースを 解放します。このため、クローズ処理については特別な処理は必要はない ただし、MyBatisを使用する際には、適切に SqlSession を開始し、クエリ実行後に適切にク ローズすることが重要。 try (SqlSession session = sqlSessionFactory.openSession()) { // ロック付きのデータ取得クエリを実行 YourEntity result = session.selectOne("yourNamespace.yourQuery", yourParameters); // 取得したデータの処理 } // クローズ処理はここで自動的に行われる <!-- UserMapper.xml --> <mapper namespace="com.example.UserMapper"> <select id="selectUserForUpdate" resultType="com.example.User"> SELECT * FROM users WHERE user_id = #{userId} FOR UPDATE </select> </mapper> MyBatisでの実装例 BEGIN; SELECT quantity FROM product_inventories WHERE product_id = 'specific_product_id' FOR UPDATE; UPDATE product_inventories SET quantity = quantity + 1 WHERE product_id = 'specific_product_id'; COMMIT; ロック（悲観的ロック）について ロックの解放について 7 ページ
8. 今回使用のDB H2 DB（Javaのアプリケーションを実行中のみ存在するDB）のPostgreSQL Modeで作成 アプリケーションが起動中のみDBが有効になる仕組み 今回使用のORM(MyBatis) ObjectMapperとしてMyBatisを使用（JPAを使用した実装とは異なる） 顧客テーブルの設計 テーブル名 customers / users 一般的な顧客以外のユーザにもログイン可能とするような場合に、customersでは実態 と合わなくなるため、customersから名称を変える余地はある ※今回はusersを選択 ER図（全体） 詳細はFigmaを参照 補足：ON DELETE CASCADEについて ON DELETE CASCADEとは 参照先のテーブルの行が削除されると、関連する参照するテーブルの行も自動的に削除される 指定で、データベース内の整合性が維持され、孤立したレコードが残ることが無くなる半面、 意図しないデータ削除のリスクも生じるため使用に際しては注意が必要。 また、対応していないDBも存在する。そのためDBの仕様の確認や使用の必要性、設計上の矛盾 がないかの確認は必須 8 ページ
9. テーブル定義（users） その他考慮事項 ・論理削除の配慮を加えた（論理削除は削除日時の有無で判断したり、Boolean等のフラグで管理す る） ・Eメールアドレスにユニーク属性を追加 ・ユーザ種別（user_type）を追加した（文字列型より数値の方が望ましい） ・ユーザのステータスの管理もできるようにした（文字列型より数値型の方が望ましい） ・氏名はFirst Nameとlast Nameに分けた ・表示用のユーザ名（user_name）とパスワード（hashed_password）の属性も追加 データ例 パスワードの管理について セキュリティ上の配慮からパスワードは平文のまま保存しないことがポイント 対応としては暗号化やハッシュ化が考えられるが、暗号化は可逆的で、暗号化したデータを復号化 すれば元のデータを取得できる。 一方、ハッシュ化は一方向的で元のデータを復元することはで きない ※ハッシュ化したパスワードを保存して、ユーザのログイン時に送信してきたパスワードを同じ ハッシュ関数でハッシュ化してハッシュ化した値同士の一致を確認するのが通常 9 ページ
10. 住所テーブルの設計 ・ユーザテーブルのレコードと1対多でつなげる ・別人で配送用に指名の属性を追加 テーブル定義（addresses） 出版社テーブルの設計 テーブル定義（publishing_companies） データ例 主キーの命名について 配送会社のテーブルも存在するため、主キーはcompnay_idではなく、publisher_idとした 10 ページ
11. 著者テーブルの設計 姓と名の間に・が付いたり、空白で氏名を分けたり、どちらかが欠けているケース （ペンネーム等）もあるため、著者テーブルでは氏名は分けずに1つのカラムで管理す る テーブル定義（authors） データ例 商品テーブルの設計 テーブル定義(products) ・出版社(publishing_companies)と著者テーブル(authors)にリレーションシップを貼る 11 ページ
12. 商品在庫テーブルの設計 テーブル定義(product_inventories) カートテーブルの設計 テーブル定義（carts） ・商品テーブルとリレーションを貼る データ例 ・後述のカートCookieデータテーブルと連携させる 12 ページ
13. 未登録ユーザがカート追加できるようにする考慮 カートはユーザ毎に存在するので、カートへの商品追加を登録済みユーザのみ対象と すれば、ユーザIDと紐づけられるが、未登録ユーザはユーザIDを持たないため、なん らかの方法で対応する必要がある 今回はCookie情報の管理用のテーブルを用いた ※ユーザIDを持たない 出典：https://icooon- mono.com/i/icon_11319/icon_113 190.svg カート 出典：https://icon-pit.com/wp- content/uploads/2018/11/shopping- cart_icon_1479.png 商品を追加 未登録ユーザ カートCookieデータテーブルの設計 テーブル定義（cart_cookie_data） データ例 今回はCookie値の有効性をチェックするために有効期限（expires_at）も設けた 13 ページ
14. カート商品テーブルの設計 パフォーマンスへの考慮 商品別の小計（商品価格×数量）の計算のリソースへの配慮 【ボトムアップ視点】カート詳細別の小計の計算は一度限りで十分。何度もやらせない 配慮必要 amountは商品価格と数量から算出できるが、注文は確定後変化しないため、都度計算さ テーブル定義（cart_items）※中間テーブル 注文テーブルの設計 テーブル定義(orders) ・カートと商品とリレーションシップを貼る ・注文の総額は再計算の手間を減らすために追加 ・注文のステータスは決済や配送状況によって影響を受ける 14 ページ
15. クレジットカードテーブルの設計 テーブル定義（credit_cards） カードのブランド毎にカード番号の特定の桁数は決まってくるが、 カードブランド毎の判別や画像の出しわけ等を考慮する場合、カードブランドの種別 も含める カードブランドの判別とブランド画像表示への対応 注文商品テーブルの設計 テーブル定義(order_items) ・小計（subtotal_amount）は再計算の手間を減らすために追加 ・CVV（セキュリティコード）は3桁の数値だが、数値よりも3桁の文字列である側面が強 い（0から始まるCVV多い）ので、今回は文字列で保存 カードブランド 数値型ではなく敢えて文字列を選択 15 ページ
16. 決済テーブルの設計 quantity(量)とamount(量)の違い quantityの方がより具体的な物の数（商品の数等）を指すことが多い R書店では金額：amoount、商品在庫数：quantityを採用 テーブル定義（payments） 所要日数等のカラムも必要に応じて追加 配送会社テーブルの設計 配送会社に必要な属性を追加 テーブル定義(freight_companies) 運送会社と物流会社は違うので注意。 物流会社と運送会社の役割は、同じようであって実は少し異なる。運送会社はトラック などで荷物を輸送することに特化しており、荷物をいかに効率的かつ安全に配送するか が重視される。 一方、物流会社は物流に関するあらゆる業務を担うことを目的に運営されている。そこ で重視されるのは、自社が提案した物流体制によって荷主の業務効率を改善し利益につ なげることができるかどうか ・決済情報は注文（orders）に対してON DELETE CASCADEは設定しない 物流会社と運送会社の違い 16 ページ
17. 配送テーブルの設計 テーブル定義(delivery_info) ・他のテーブルとの関係性や自身のデータの重要性を考慮してON DELETE CASCADEは省 いている 運送会社 - Weblio 英和・和英辞典 transportation companyやfreight company当てはまりそう 運送会社って英語でなんて言うの？ - DMM英会話なんてuKnow? transport companyやshipping companyが多そう ※shipping companyは本来は海運会社を指す言葉だったが、運送会社を指す言葉として慣 用的に使用されることが多く、本来の意味とが違ってきている 運送会社（配送会社）の英訳について 17 ページ