RDBの論理設計でモデリング以外で重視すべきポイントの3回目はプロパティ情報とトリガーをテーマにする。
DB設計を標準化して全テーブルに共通するカラムを設けることがある。全テーブルに共通するカラムのことをここではプロパティ情報と呼ぶ。
また、プロパティ情報をRDBのトリガー機能を用いて設定するメリットも説明する。
プロパティ情報
どのような情報をプロパティ情報として定義すべきか。ここでは「定義すべき情報」「定義した方が好ましい情報」に分けて説明する。
定義すべきプロパティ情報
| カラム | データ型 | 設定タイミング | 内容 |
|---|---|---|---|
| ステータス | Number(1) | Insert・Update時 | 業務要件に応じて定義。以下は一例。 0:有効 1:無効（いわゆる論理削除） |
| 初期登録源泉分類 | Char(1) | Insert時のみ | 業務要件に応じて定義。以下は一例。 B:バッチ F:フロント |
| 最終更新源泉分類 | Char(1) | Insert・Update時 | 業務要件に応じて定義。以下は一例。 B:バッチ F:フロント |
| 更新カウンタ | Number(4) | Insert時のみ | Insert時は0。Update時はインクリメント。 最大値を超えれば0。 |
| 初期登録日時 | DateTime | Insert時のみ | Insert時の日時 |
| 最終更新日時 | DateTime | Insert・Update時 | Insert・Update時の日時 |
- 上記は論理カラム名だが、物理カラム名も全テーブルで統一する。
- 「ステータス」「初期登録源泉分類」「最終更新源泉分類」については前回投稿したモデリング以外のRDB論理設計 ～ ステータス管理編 ～を参考にして欲しい。なお今回もフロントという言葉は、「サービスの利用者」や「サービス提供者側の運用に携わる人間」がブラウザや専用アプリを用いてシステムを操作することの意味で用いる。
- 「更新カウンタ」は更新系処理の排他制御機能を実現するために用いる。例えば通販サイトのフロントで同じ商品の単価を、Aさんは1000円から1100円に、Bさんは1000円から1200円に変更すると仮定する。そして次のように操作されたとする。
| 順序 | 操作 | 備考 |
|---|---|---|
| 1 | Aさんが更新対象の商品をブラウザに表示する。 | 単価は1000円で表示される。 商品マスタの更新カウンタは10とする。 |
| 2 | Bさんが更新対象の商品をブラウザに表示する。 | 単価は1000円で表示される。 商品マスタの更新カウンタは10とする。 |
| 3 | Aさんが単価を1100円に変更して更新する。 | 更新は成功し、更新カウンタは11になる。 |
| 4 | Bさんが単価を1200円に変更して更新する。 | 更新失敗（第三者に更新されたので最初からやり直してください、のようなメッセージを通知） |
- 「更新カウンタ」は、このようなケースで 4 の更新を禁止するために活用する。（更新カウンタはブラウザに表示するタイミングでセッション変数や隠しタグ等に設定され、更新時に「ブラウザ表示時と同じであること」をチェックする。）
- 「初期登録日時」「最終更新日時」は様々な調査に役立つ情報である。開発時もこれを活用すれば便利な検証ツールが作成可能である。（例えばXという入力によりテーブルAのID=1のレコードと、テーブルBのID=1のレコードが更新されると仮定する。このXのテストでテーブルCやテーブルD、あるいはテーブルAとテーブルBのID=1以外のレコードが更新されないことの証明は、どうすべきだろうか。興味のある人は開発を容易にするRDB設計を参考にして欲しい。）
- アプリ開発用フレームワークのORMで「created」や「modified」等のフィールドを定義し、ORMを用いて更新すると「初期登録日時」「最終更新日時」と同じ情報が自動で設定されるが、あくまでもORMを用いて更新した場合のみである。トリガーの場合、どのような方法で更新しても必ず設定される。
定義したほうが好ましいプロパティ情報
- 「このレコードを登録・更新・削除したのは誰か？」等を調査したいことは少なくないと思う。このような調査を容易にするため、可能であれば「初期登録クライアント情報」「最終更新クライアント情報」を定義したほうが好ましい。
- いずれも定義すべきプロパティ情報の「初期登録日時」「最終更新日時」設定時の情報とする。
- どのような内容を設定するかはシステムの内容に応じて決定する。（フロントであれば「ログインユーザーID」「URL」「クライアントIP」、バッチであれば「ジョブ、タスク、シェル、プログラム名」等）
トリガー
多くのRDBが標準で備えているが、おそらくあまり活用されていない便利な機能の代表がトリガーである。
- トリガーとはテーブルに対してInsert・Update・Deleteが行われた際、事前に作成したストアドプロシージャ等のプログラムを実行する機能である。
- プロパティ情報（ステータスを除く）のように、ビジネスロジックと無関係かつ必ず設定しなければならない情報を確実に設定するようなケースではトリガーが効果を発揮する。
- トリガーの実装方法はRDBの種類ごとに異なる。（ここでの詳細な実装方法の解説も割愛する。）
- プロパティ情報は次のように設定する。
| カラム | 設定方法 |
|---|---|
| ステータス | トリガーでは設定せず、アプリケーション側で設定する。 |
| 初期登録源泉分類 | 後述トリガー方式Bで設定する。 |
| 最終更新源泉分類 | 後述トリガー方式Bで設定する。 |
| 更新カウンタ | 後述トリガー方式Aで設定する。 |
| 初期登録日時 | 後述トリガー方式Aまたはトリガー方式Bで設定する。 |
| 最終更新日時 | 後述トリガー方式Aまたはトリガー方式Bで設定する。 |
| 初期登録クライアント情報 | 後述トリガー方式Bで設定する。 |
| 最終更新クライアント情報 | 後述トリガー方式Bで設定する。 |
トリガー方式A
- DBサーバー内の情報だけを参照する方式。トリガーのマニュアルや解説サイト等を参考にすれば実装できる。
- 「初期登録日時」「最終更新日時」をこの方式で設定すると、DBサーバーの日時が設定される。
トリガー方式B
- トリガー内でアプリケーションサーバー側の情報（フロントのログインユーザーID等）を参照する方式。一時テーブル（TEMPORARY TABLE）が扱えるDB（PostgreSQL等）と、そうでないDB（Oracle）では実装方法が異なる。（※2020.04.16追記。初期のOracleでは一時テーブル機能は存在しなかったが、おそらくバージョン8以降は扱えるようになった。但しトリガー内でアプリケーションサーバー側の情報を参照するという目的であれば、後述パッケージ方式のほうが好ましい。）
- 一時テーブルが扱えるDBでは、DBサーバーとのセッション開始時、アプリケーションサーバー側の情報を一時テーブルに登録する。（トリガー内では一時テーブルからアプリケーションサーバー側の情報を取得する。）
- Oracleでは一時テーブルの代替機能として事前にパッケージを作成し、DBサーバーとのセッション開始時、アプリケーションサーバー側の情報をパッケージ変数として設定する。（トリガー内ではパッケージ変数からアプリケーションサーバー側の情報を取得する。）
- DBサーバーとのセッションをコネクションプール型で使用する場合、上記の「DBサーバーとのセッション開始時」は「プールされたDBサーバーとのセッション使用開始時」に読み替える。（一時テーブルまたはパッケージ変数の初期化を忘れずに行う。）
- 「初期登録日時」「最終更新日時」をこの方式で設定すると、アプリケーションサーバー側の日時が設定可能になる。
以上最後まで目を通して頂き、ありがとうございました。