複雑さと戦う : データの入れ物 テーブル 

データベースのテーブルに、カラムがずらっと並んでいたら、たぶん、テーブルを分割すべき。
オブジェクトで、フィールドが並んでいたら空白行で分けて、グループごとに名前をつけて、別のクラスを抽出するリファクタリングは、テーブルでも同じですね。

スコットアンブラーの データベースリファクタリング も、カラムの多いテーブルをいや臭い、リファクタリングが必要な兆候としている。

私は、カラムが10を超えるテーブルは、大きすぎると思う。異なる性格の情報を無理やり一つのテーブルに押し込んでいる。

クラスのソースコードなら、とりあえず空白行でちいさなリファクタリングするんだけど、テーブル定義は、それよりはかなりたいへん。
リファクタリングの前に、ちゃんと設計しておきたい。

テーブル設計 "いやな臭い"

Scott Ambler の Refactoring Databases から

次のような現象を発見したら、データベースのリファクタリングを考える。
テーブル設計の再検討が必要。

・多目的に使われるカラム
・多目的に使われるテーブル
・重複したデータが別がテーブルに保存されている(不一致)
・カラムが多すぎるテーブル
・レコードが多すぎるテーブル
・特定の桁に意味を持たせたコード(上位3桁が商品大分類...)
・怖くて、データベースの設計は変更できない (末期症状?)

一つの入れもの(カラムやテーブル)、なんでもかんでもつっこんでしまう
「詰め込み症候群」ですね。

洗練された設計は、カラムやテーブルがとてもシンプルな目的だけの
ために存在している。

データーベース自体は、情報を集約して管理する仕組みですが、
それと、カラムやテーブルになんでも詰め込むのは別の問題です。

顧客関連の情報を、とにかく CUSTOMER テーブルにカラムを追加して
なんでもかんでも、詰め込むと、何が起きるか?

・SQL 文が複雑になる
・プログラムが複雑になる
・見通しが悪くなる(障害・データ不整合の調査に時間がかかる)
・変更ができない (変更すると何が起きるか、誰も見通せない)

良いことは何もありません。

大原則
「関心の分離」(ひとつの関心に集中)

クラス設計でも、テーブル設計でも、役割が単純で明快なクラス/テーブルを
設計すること。

交差表 ?

テーブル設計で、「交差表」という言葉をつかったけど、Google で検索したら、359件という超マイナーな表現だった。

出所は、日本オラクル監修 R.バーカー著の CASE*METHOD 実体と関連 にある 交差エンティティです。

原文では、Intersection Entity。

Oracle システム設計 では、リンクエンティティ 原文では、 Intersect (Link) Entity。

Intersect は、 SQLの集合演算のキーワードとかぶるのがちょっと気になる。

マイナーだけど、個人的には「交差表」は、良い用語だと思います。

テーブル設計 データモデリングのエッセンス(5)

ビジネスルールとは

業務を進める上で、さまざまな決めごと、約束事があります。

・販売価格
・15:00までの注文は、翌日配送
・7日前までのキャンセルは、キャンセル料を請求しない
・小口の注文は宅配便、大口の注文は、倉庫から直送
・先着100名は、10%の特別割引をする
・和書と雑誌は、ポイントで割引はできない
・洋書はポイントで割引をする
・送料は商品種類にかかわわらずポイントで割引できる

業務アプリケーションでは、このビジネスルールを明確にして、実装することが、重要課題です。ビジネスルールがうまく実装されたシステムは、使いやすく、ビジネスの強力な道具になります。

ビジネスは生き物ですから、ビジネスルールは状況によって、変化します。この変化に短期間で低コストに対応できるシステムほど、ビジネスにとって、価値の高いシステムになります。

今回は、このビジネスルールの実装をデータベース中心で行う方法をとりあげます。
ビジネスアプリケーションの設計の中核です。もっとも楽しい部分です。

ビジネスルールの実装パターン

業務アプリケーションで、これらのルールを実装する方法は、次の三つがあります。

・プログラムのコードで記述( if 文など )
・ルールをDBのテーブルに記述(価格、割引率など)
・テーブルに制約を宣言する ( NOT NULL, 参照制約 )

アンチパターン

ビジネスルールを、力づくで、その場しのぎで if 文を プログラムコードに追加していく。典型的なアンチパターンですね。バグの巣になりやすく、保守性がどんどん悪くなる。

DDD 、リファクタリング、Spring

ドメインオブジェクトをきちんと設計し、個々のビジネスルールを単純に実装した評価メソッド群を作る。ドメイン駆動設計 ( DDD : Domain-Driven Design )の基本思想ですね。また、Fowler のリファクタリングでは、多くのパターンが、ビジネスルールを分かりやすく記述することに関係しています。例えば、第9章「条件記述の単純化」の例が参考になります。

ビジネスルールをプログラムコードに記述する場合、定数などは、Spring の Bean ファクトリの仕組みを使って、XML ファイルに記述すると変更が容易になります。

ルールテーブル

価格や割引率は、テーブルとして実装することが普通に行われます。少し複雑なビジネスルールをテーブルとして設計・実装する例は、後述します。

ビジネスルールをテーブルとして実装すると、変更や内容確認が容易になります。

データベース制約

データベースの制約は、必須ルールや不変ルールの実装方式として最も厳格で安全な手段です。

【一意制約】

主キーをシステムで生成したサロゲート(代理)キーにした場合、商品番号とか顧客番号という自然キーを一意制約を宣言することがあります。

この情報は重複すべきではない、というルールがあれば、積極的に一意制約を宣言して、データベースに問題データを追加できないようにします。

【NOT NULL制約】

ビジネス上 必須の情報は、データベースで NOT NULL 制約を宣言します。
画面やドメインオブジェクトで必須チェックをしていても、データベースでも NOT NULL 制約を宣言すべきです。それぞれの役割と効果が異なるので、3重であっても、それぞれの必須チェックはすべきです。

画面での必須チェックは、ユーザに誤った入力をフィードバックして、その場で修正できる使いやすさの工夫です。

ドメインオブジェクトの必須チェックは、ドメイン(問題領域)の分析結果から、導かれます。ドメインのロジックとして、当然必須チェックをします。

そのオブジェクトをデータベースに永続化する時「データベース側」でも NOT NULL 制約で、整合性のチェックをすべきです。

ドメインオブジェクトには、バグがあるかもしれない。後で保守したときに、バグが混入するかもしれない。画面処理とバッチ処理で、別々のプログラムが、同じテーブルに書き込みをするかもしれない。データの整合性の最後の砦がデータベース側の制約宣言です。

テーブルは役割ごとに分割します。この設計方針だと、ほとんどのカラムは必須つまり NOT NULL 制約の対象です。
NOT NULL 制約が宣言できないカラムを発見したら、そのテーブルは、おそらく、異なる目的が混在しています。目的が単純で明確なテーブルに分割することを検討してください。

【参照制約】

参照制約は、情報の結びつきを宣言してデータの不整合を防止する仕組みです。

受注明細は、受注番号をキーとして、受注ヘッダと結びついています。
出荷レコードも受注番号をキーとして、受注と結びつきます。

テーブルには結びつきを示唆する参照カラムがあるのに、参照整合性制約(外部キー制約)を宣言していないケースが多い。

他のテーブルを参照するカラムは、かならず外部キー制約を宣言します。データの整合性を確保する簡単で確実な手段です。

ビジネスイベント系テーブルで説明したように、先行イベントと後続イベントの関係は参照性制約を一方向に限定して宣言することで、ビジネスの論理モデルをテーブルに実装します。

外部キー制約を宣言すると、INSERT や UPDATE がうまくいかないことがあります。それが正しい姿です。操作がまちがっているか、テーブルの設計がまちがっているかのどちらかです。

ひとつのテーブルで5つ以上も外部キーカラムがある場合は、おそらくテーブル設計の間違いです。役割を単純に、用途を単純に設計すれば、テーブル間の参照関係は、もっとシンプルになります。

ビジネスルールのテーブル設計:価格

ビジネスルールテーブルの典型は、価格表です。何をいくらで売るかは、ビジネスルールそのものですね。

価格は、キャンペーン期間、数量割引、お買い得セット、予約割引などで、同じ商品でも別価格になるのはよくあるケースです。

○○価格が複数あったら、それぞれの価格を別テーブルで設計・実装することを考えます。
標準価格表、キャンペーン価格表、数量割引ルール表、セット価格表、予約割引ルール表など。

KEY を検索すると、VALUE が見つかる LOOKUP テーブルパターンです。

それぞれのテーブルごとに関心を分離して、プログラムもシンプルな構造になります。また、将来、ルールの廃止・追加・変更があった場合にも、副作用を心配せずにテーブルやプログラムの変更ができます。

一つの価格表に多くの属性を持たせると、問題を複雑にして、バグのリスクが増え、保守や拡張がむずかしいシステムになります。

ひとつのテーブルにすべての価格情報をカプセル化したがるのは、典型的なアンチパターンです。入れ物は目的をシンプルにして、小さな入れ物をたくさんつくるべきです。

多くの価格情報をひとつのグループとして管理する方法がスキーマです。関連するテーブルをカプセル化するための仕組みがスキーマです。

ビジネスルールのテーブル設計:送料テーブル

送料は、重量区分と地域区分の組み合わせで決まるというケースがあります。

縦軸に重量区分、横軸に地域区分を並べた、紙ベースの送料早見表がありますね。

こういう表形式のビジネスルールのテーブル設計は、交差表パターンが定石です。

交差表パターンは、三つのテーブルで構成します。

A. 重量区分の一覧
B. 地域区分の一覧
X. 交差表(送料テーブル)

交差表は、重量区分と1対多、地域区分とも1対多の関係で関連づきます。

交差表パターンは、縦横の表形式で記述できるルールをテーブルで表現する応用範囲の広いパターンです。

デシジョンテーブルと呼ばれる、表で縦横の組み合わせごとに○×で表現するルールも、プログラムのif 文でがんばるのではなく、交差表パターンのSQL問合せで実装したほうが、シンプルだし、ルールの変更も容易になります。

状態遷移図であらわせるルールも、2次元の表形式ですから、交差表パターンがテーブル設計の候補です。

ビジネスルールのテーブル設計:デシジョンツリー

デシジョンツリー型のビジネスルールがあります。
質問にYES/NOで答えていくと、最後には、あなたは○○タイプです、というような診断ゲームにでてくるパターンですね。

テーブル設計としては、自己参照テーブルで、実装は可能です。
もし、このタイプのルールが多く、また、そのルールの変更が重要な業務であれば、ビジネスルールエンジンのパッケージやライブラリの利用も選択肢です。

プログラムコードかテーブルか

最初に書いたように、ビジネスルールは、プログラムコードでも実装できるし、テーブル+SQL問合せでも実現できます。

私は、if 文がずらった並んだプログラムは、分かりにくく、保守しにくいと思っています。多くの場合、ビジネスルールは、テーブル+SQL問合せで実装したほうが、シンプルで拡張や変更に強いシステムになります。

もちろん、ビジネスルール記述のテーブルは役割ごとに分割する設計にします。

Domain-Driven Design (DDD) の Responsibility Layers

ビジネスルールテーブルの多くは、Responsibility Layers パターンの Policy Layer のオブジェクトです。業務遂行上の判断や決定をするために必要な情報を提供します。

Policy Layer のテーブルは、下位の Capabilities Layer であるビジネスリソース系のテーブルを参照します。価格表であれば、商品テーブルへの外部キー参照をします。

商品テーブルからは、上位の Policy Layer の 価格テーブルは見えない存在です。

Domain-Driven Design (DDD) の Knowledge Level

送料テーブルで参照する、重量区分テーブル、地域区分テーブルそのものはビジネスルールではありません。ビジネスルールを構成する知識をDBのテーブルで表現したものです。

ビジネスアプリケーションでは、区分テーブルとか分類テーブルがたくさんでてきます。業務の知識や業務上の判断材料として、半ば無意識に利用されている知識を、テーブルで実装したものです。

これらの区分テーブルは、顧客や商品というビジネスリソース ( Capabilities Layer )のテーブルから外部キー参照されます。また、ビジネスルール( Policy Layer ) のテーブルからも外部キー参照されます。

つまり Responsibility Layers パターン では、うまく整理できない種類の情報が、この区分や分類のテーブルです。

これを整理する DDD パターンが、Knowledge Level です。Layer ではないので、依存関係は一方向に限定されません。
Eric Evans が書いているように、Knowledge Level は Responsibility Layers と組み合わせて使って、ビジネスルールやそれに関する知識を、分離して整理するための有用なパターンです。

テーブル設計 データモデリングのエッセンス(4)

ビジネスリソース

今回はビジネスリソース系のテーブル設計、データモデリングがテーマです。

ビジネスリソースとは経営の資源です。ビジネスを進めていく上で、利用可能であり、また必要不可欠な企業の財産です。

例えば、ヒト(顧客、従業員、取引先)、モノ(商品、倉庫、店舗)、カネ(資金、与信枠)、情報(コンテンツ、ノウハウ)などです。

ビジネスリソースを適切に管理し効果的に利用するためのテーブル設計、データモデリングが、ビジネスの成功要因、競争優位の源泉になりえます。

ビジネスイベントとの関係

前回まで説明したビジネスイベントは、これらのビジネスリソースとの関連で、発生し、記録します。
・受注は「顧客」が「商品」を注文したイベント
・出荷は「商品」を「倉庫」から「顧客」に発送したイベント

これらの関係を外部キー参照として設計・実装します。

ビジネスリソースの主キー

ビジネスリソースの管理は経営の重大関心ごとですから、業務上の管理番号(Identifier)があるはずです。
私は、テーブルの主キーは、システムで生成したサロゲート(代理)キーを使います。管理番号が変更されるケースとか同じ管理番号複数のレコードが存在するケースを扱うためです。

一方通行の参照(=レイヤ構造)

ビジネスイベントとビジネスリソースの関係は、Domain-Driven Design (DDD) の Responsibility Layers パターンで取り上げられている Operational Layer と Capabilities Layer と同じ関係です。

レイヤ構造ですから、上位のレイヤは下位のレイヤを参照します。下位のレイヤは、上位のレイヤの存在すら知りません。

上位のビジネスイベントはビジネスリソースを外部キー参照します。下位のビジネスリソースは、上位のビジネスイベントを知りません。外部キー参照は不可です。

集約( Aggregates )

ビジネスリソースは、さまざまな属性情報で表現されます。

顧客は、氏名、連絡方法、届先住所、購買履歴など。
取引先は、社名、部署名、担当者、連絡先(電話番号)など。
商品は、商品名、商品概要、特徴、詳細仕様など。

ビジネスリソースは、Domain-Driven Design (DDD) の基本パターンである Aggregates (集約)を適用することが多い。

一つのテーブルにすべての属性を列挙したテーブル設計を見かけます。しかし、テーブルの役割を単純にして、問題を扱いやすくするために、 Aggregates として設計し、実装すべきです。

顧客テーブルのほかに、氏名テーブル、連絡方法テーブル、届先住所テーブルがある。顧客テーブルが全体を集約するAggretate のルートテーブルという構造です。

氏名テーブル、連絡方法テーブル、届先住所テーブルは、DDD の Value Object です。単なる値のリストであり、重複した内容のレコードが存在することもあります。

氏名レコード、連絡方法レコード、届先住所レコードは、顧客テーブルの主キーを参照する外部キー制約で関連づけます。

Aggregates のモデリング方法として「○○変更」という業務の発生を判断材料にします。

連絡方法変更、届先住所変更はありそうですね。こういう変更業務の単位ごとに別テーブルにして、Aggregates パターンとして集約するテーブル設計が良い。

テーブルの数が増えると、複雑に感じる人もいます。しかし、実際にはそれぞれのテーブルに関心を持つ業務は異なります。テーブルとして関心の分離ができているので、業務ごとの設計・開発・テストが単純になり扱いやすくなります。

その業務の切り分けとテーブル分割の一つの判断材料が「○○変更」業務の有無ということです。

ビジネスリソースの登録

Aggregates パターンのテーブル構成だと、新規作成時にすべての情報を登録することは必須ではありません。

情報が発生した時点、明確になった時点で、順次、適切なテーブルにレコードを追加していけばよい。これも関心の分離の設計原則の適用です。

ビジネスリソース情報の変更

まず、内容の変更の前に「○○変更」業務の発生をビジネスイベントとして記録すべきです。誰が、いつ、なぜ、変更したかをビジネスイベント系の別テーブルに記録する。

情報の変更内容は、該当するテーブルに新規作成します。
例えば、届先住所が変更された場合、新しい届先住所のレコードを追加する。

イベント記録と新情報のレコード追加は、1トランザクションにする。

この結果、変更前と変更後の複数のレコードが存在することになります。
ビジネスリソースを参照する多くの業務は最新の状態だけに関心があります。旧レコードは、物理削除してしまうのが単純でわかりやすい方法です。 DDD の Value Object パターンのテーブル設計への応用です。

論理削除フラグを更新して、参照するときには、いつも、そのフラグを検索条件に含める、というのは、面倒なわりには、間違いが多く、メリットが少ない方法だと思います。

データの信頼性や追跡性を確保するためには、

・削除と追加を1トランザクションにする
・削除時にトリガーで、履歴テーブルに、削除内容を記録しておく

などの手法があります。

物理的に削除せず、追加のみにする方法もあります。
この場合は、どの住所レコードが現在有効であるかを示す、現住所テーブルをトリガーで維持します。導出テーブルですね。現住所テーブルは、ドロップしても、オリジナルの届先住所テーブルから、導出可能です。

変更前の情報も必要?

変更前の情報を、参照する業務ニーズをよく検討してください。ビジネスリソースは、通常の業務(オペレーション)では、現在有効な情報のみで十分です。

もし、変更前の情報も参照する業務があるなら、管理番号を新規に発行して、新しいビジネスリソースとして登録すべきです。それが、管理番号の意味だし、ビジネスリソースの管理の正しいやり方のはずです。

ビジネスリソースの利用停止

ビジネスリソースそのものを削除する(ないことにする)、リソースの管理番号を廃番にすることは、通常のビジネスではありません。多くのビジネスイベントの記録から参照されるので、安易に物理削除はできません。

しかし、今後は、この商品は販売停止、受注は不可にする、ということはよくあるケースですね。

この場合、「販売停止」ビジネスイベントをまず別テーブルに記録することが必須です。

論理的には

(全商品 MINUS 販売停止商品)

で、現在、販売可能な商品を特定できます。

実用上は、トリガーを使って、「現在販売可能な商品」テーブルを自動的に維持する方法があります。商品登録時に、トリガーで追加し、販売停止イベント時に、トリガーで削除する。

「現在販売可能な商品」テーブルは、ビジネスルールのテーブルという性格もあります。ビジネスルール系のテーブルについては、次回で説明します。

テーブル設計 データモデリングのエッセンス (3)

ビジネスイベント系テーブルの初期モデル

UMLのアクティビティ図とか業務フロー図で、業務のステップを洗い出す。
受注→出荷→請求→回収(入金)
一つ一つのステップが、ビジネスイベントそのもだから、それぞれ、テーブルにして記録すればよい。

私の場合は、ER図でビジネスイベントの発生順に左から右にイベント系のテーブルを並べる。つまり、ER図がビジネスプロセス図にもなっている。



主キーと外部キー

主キーは、受注番号などの自然キーではなく、システムで生成するサロゲートキー(代替キー)を使う。前回説明したように、受注変更の場合は、同じ受注番号で、別のレコードを作成するので、受注番号は、主キーにできない(しない)。

外部キーは、先行するイベントの主キーを参照する。

後続するイベントへの外部キーは先行イベント側に作成しないこと。ビジネスイベントの発生時点では、後続イベントの情報は存在しない。発生時点で存在しない情報は、記録できない。するべきではない。

イベントの発生順で外部キーを実装して、参照制約を宣言しておく。

ビジネスイベント系テーブルは What Who When

ビジネスイベントの記録は、What Who When が基本3点セット。

受注レコードの例:

What : 何を受注したか
Who1 : 誰から受注したか
Who2 : 誰が受注したか
Who3 : 誰が記録したか
When1: いつ受注したか
when2: いつ記録したか

3点セットといいながら、実は、6点セット。
ビジネスの記録は、行為者(誰が受注したか)と記録者(レコード作成者)は、別に管理したほうが実践的です。

When も、ビジネス上の受注日と、レコードを作成した日時は別に持つほうが良い。
ビジネスイベントが実際に発生した日時とデータベースに記録した日時は、通常は一致しません。

備考欄は別テーブル

ビジネスイベントの記録に備考欄や摘要欄を設けて、テキストを入力可能にすることも多い。
このテキスト情報は、別テーブルにして、受注レコードへの外部キーを持つべきです。イベントテーブルは、あくまでも、必ず記録できる、NOT NULL 制約のカラムだけで構成する。

イベントテーブルと備考欄テーブルに分けるのも、役割を単純化したテーブルを作る、というテーブル設計の原則の適用例です。関心の分離です。

イベントの基本情報だけで十分な業務と、備考欄にも関心がある業務は通常は別です。備考欄の内容によって発生する業務は、例外系の業務になることが多い。この例外系の業務用にシステム機能を設計・開発するときに、関心を分離したテーブルになっていることのメリットがでてきます。

Core Domain or Generic Subdomains ?

ビジネスイベントのテーブルは、Domain-Driven Design の Responsibility Layers の Operation Layer の永続化の実装パターンです。

Operation Layer (業務の層)であるビジネスイベントのモデリングやテーブル設計は、ビジネスアプリケーションの基本部分です。業務システムであれば、必ず、なんらかの実装が必要です。

この領域を「基幹システム」と呼ぶことがある。ただし、この部分は、業務システムにとって、必須ですが、中核( Core Domain ) とは限りません。そのビジネスにとって、競合との差別化のポイントや競争力の源泉こそが Core Domain としてモデリングと設計の重点になります。

私は、ビジネスイベント系のモデリングとテーブル実装は、ほとんど場合、Generic Subdomains だと思います。つまり、世の中に良いものがあれば、それを流用して活用する。最も優秀な人材は、この領域ではなく、もっと本質的な課題を担当させるべきでしょう。

既存のモノを利用する場合、パッケージソフトという選択肢があります。私は、それよりも、書籍で公開されている、汎用モデルを流用することが多い。
データモデルリソースブック
データモデルパターンズ
アナリシスパターン


パッケージソフトにせよ、公開モデルの流用にせよ、それを評価して、採用方針を決めるのは、もちろん、経験豊富で優秀な技術者が判断する。ただし、具体的な設計や実装は、そこそこの技術者にまかせる。あまり凝った設計をやりすぎないようにリードしてやる。

イレギュラーな処理のモデリング

ビジネスイベント系のモデリングをするために、現場でヒアリングをすると、さまざまなイレギュラーな処理や「特別の」やり方がいろいろでてくる。

そして、その「特別な処理」こそ、差別化の要因、競争優位の源泉と考えている人も多い。

ここの見極め、利害関係者での方針の共有は、重要な問題です。

私は、たいていの場合は「特別の処理」は、業務のムリ・ムラ・ムダであり、もっとシンプルにすることが、ビジネスの成功要因だと思います。そこを整理して、エネルギーをもっとほかの場所に向けることが成功要因になる。

ただ、競合との差別化を徹底した「特別の処理」で実現する、という道もあると思います。その方針が経営ビジョンから、日々のオペレーションレベルまで一貫して実施されるなら、すばらしい戦略になる可能性がある。

前者であれば、ビジネスイベント系のモデリングは、Generic Subdomains であり、既存のモデルやパッケージの活用を重視する。

後者であれば、Core Domain であり、優秀なメンバーを投入して「特別な処理」を効果的に進めることができる、オペレーションのモデリングを行い、システムの設計と実装を行う。

典型的なアンチパターンは、方針は、前者なのに、モデリングと設計は、後者、というパターンです。

Core Domain でない領域に、優秀な人材を投入して、時間とエネルギーを浪費する。


テーブル設計 データモデリングのエッセンス(2)

テーブルのタイプ

データベースに記録する情報の性格から、テーブルを三つのタイプに分類する。
・ビジネスイベント:受注、出荷
・ビジネスリソース:商品、顧客、店舗
・ビジネスルール:価格表、手数料、

これは、Eric Evans の Domain-Driven Design (DDD) の Responsibility Layers の考え方と同じです。

今回は、ビジネスイベント系のテーブルの設計原則をとりあげる。

ビジネスイベントの記録

受注や出荷は、ビジネスイベントが発生した時点の記録です。このタイプのテーブル(情報)は、過去の事実の記録ですから、一度作成(インサート)したら、後から、更新や削除はしない。このテーブルに許される操作は、インサートと参照のみにする。

受注取消は、受注取消テーブルに記録する。受注テーブルに論理削除フラグを作って更新したり、受注テーブルの物理削除はしない。過去の記録は書き換えない。

受注変更は、受注取消テーブルに取消を記録し、受注テーブルに受注レコードを新規に作成する。受注レコードは更新しない。レコードの削除もしない。有効な受注は( 受注 MINUS 受注取消 )で導出する。

出荷は、出荷テーブルに記録する。受注テーブルに出荷フラグや出荷日付カラムは作らない。

ビジネスイベント系のテーブルの設計原則

受注は受注、出荷は出荷、受注取消は受注取消のそれぞれの用途別にテーブルを作成する。
「テーブルの役割・用途は一つ」の設計原則です。

発生した事実の記録は発生時点での作成(インサート)だけにする。後から、更新したり、削除したりない。

取消は、取消イベントとして記録する。
変更は、取消+追加で記録する。

予定も過去の記録である

予定日など未来に関する情報も「予定した」という過去の事実として記録する。
予定変更になった場合も、予定取消を記録し、変更後の予定を新規にインサートする。

受注残の管理

( 受注 MINUS 受注取消 ) MINUS 出荷 

過去の事実の記録から、受注残を論理的に導出する。

業務量が多いと、このやり方は性能面で問題が起きる。解法の一つは、受注残テーブルを別途作成する。

受注残テーブルは、トリガーを使って、最新の状態に維持する。

受注テーブルにレコード作成時にトリガー、受注残テーブルにレコードを追加。
受注取消のレコード作成時に、トリガーで、受注残テーブルから該当する受注を物理的に削除する。
出荷レコード作成時に、トリガーで、受注残テーブルから出荷した受注を物理的に削除する。

アプリケーション開発者には、受注残テーブルは参照オンリーのテーブルになる。

入庫・出庫・在庫数

似たパターンに、入庫・出庫・在庫がある。論理的には、過去のすべての入庫データと出庫データから、現在の在庫数を導出できる。

毎回導出していたのでは、処理性能の問題があるので、トリガーで、在庫数を自動更新する。

導出テーブル

受注残や在庫数は、トリガーで自動的に更新する導出テーブルです。論理データモデルとしては、対象外。過去の事実の記録テーブルだけあれば、必要な情報は、論理的に導出可能です。

受注残や在庫数は、インデックスと同じです。つまり、性能面などを考えると必要ですが、論理的には必要ない。

インデックスは、ドロップしても再作成できます。受注残テーブルや在庫数テーブルも同じで、ドロップしても、ビジネスイベントの記録テーブルから、再作成可能です。

この設計の利点

・テーブルの役割が明確。わかりやすいデータモデルになる。
・設計・開発・テスト・保守が簡単になる。
・上書き更新や削除による、過去の記録の消失が発生しない。
・速い (小さいレコードで追加のみの処理は速いです)

テーブル設計 データモデリングのエッセンス(1)

何回もデータベースを設計してきた。多くの失敗をしながら。その中から、こうすれば良い設計ができる、というデータモデリングやデータベース設計のノウハウが少し見えてきた。その要点をまとめてみる。



テーブルの役割を一つにする


クラス設計と同じで、テーブルの役割は単純にすべきである。
関心のある情報のグループごとに別のテーブルに分ける。


例えば、商品にはさまざな属性情報がある。
・仕様(色、サイズ、機能など)
・価格や割引ルール
・仕入れルート
・原価
・在庫数
・在庫場所
・販売開始/販売停止の状態や予定日
など。


「商品」という一つの「なんでもテーブル」に情報を詰め込んで、アプリケーションプログラムのほうで、さまざまな検索条件を使い分けるのは、典型的なアンチパターン。


それぞれの情報ごとに別テーブルを用意して、ひとつのテーブルは、一つの関心だけを管理する設計が基本。


役割が一つのテーブルの特徴


この設計だと、テーブルは次のようになる。
・カラム数が減る。(せいぜい10カラム程度)
・カラム名がシンプル。
・すべて NOT NULL 制約のカラムになる。


カラム数が多いテーブルだと、それぞれのカラムを識別する名前が複雑になる。テーブルの役割が単純明快で、カラム数が少ないと、カラム名も単純になる。


テーブルがさまざまな用途の情報を持っていると、レコード作成時は NULL のカラムが多く、NOT NULL制約は設定できないケースが多い。


テーブルの目的が一つだと、レコード作成時にすべてのカラムが埋まるのが普通になるので、すべて NOT NULL 制約が設定できる。


データベースリファクタリングの初歩


リファクタリングの動機になる、いやな臭いのするテーブル。
・カラム数が多い。(20以上?)
・似たような名称がずらっと並ぶ ( prefix や suffix が同じカラム )
・ほとんどのカラムが Null 値が可能である。


こういうテーブルは、複数の目的が混在している。
単純な役割を持つテーブルに分割すると、見通しがよくなり、プログラムが単純になり、保守も簡単だし、変更の副作用の範囲も限定される。パフォーマンスチューニングも容易になる。


サブタイプのテーブル設計


テーブルの役割が複雑になる、別の例。


一つの顧客テーブルで、個人と法人の顧客が混在している。顧客区分カラムがあり、企業名カラムは、個人顧客の場合は、null。氏名欄は、企業顧客の場合は、担当者名を入れる。


この場合も、カラム数が増え、似たような名称が並び、null を許すカラムが増える。
個人顧客と法人顧客は単純に別テーブルにする。個人と法人では、ビジネスプロセスやルールが異なる点が多いので、別テーブルで情報を管理したほうが、システム全体がすっきりする。


個人と法人を一緒にして(区分を無視して)処理したいケースは、ビューを使って解決することが多い。


共通情報だけのスーパークラスの顧客テーブルと、個人顧客、法人顧客の三つのテーブルに実装する設計パターンはある。顧客の区分を意識せず共通情報だけで業務が進められるケースが多ければ、このパターンもありだと思う。
経験的には、顧客区分がほんとうに意味があれば、このスーパークラスの顧客テーブルの必要性は少ない。


ビジネス分析の結果、区分の重要度をどう判断しているかが、テーブル設計に反映される、ということ。


<今日のまとめ>


カラム数の多いテーブルは、いやな臭い。


目的が単純なテーブルをたくさん作るのが良い設計のツボです。


良い設計の結果、カラム数が減り、名前がシンプルになり、カラムはすべて NOT NULL になる。


calendar
     12
3456789
10111213141516
17181920212223
24252627282930
31      
<< March 2024 >>
システム設計日記を検索
プロフィール
リンク
システム開発日記(実装編)
有限会社 システム設計
twitter @masuda220
selected entries
recent comment
recent trackback
categories
archives
others
mobile
qrcode
powered
無料ブログ作成サービス JUGEM