業務システムの開発において、データベース設計はシステム全体の優劣やコスト、さらにはシステムの寿命をも左右する重要な作業です。このコラムでは、データベース設計で重視すべきことを解説し、設計の手順と作成すべきドキュメントについて説明します。
【データベース設計で重要なこと】
業務システムにおいて、データベース設計で重視すべきポイントは次の3点です。
- 1.将来性に影響：自然な構造で適切に正規化されており、現実のデータ構造と乖離がないこと
- 2.生産性に影響：理解しやすい構造であり、ドキュメントが整備されていること
- 3.安定稼働に影響：性能確保や排他制御、大きすぎないサイズ等、システムの実行面で無理がないこと
以下、１つずつ解説します。
1.将来性に影響：自然な構造で適切に正規化されており、現実のデータ構造と乖離がないこと
業務システムは運用を開始した後も業務の見直しなどにより変更の要求が発生することが普通です。変更の要件がデータベースのデータ構造に及んだ時、元の構造が適切に正規化されていて、かつ現実のデータ構造と乖離がないようにすると、無駄な変更を抑えることができます。以下に簡単な例を挙げて説明します。
★実業務の概念とデータベース設計に乖離がある悪い例
※商品と部品が１つのテーブル「在庫」で管理されています。
システムが小さいうちは問題になりにくいのですが、次のようなときに設計の見直しが必要になってきます。
・商品の場合に必要な項目を追加（例：売価）
・部品の場合に関係を持つ別テーブルの追加（例：調達先）
★正規化ができていない悪い例
※この例ではオプションのIDと料金が2組になっています。
将来オプションの管理項目（例えば、オプション半額キャンペーン適用）が増えたり、オプションの設定可能数が3点以上に増えたりすると、システムの変更規模が大きくなります。
2.生産性に影響：理解しやすい構造であり、ドキュメントが整備されていること
データベースにアクセスするプログラムを設計・実装するには、そのシステムのデータベース構造を理解する必要があります。勘違いがあると致命的なバグを作りこむ可能性が高くなるからです。データベースが自然で理解しやすい構造で、またドキュメントがしっかり整備されていると、プログラム開発時の生産性を確保しやすくなります。
データベースの設計書は他の設計書より重要です。例えばプログラムの仕様は、ある程度の業務理解があれば、開発環境で動作させて概要を把握しコードを読んで詳細を把握することができます。しかしデータの状態がシステム全体にどのように影響するかは、ビジネス要件やテーブルのDDL（テーブル作成のときに使う定義文）、プログラムコードから読み取ることは難しいのです。概ね分かっても、気づいていないルールがあるかもしれないという不安が残るのです。
3.安定稼働に影響：性能確保や排他制御、大きすぎないサイズ等、システムの実行面で無理がないこと
データベースは、どんなに理論的に正しくても無理な箇所がないように設計しなくてはいけません。例えば次のような事象を避ける必要があります。
- （たとえ現実にフィットした構造でも）関係が複雑すぎてデータ取得のクエリー（SQL）が複雑になってしまう
- テーブルのデータ件数が多すぎてクエリーの実行速度が確保できない
- （テーブルの項目数が多い場合に）検索対象が多すぎてインデックスのサイズが大きくなりすぎる
【データベース設計の進め方】
1.データベースの知識を得る
大前提としてデータベースの仕組みをよく理解する必要があります。一般ユーザー向けに作られているツールとは違い、データベースシステムは簡単を目標にしたものではありません。使う側のエンジニアがデータベースシステムをよく理解したうえで適切に利用することが前提となっています。
このコラムで扱っているデータベースとはリレーショナルデータベース（関係モデルのデータベース）を指しているのですが、そもそもリレーショナルデータベースは「性能が出にくい」かつ「癖が強いもの」なのです。
データベースの設計の良しあしはシステム全体の良しあしに波及するため、業務システムの構築にあたっては知識が不足した状態でデータベースの設計に手を出すべきではありません。もし十分な知識や経験がないのにデータベース設計を行うのなら、必ずデータベースをよく理解し、経験を積んだ上級エンジニアの指導やレビューを受けてください。
2.業務とシステム仕様を理解する
「データベース設計で重要なこと」でデータベースは自然な構造になっていることが大事だと述べました。自然な構造に設計するには、業務とシステム仕様を十分理解していることが必要です。例えばシステム仕様は理解したが、その背景の業務が分からない状態でデータベース設計を行うと、初期実装は無事に完了するかもしれませんが、データ構造が現実と乖離する可能性が高く、少々の変更で変更コストが跳ね上がるシステムができるかもしれません。よいシステムを作るために大事なのは「動けばいい」と考えないことです。「あるべき姿」を常に模索する必要があるのです。
3.テーブルを見いだす
データの管理単位であるテーブルを見いだす作業を行います。テーブルは商品、売上、売上明細、等のデータの管理単位に合わせます。データの用途が説明しやすい１つの単位であり、データの単位が明確であることが条件になります。データの単位とは、「売上」の発生毎に1件、売れた商品毎に１件などのデータの発生・管理単位のことです。
4.テーブルの主キーを設定し、テーブル間の関係を明らかにする
この作業がDB設計のヤマ場です。次の4-1～4-5を行います。
4-1.見いだしたテーブルに主キーを設定する
主キーとはテーブル内の１データを一意に決定する項目のことです。
主キーは業務で使うデータ項目をそのまま指定するナチュラルキーと呼ばれる手法と
主キーのために項目を追加するサロゲートキーと呼ばれる手法があります。
ナチュラルキーは業務データそのものであるため分かりやすい反面、いくつかのデメリットがあるので採用するときは気を付けて下さい。
・主キー項目の更新がしにくい（例：メールアドレスは会員をユニークに識別できますが、変更の可能性があります。主キーは他のテーブルからのそのデータを参照するためによく使われます。つまり値を変更する用途に向いていません。）
・主キーが複数の項目からなる複合キーになりがちでクエリーがその分長くなる
・ユニークに見えても実は違う可能性がある（例：書籍のISBNコードは使い回されていることが知られています）
・将来の運用が不明な場合がある（例：部門コードは部門に対してユニークに設定されるでしょうが、未来永劫までユニークに運用されるとは考えない方が無難です）
明確にナチュラルキーが良いと確信できるとき以外はサロゲートキーを使うことをお勧めします。
4-2.テーブル間の関係を明らかにする
テーブル間の関係を明らかにします。1:1、1:Nの関係が基本となります。N:Mの関係は中間テーブルを使い、1:Nの関係に整理します。
テーブルの関係はER図に整理・記述します。ER図はテーブル内の項目を記述することが一般的なのですが、全体が表現しにくい場合は、項目の記述を省略してテーブル名のみを書く方法で良いと思います。項目の表記より全体を見渡せることの方がデータベース構造の理解のために重要だからです。
4-3.正規化の観点でテーブル構造を見直す
テーブルの関係性を設計する際、データの重複をなくし矛盾が発生しにくい整理された構造とするために、正規化の概念が役に立ちます。正規化では、繰り返し項目を別テーブルにし、従属する内容（たとえば、売上明細における商品名）を別テーブルに切り出す等の作業を行います。
正規化は杓子定規に行うのではなく、現実を踏まえて判断します。たとえば、自社の採用活動をシステム化するなら、転職歴が少ない人のみを採用する会社であっても応募者データと職歴データは別テーブルにすべきだと思いますが、連絡先電話番号を最大2項目登録可能とする場合は、将来3項目以上に増やす可能性は低いので、繰り返しの項目ではあるのですが別テーブルには切り出さずに、応募者テーブルに2項目用意する方が単純で望ましいでしょう。
4-4.物理的に無理がないか検討し、現実的な構造に調整する
データ件数が多すぎたり、項目数が多すぎたりする箇所を調整します。また、テーブルに格納するデータに偏りがある場合もテーブルの分割を検討します。
いくつか例を挙げます。
・スマホを使った会員システムで、様々な条件で会員を選んで記事を配信する場合、どの会員にどの記事を配信するかをテーブルに格納することになりますが、このテーブルは（記事×配信対象）の件数になります。例えば、このテーブルを会員IDと記事IDの2項目にすることで（つまり他の項目を配置しないことで）テーブルのサイズを小さくできます。また、記事のタイプ毎にテーブルを分ける方法で、１テーブルの件数を抑える設計も考えられます。
・例えば多数の個人情報の項目を持つ会員テーブルを考えるとき、多くの会員は個人情報を記録せず、一部の会員だけが個人情報を記録するなら、個人情報を個人情報テーブルに切り出すと会員テーブルが扱いやすくなります。
4-5.参照整合性制約を設計する
テーブル間で参照整合性制約を設定するかを決定します。参照整合性制約とは、参照されているデータは存在が必須であり、また削除できないようにする制約です。たとえば、商品カテゴリAを参照している商品データBBBがあるとき、Aは存在している必要があり、参照されている限り削除できません。
5.テーブルの項目を整理し、検索に使う項目にインデックスを設定する
テーブルに全ての項目を配置し、項目に適切なデータ型、ユニーク制約、NotNull属性を決定します。さらに外部キー（主キーを参照する項目）と検索で使う項目にインデックスを設定します。
尚、ユニーク制約は本当にユニークになるのかを確認してください。例えば会員テーブルで退会者のデータを消さずに残す場合、同じメールアドレスで新規登録を受け付ける必要があるかもしれません。
項目値が入らないケースが多い項目にインデックスを設定するときは、NULLを許可することでインデックスのサイズを抑えることができます。（NULLはインデックスに記録されないからです。この仕様はDBによって異なります。お使いのDBの仕様を確認してください）
6.排他制御を設計する
デッドロックが発生しないようにデータ更新時の具体的な排他制御の方法を設計します。どのようなときにトランザクションを使い、どのレコードをどのようにロックするか、どのようなときに楽観的な排他制御を行うのかを設計します。
【データベース設計のドキュメント】
データベース設計は開発者全員が理解することが非常に重要です。そのためデータベース設計書のドキュメントは開発に参加する全員（少なくともデータベースにアクセスするコードを書く全員）が読み込むので、コストをかけてでも良好なものにする価値があります。
1.ER図
テーブル間の関連を書いた図です。私は下から上に参照するように（1:Nなら1が上、Nが下）書きます。書き方にはいくつか流儀があるのですが、大事なのは全体が見渡せることと、構造が理解しやすいことの2点です。
例を示します。
※悪い例（参照の方向がばらばら）
見ただけでは構造が把握しにくいのではないでしょうか。直したのが次の例です。
※良好な例（参照の方向が同じ）
どうでしょうか、関係が理解しやすいと思います。ある程度テーブル数が増えて、関係も増えてくるとテーブルとテーブルを結ぶ線が引きにくく、配置も難しくなります。なかなか骨の折れる作業なのですが、ハコの位置を調整したり書き方を工夫して理解しやすくすべきです。かけた以上のメリットが得られると思います。プロジェクトに参加するエンジニアが多いならなおさらです。
また、一般にER図の要件ではないのですが、テーブルの分類にあわせて色分けしたり、注意が必要な箇所にコメントを書くと、より理解しやすいER図になります。
2.テーブル設計書
テーブルと項目の設計書です。テーブル設計書は1テーブル毎に書くのではなく、全テーブルを1つのExcelシートに記述すると全体を見渡しやすくて便利です。また、コード設計は別資料に書くことが一般的なのですが、テーブル設計書に記述したほうが実装時の効率が良くなります。
3.排他制御ルール
複数のテーブルをまたがって整合性を保つ必要があるときはトランザクションとレコードロック等を使った排他制御を行うのですが、この排他制御はシステム全体で統一がとれていないとデッドロックが発生します。そのため、プログラマが守るべき排他制御のルールのドキュメントを作成します。※ロックについては別のコラムで解説します。
4.CRUD表
CRUD表はテーブル毎のレコードのCreate,Read,Update,Deleteがいつ行われるかを記述したマトリクス表です。これはデータの作成や更新のタイミングが分かりにくいときに作成します。CRUD表は単に作るだけの資料になりがちなので、作成前に本当に必要かどうかを考えると良いでしょう。
上記以外にも、事象に応じて保存データの状態が変わる場合や特徴的な要素があるときは、補足の説明資料を作成します。
【まとめ】
業務システムはデータベース設計の良しあしで決まると言っても過言ではありません。また、設計を理解しやすくするためのドキュメント類も重要です。業務システムの開発ではいろいろなドキュメントを作成しますが、最も重要なドキュメントは何かと聞かれれば、私は真っ先にER図とテーブル設計書を挙げます。この2つは完璧さが常に要求されます。