<< 試合開始 | main | テーブル設計 データモデリングのエッセンス(2) >>

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

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



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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


<今日のまとめ>


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


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


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


コメント
昔の記事にコメントすいません。

現在、テーブルの設計に悩んでいます。
すみませんが、テーブルの設計について
教えていただけませんでしょうか。

例えば、「商品発送テーブル」があったとします。
(発送する商品の明細は別テーブルだとします)

カラムとしては、

伝票番号
発送先コード
発送元コード
発送方法コード
発送手段コード
取引区分
受注番号
受注日
出荷日
納品予定日


(その他のカラム合わせて、合計25カラム)

であった場合で、更に、

・役割がひとつのテーブルである
・似たようなカラム名が無い
・全てのカラムが Null 値が不可能である

であった場合でも、テーブルを分割する方が良い設計と言えるのでしょうか?

お手数おかけしますが、よろしくお願いいたします。
  • ウサ
  • 2010/08/17 1:43 PM
ウサさん

コメント、ありがとうございます。
昔の記事を読んでいただけ、しかもコメントまでしてもらえるのは、とてもうれしいです。

ご質問の件ですが、設計は、案件ごと、現場ごと、いろいろな前提や制約があると思うので、あくまでも、私なら、こう考えるという、ことで。

このテーブルは、

・発送の指示(の記録)
・発送した記録

のどちらの役割でしょうか?

もし、両方の意味があるなら、「指示(予定)」と「実績」は、別テーブルにするのが、基本だと思います。

また、例であげられたカラムでは、

・受注日
・出荷日
・納品予定日

が、ひとつのテーブルにあるのは、ちょっとひっかかります。

受注日は(受注番号から参照できる)、受注テーブルにあるべきでは?

出荷日=実績、納品予定日=予定ということであれば、これは、別テーブルなんではと思います。

また、

・発送方法
・発送手段
・取引区分

あたりの組み合わせに、なにかビジネスルールが隠れているにおいがします。

可能な組み合わせのみを定義したテーブルが別にあって、それを外部参照制約にするほうが、ビジネスルールが明確になる、とかいう可能性はありませんか?

-----

テーブル分割は、手段です。

目的は、

・変更がやりやすい
・変更が安全(副作用が少ない)

ことです。

私自身は、

・情報発生のタイミングが違えば別テーブル
(新規発生のタイミング、変更のタイミング)

・利用者が別、あるいは、利用目的が別の情報は、別テーブルの可能性がある

ということを、意識するようにしています。

そうするようになってから、業務の現場からでてくるさまざまな仕様変更の依頼にも、対処しやすくなったと、感じています。

---

少しでも、ウサさんの参考になれば、幸いです。



  • masuda220
  • 2010/08/17 4:52 PM
早速のお返事、ありがとうございます。
「テーブル設計 データモデリングのエッセンス」シリーズ、
全て拝見させて頂きました。

とてもためになる記事で、非常に感謝しております。
同僚にも紹介させて頂きました。

> もし、両方の意味があるなら、「指示(予定)」と「実績」は、別テーブルにするのが、基本だと思います。

仰る通り、両方の意味を兼ねていました。

> 出荷日=実績、納品予定日=予定ということであれば、これは、別テーブルなんではと思います。

ありがとうございます。
ご意見を参考に、別テーブルで設計してみようと思います。

> ・発送方法
> ・発送手段
> ・取引区分
>
> あたりの組み合わせに、なにかビジネスルールが隠れているにおいがします。
>
> 可能な組み合わせのみを定義したテーブルが別にあって、それを外部参照制約にするほうが、ビジネスルールが明確になる、とかいう可能性はありませんか?

これも、仰る通りです。

「可能な組み合わせ」が存在します。
別テーブルに「可能な組み合わせ」を持たせ、外部参照にした方が、
分かりやすくなると思いますので、参考にさせていただきます。

> そうするようになってから、業務の現場からでてくるさまざまな仕様変更の依頼にも、対処しやすくなったと、感じています。

現在関わっている案件は仕様変更が多く、対処が困難に成ることが予想されています。
masuda220さんのお考えを参考に、設計を進めたいと思います。

ありがとうございました。

...度々申し訳ありません。
もう一つだけ、お伺いさせて頂けませんか。

業務の要件として、

・取引先に対して、日単位で売上予算の計上を行う
・売上予算の入力画面で予算を入力する
・入力したデータを帳票で確認する
・確認後、対象のデータを「修正」もしくは「確定」する
・「確定」するまでは、何度も「修正」可能

と言うものがあったとします。
データの「修正」と「確定」では、データの発生タイミングが異なるので、

その場合、masuda220さんのお考えですと
テーブルとしては、

--- 以下、例1 ---
【売上予算】
・入力番号(PK)
・予算対象日
・取引先
・予算金額
・入力日
・確定日
・確定フラグ

と、するのでは無く、

--- 以下、例2 ---
【売上予算(入力時や修正時にインサート)】
・入力番号(PK)
・予算対象日
・取引先
・予算金額
・入力日

【売上予算確定(確定時にインサート)】
・入力番号(PK)
・予算対象日
・取引先
・予算金額
・確定日

とするべきなのだと、理解させて頂きました。
(違いますでしょうか?)

しかし、こう言ったやり方はどのようにお考えになりますか?

--- 以下、例3 ---
【売上予算(入力時や修正時にインサート)】
・入力番号(PK)
・予算対象日
・取引先
・予算金額
・入力日

【売上予算確定(確定時インサート)】
・入力番号(PK)
・確定日

---

何故かと申しますと、

例2ですと、似たような構成のテーブルが複数あって冗長な気がするのと、
例3の方が、テーブルの役割が明確で、分かりやすいのかなと思うのです。

例1の「確定フラグ」と「確定日」を別テーブルにしただけのような気がしますが、
データの発生するタイミングも異なりますし、また、
「データ確定後、更に上司の承認を必要とする」と言った仕様変更が
あった場合に、

【売上予算承認管理(承認時インサート)】
・入力番号(PK)
・承認日

と、テーブルを追加すれば、変更に対応しやすいし、分かりやすいのかな、
と思うからです。

どのようにお考えになりますでしょうか。

度々申し訳ございません。
  • ウサ
  • 2010/08/18 11:15 AM
ウサさん

この日記が、少しはお役にたてたようで、うれしいです。

今度の、ご質問の件ですが、「予算」は、予算の作成プロセスが、流動的なところがあって、いろいろ悩ましい問題が多いですよね。(計画立案系の業務は、みんなそうですね)

ウサさんの3つの例の中では、例3が、すっきりして良い感じがします。ウサさんのおっしゃるように、「承認」とかの機能追加にも強そうです。

もし、私が設計するとしたら「売上予算」の金額を変更する場合は、

A.売上予算を update する時、トリガーで、変更イベントを別テーブルに記録
または
B.金額変更イベントテーブルを insert し、トリガーで、売上予算の金額を update
または
C.金額変更イベントテーブルへの insert と、売上予算の 金額 update をカプセル化したストアードプロシージャ

のどれかを、追加で実装すると思います。

予算の編成過程での、すべての金額変更のイベントを追跡するのは、ちょっとやりすぎかもしれません。
ただ、金額がらみ、かつ、承認行為が存在する業務では、万が一のための、こういう追跡用のイベントログを記録しておくのが、良い設計だと思っています。
  • masuda220
  • 2010/08/18 3:20 PM
お返事ありがとうございます。
非常に参考になりました。

> ただ、金額がらみ、かつ、承認行為が存在する業務では、万が一のための、
> こういう追跡用のイベントログを記録しておくのが、良い設計だと思っています。

丁度、「マスタ系のテーブルも変更履歴を記録する事」
なんて話もあり(内部統制云々)masuda220さんの
お考えを参考にさせて頂きます。

度々すみませんでした。
今後もちょくちょく拝見させて頂きます。
  • ウサ
  • 2010/08/19 6:06 PM
ウサさん

こちらこそ、ありがとうございました。
読んでいただけただけでも、うれしいです。
その上、いろんなアイデアやサンプルを、ご提示いただき、勉強になりました。

今後も、よろしくお願いします。
  • 2010/08/20 6:29 PM
コメントする









この記事のトラックバックURL
トラックバック
calendar
 123456
78910111213
14151617181920
21222324252627
28293031   
<< May 2017 >>
システム設計日記を検索
プロフィール
リンク
システム開発日記(実装編)
有限会社 システム設計
twitter @masuda220
selected entries
recent comment
recent trackback
categories
archives
others
mobile
qrcode
powered
無料ブログ作成サービス JUGEM