はじめに
こんにちは。TIG DXチームの村瀬です。
春の入門祭り #05 データベース テーブル設計入門です。
テーブル設計を初めて任された際にどうやって設計をすれば良いか困った経験はありませんか？
誰かが設計済みのテーブルを見れば理解できるのだけれども、そもそもテーブル設計ってどうすれば良いの？って方に向けた記事です。
とりあえず定義したテーブルを正規化していく形であるべき姿を学んで行きます。
前提条件
DBはRDBMS(リレーショナルデータベース)を対象とします。
論理設計を対象とします。
処理速度よりも整合性を優先するものとします。
良いテーブル設計とは？
処理速度が速いに越したことはないのですが、それよりも保持するデータの冗長性を排除し、整合性を保持する設計が良しとされます。
これを行う方法が正規化として定義されており、一般的には第３正規形まで正規化することで取り扱いやすい良いテーブル設計となります。
正規形定義
| 名称 | 定義 |
|---|---|
| 非正規形 | リレーションRの属性の中に、単一でない値が含まれている。 |
| 第１正規形 | リレーションRの属性が、単一値である。 |
| 第２正規形 | リレーションRが次の二つの条件を満たす。 1.第１正規形であること 2.すべての非キー属性は、いかなる候補キーにも部分関数従属していない(完全関数従属である)こと |
| 第３正規形 | リレーションRが次の二つの条件を満たす。 1.第２正規形であること 2.すべての非キー属性は、いかなる候補キーにも推移的関数従属していない |
ですがこの定義の説明、専門用語と独特の言い回しが多く初見だと難しく感じたので順を追ってわかりやすく非正規形から第３正規形にしてみようと思います。
STEP0 基本となるテーブル
説明の為、サンプルとなるテーブルを用意します。社員番号、社員名、部署コード、部署、趣味を持つものとします。社内向け社員検索システムの設計の一部だとでも思っていただければよいです。
仮のレコードも付け加えると以下のようになります。以後、主キーは下線にて示します。
この社員テーブルは非正規形の状態です。
社員
※1 本来であれば社員名は姓、名で分けたり、よみがなの項目を分けて持つべきですが、今回の説明の主旨から外れるので簡易的に社員名として表現しています。
※2 会社によっては社員が複数の部署に所属するような場合もあるかと思いますが、今回は社員は一つの部署にのみ所属することとします。
STEP1 非正規形から第１正規形へ
非正規形の定義として「単一でない値が含まれている」というのがあり、それは上表で言うと趣味にあたります。
一人が複数の趣味を持つことがあり、検索や更新をする際に適切な形式ではありません。
まずは単一でない値を排除することで第１正規形にしてみましょう。
社員
趣味
STEP2 第１正規形から第２正規形へ
用意したサンプルでは実はもう社員テーブルは第２正規形になっています。
正規形の定義を確認すると第２正規形の定義の一部として「1.第１正規形であること」とあるので第３正規形までであれば高次の正規形は低次の正規形を満たすことになります。
「2.すべての非キー属性は、いかなる候補キーにも部分関数従属していない(完全関数従属である)こと」この定義がいまいちわかりにくいので補足すると複数のキーで主キーが構成される場合に主キーの一部が他のキーの主キーとなるのを解消することです。
※正確に言うと主キーではなく候補キーですが説明を簡素化する為、主キーと記載しています。
別のテーブルで例を示すと以下のようになります。
伝票
このテーブルでは主キーの一部である商品コードが商品名に対する主キーになる為、第１正規形ですが、それを以下のように解消することで第２正規形(第３正規形)にできます。
伝票
商品
STEP3 第２正規形から第３正規形へ
社員テーブルはまだ第２正規形ですので第３正規形にしましょう。
「2.すべての非キー属性は、いかなる候補キーにも推移的関数従属していない」ことが第３正規形の定義です。
これも言い回しが独特でとっつきにくいのですが、マスタテーブルを作成すると捉えればOKです。そのマスタテーブルって何って話ですが、何らかのIDに対して一意の名称を持ち利用されうる値全てを保持するテーブルです。例えば都道府県だったり、社員だったり取引先だったり。反対に伝票番号のように業務に伴って発生した出来事を記録するテーブルはマスタテーブルではありません。
社員(再掲)
部署の主キーとなるのが部署コードなので新たに部署テーブルを作成し、社員テーブルからは部署テーブルの主キーのみを示す形します。
社員
部署
その他
正規化の他にも気を付けるべき点があるので特に気を付けるべきと思われる点を簡単に３点だけ紹介します。
多対多の関係の解消
テーブル設計を進めていくとテーブル間の関係が多対多になってしまうことがあります。
先ほどの例ではあえて留めましたが、趣味テーブルがマスタテーブル化されているような場合に起きえます。
社員
趣味マスタ
多対多の関係の場合にどうテーブル設計をすれば良いかわからなくなってしまいがちです。
そうなんです。多対多の関係の場合、どう頑張っても良い設計にならないのです。
ですのでそもそも多対多の関係にならないような設計が必要で、その解消方法は中間テーブルを用意し、１対多の関係になるように設計することが必要です。
社員趣味紐付け(中間テーブル)※
※代理キー(サロゲートキー)を主キーとする設計も良くあります。
論理削除の可否
論理削除はアンチパターンの一つなのですが、割と良くある設計です。
レコードを消したい。でも消したくないみたいな時に削除フラグ項目を設け、レコードをDELETEするのではなく削除フラグをUPDATEして、SELECTの条件で削除フラグがTRUEなら取得しないようにするやつです。
頻繁に復活させたり、レコード数が少ないテーブルに設けるのであれば検討の余地はありますが、基本的には論理削除を用いないほうが良いでしょう。
削除フラグを設けることで検索速度が遅くなるので、削除したデータを保持しておきたいのであれば検索に利用しない削除済みデータを管理する別のテーブルを用意しそちらに移動させるか、ログから削除したデータが追えるようになっているほうが良いと考えます。
※論理削除については以下Webページで語られているので深く知りたい方はご確認ください。
27. 論理削除とは何か？どのような解法があるのか？ w/ twada
インデックスの作成
検索速度を早くしたい場合にインデックスを作成しますが、インデックスにも順序性があり、実際にSQLのwhere句で指定されるキーの順序と合致しないとそのインデックスは利用されません。
また、メリットだけではなくデメリットが存在し、検索速度が早くなる代わりにそれ以外のINSERT,UPDATE,DELETEは遅くなります。
良くインデックスは文字通り書籍の索引に例えられますが、索引があれば引くのは早く、ページに変更(INSERT,UPDATE,DELETE)があれば索引も再構築しなければならない為、遅くなります。現実世界のものに例えると理解しやすくなりますね。
なお、主キーには暗黙的にインデックスが作成されます。
さいごに
テーブル設計を第３正規形になるように順を追って設計し、その他に気を付けるべき点を紹介しました。
他にもテーブル設計の要素として外部キーやデータ型、トリガーなどがありますが、入門記事ということもありさくっと読めるように割愛しています。
第３正規形がいかなるときも最適かと言うとそんなことは無く、整合性よりも処理速度を優先するようなシステムの場合には第２正規形にすることもありますし、今回は入門記事のため触れませんでしたがレコード数が多い場合にはパーティショニングやシャーディングの検討が必要になることもあります。
型を崩すにしても基本を知っているのと知っていないのでは大違いですのでまずは基本をマスターしましょう。その上で崩すところは崩す。
重要なのはシステムの規模や特性にあった設計を行うことです。
それでは良いテーブル設計ライフを！