古き良きデータベースデザイン

2020/09/17 06:28

Elnur
CMDBサービス「Relinx」の創業者
 
この記事は、著者の許可を得て配信しています。
https://relinx.io/2020/09/14/old-good-database-design/

なぜ気にする必要があるのか?

アプリケーションはもちろん、データベースに至るまで、さまざまなものがありますが、データは最も重要な部分です。通常、データにはそもそもシステムが存在するという目的があります。だからこそ、データベースシステムはデータを保持するためのブラックボックスとしてだけでなく、データが破損しないようにバリデーションしたり、ガードしたりするためのツールとして考えるべきなのです。

これは、しっかりしていてよく考えられたデータベース設計があるからこと実現できているのです。もちろん、ビジネスロジックはアプリケーション層でコード化されており、データベースに到着する前にデータが適切な形式であることを確認します。しかし、ネットワーク障害やバグによって破損した「ゲスト」を許可しないことを誰が保証するのでしょうか?また、アプリケーション層はデータベースへの唯一の「扉」ではありません。インポートスクリプト、メンテナンススクリプト、そして単なる人間のDBAやそれらと対話する開発者などが存在します。非常に低いレベルでの予防措置をとることで、データが保存される前に常にチェックされることが保証されます。

しっかりとしていて、信頼性の高いデータを持つことは、開発やテストにも役立ちます。カラムをNot Nullに設定することで、そのカラムが空であることを前提とした多くのテストシナリオを除外することができます。またコードを簡素化することで、開発者がアクセスする前に値を (ほぼ) 毎回チェックできるようになります。

今まで良いデータベース設計の重要性を強調してきましたが、今からはそれを実現するためのツールを具体的に見てみましょう。

ノーマライゼーション(正規化)


イメージ提供者: https://www.w3resource.com

これは間違いなく優れたデザインの第一のルールです。私たちはここでノーマライゼーション(正規化)のルールを掘り下げようとはしていませんが、その重要性を強調したいとは思っています。このトピックについてはこちらの記事をぜひご一読ください。とてもよい内容です。

データタイプ

属性に適切なタイプを設定することにも注意が必要です。これはデータベースのパフォーマンスを向上させるだけでなく、データを格納する前にデータをバリデーションすることにもなります。そのため、数値データは「integer」「numeric」のフィールドに入れておくべきです。タイムスタンプは 「timestamp」「timestamptz」フィールドに保存しておきましょう。「char(1)」「boolean」「bit」フィールドなど(いずれかRDBMSに対応している方)の中にBooleansを保存します。

日付は特定のメンションに値します。Date属性が日付の部分だけを持つことになっている場合(OrderDate, ReleaseDate)は、時間の部分を持たない型("date")を使用します。時間だけを保持する必要がある場合(StartTime, EndTime)は、適切なtimeタイプを使用してください。精度を必要としない場合は、それをゼロとして指定します("time(0)")。時間部分を持つ日付の問題点は、日付だけを表示するために常に時間部分を切り取らなければならないことと、データベースとは異なるタイムゾーンでフォーマットする際に、昨日や明日などという言葉が表示されないように気をつけなければならないことです。また、時間部分を持つ日付は、日照時間を変更する日付の上を移動する際に、減算や足し算をしなければいけないという問題を引き起こす可能性があります。

制約

制約が本日の主な話題です。制約は、無効なデータを排除し、データの堅牢性を確保するものです。一つずつ見ていきましょう。

NOT NULL 制約

ビジネスルールで属性は常に存在しなければならないとされている場合は、迷わずNot Nullにしましょう。Not Nullにするには、Id、Name、AddedDate、IsActive、State、CategoryId(すべてのアイテムにカテゴリがある場合)、ItemCount、Price、およびその他多くのフィールドが適切な候補となります。通常、これらの属性はビジネスロジックにおいて重要な役割を果たします。他のオプションの情報フィールドは、まだNullのままかもしれません。

しかし、Nullになる可能性のある属性に対しては、Not Null制約を使いすぎないように注意してください。例えば、長時間実行しているタスクは常にStartTimestampを持っていますが(Not Null)、EndTimestampはタスクが終了したときにのみ更新されます(Null)。別の昔ながらの例もあります。Employee テーブルには ManagerId がありますが、すべての従業員にマネージャーがいるわけではありません。ManagerIdをNullにせず、マネージャーがいない従業員のために "0 "または"-1 "を挿入したいという誘惑に駆られないようにしてください。そうしてしまうと外部キー制約を追加する際に別の問題を引き起こすことになります。

UNIQUE 制約

繰り返しになりますが、ビジネス・ルールによっては、Id、PinNumber、BookIdとAuthorId、OrderNoなど、いくつかの属性(または属性の組み合わせ)はユニークでなければなりません。Unique制約を追加することでこれらの属性をユニークにする必要があります。もう一つ注意点があります。それは、同じ効果を得るためにUnique Indexを使用することもできますが、制約を追加する方が良いアプローチであるという点です。なぜなら、Unique制約を追加すると、ユニークではないインデックスが自動的に作成されるからです。そのため、何らかの理由で一時的に制約を無効にしたり有効にしたりしなければならない場合でも、非常に簡単にできます。ユニークインデックスの場合は、インデックスを削除/作成しなければなりませんが、これはパフォーマンスの低下と時間の点でコストのかかる作業です。

PRIMARY KEY

Nullではない制約とUnique制約が一緒になっPrimary Keyを構成します。Primary Keyといえば、IdやObjectIdなどの列がすぐに思い浮かびます。しかし、Primary Keyは、BookIdやAuthorIdのように複合的になることもあります。ここでのジレンマは、別個の Id カラムをPrimary Keyとして持つか、それとも両方のカラムを合成してPrimary Keyを作成するかということです。別々のIdカラムを持つことは、結合をすっきりさせ、ユニークな組み合わせに別のカラムを簡単に追加することができるので、通常はよりよい方法だと思います。別個のPrimary Key(Id)を持つことで、BookId と AuthorId カラムにユニーク制約を追加する必要がなくなります。

CHECK制約

Check制約により、データの有効な値/範囲を設定することができます。Check制約に最適な候補は、パーセント(0から100の間)、状態(0、1、2)、価格、金額、合計(0以上)、ピン番号(固定長)などです。繰り返しになりますが、チェック制約にビジネスロジックをコード化しようとしないでください。以前、AccountBalance列にCheck制約「ゼロ以上」を追加することで、誤って残高超過を防いだケースがありました。

DEFAULT制約

Default制約も重要です。これにより、既存のテーブルに新しいNot Nullカラムを追加し、すべてのパーティがアップグレードされるまでの間、「古い」APIを新しい構造と互換性のあるものにすることができます(ただし、完全なアップグレード後にはDefault制約は削除されるべきです)。

ここで一つ覚えておいてほしいことがあります。Default 制約にビジネスロジックをコード化してはいけないということです。例えば、関数 「now()」 は、ログテーブルの Timestamp フィールドのデフォルト値としては適していますが (常にというわけではありませんが)、 Orders テーブルの OrderDate フィールドには適していません。Default 制約を頼りにして、挿入時にOrderDateを省略したくなるかもしれませんが、これはビジネスロジックをデータベースレベルにまで広げることを意味します。また、ある時点でビジネスは、承認された後にのみOrderDateを割り当てることを決定するかもしれません。Default 制約はデータベースの奥深くに埋もれているため、アプリケーション層のコードに変更を加えてもあまり変化が見られません。

外部キー制約

外部キー制約はリレーショナルデータベース設計における王様のようなものです。Primary Keyとともに、外部キーはテーブル間レベルでのデータの一貫性を保証します。ノーマライゼーションルールは、データをそのテーブルに抽出し、外部キーで参照するタイミングを教えてくれます。ここでは、OnDeleteやOnUpdateルールのような微妙なニュアンスに焦点を当てます。


DBeaver の外部キーエディタダイアログ

まずは簡単なところから始めましょう。OnUpdateです。外部キーはPrimary keyを参照しています。Primary keyが変更されることはほとんどありません。そのため、OnUpdateルールはあまり一般的ではありませんが、Cascadeに設定するのは理にかなっています。というのも、いくつかの行のPrimary keyを更新しなければならないことがあるからです (通常は移行後に行います)。この方法では、データベースは更新を許可し、新しいIDは子テーブルに継承されます。

OnDeleteルールはもう少し複雑です。データベースに応じて、NoAction、Restrict、SetNull、SetDefault、Cascadeのオプションがあります。では、どれを選べばいいのでしょうか?

NoActionは通常、ルックアップを参照しているキーや、エンティティを参照しなくても存在できるエンティティのために選択されます。例えば、Product -> Categories、Books -> Authorsなどです。ほとんどの場合、RestrictはNoActionと同じですが、データベースによっては微妙な違いがあります。

一方、Cascadeは、親がいないと子レコードが存在できない場合に選択されます。BookとAuthorの例では、bookが削除されたときに、BookAuthorテーブルからもレコードを削除する必要があります。他の例はOrderDetails -> Orders、PostComments -> Postsなどです。ここでは、データベースが自動的に子行を削除すべきではないことに反対する人もいるかもしれませんが、彼らはアプリケーション層によって削除されるべきです。ビジネスロジックに応じてです。そうだんです。しかし、時には 「重要でない」子の削除は、データベースに委任することができます。

SetNull が使用されることはほとんどありません。例えば、Employee.ManagerIdとEmployee.Idの間の外部キーは、Set Nullになることもあります。マネージャーが解任されると (一度でも) 彼の部下は孤児になります。明らかに、このルールは、列がNULL可能な場合にのみ選択するべきなのです。

SetDefaultは、このルールの中で最も珍しいルールです。これは、親レコードが削除されたときにカラムをデフォルト値に設定します。外部キーはPrimary keyを参照するので、外部キーを持つフィールドのデフォルト値をハードコードすることはほとんど考えられません。しかし、いずれにしてもこのオプションは必要に応じて存在します。

インデックス

インデックスは良いデータベース設計の重要な部分ですが、今回の議論では少しテーマから外れています。注意しなければならないのは、いくつかのRDBMSシステム(例:Oracle)は、外部キーの作成時に自動的にインデックスを作成するので、そのことを心配する必要はありません。他のシステム(例:MS SQL Server)はそうではないので、自分でインデックスを追加しなければなりません。

まとめ

よく考えられた設計は、コーディング、テスト、トラブルシューティングにかかる時間をいくらでも節約することができます。よく設計されたデータベースに対してクエリやレポートを書くのは楽しいものです。また、そのデータを公開したり、新しいシステムにマイグレーションしたりするのも非常に簡単です。

ただ、この記事で紹介する意見は、様々なデータベースシステムを長年扱ってきた経験を反映したものであり、あなたのケースに適しているものもあれば、そうでない場合もあるかもしれません。ですからすべてが正しいと鵜呑みにしないようにしてください。

ぜひ、みなさんコーディングを楽しんでくださいね!

appstore
googleplay
会員登録
URLからPICKする

会員登録して、もっと便利に利用しよう

  • 1.

    記事をストックできる
    気になる記事をPickして、いつでも読み返すことができます。
  • 2.

    新着ニュースをカスタマイズできます
    好きなニュースフィードをフォローすると、新着ニュースが受け取れます。