DB スキーマ設計のガイドライン

この記事は、2011年頃に書かれた Yii framework サイトの wiki 記事 Guidelines for good schema design の翻訳です。

もともとは Yii 1.1 のために書かれたものですが、Yii 2, Yii 3 にもそのまま適用可能ですし、もっと広く、アクティブ・レコードのような ORM 一般に通用する内容であろうと思われます。つまり、以下の文章中の "Yii" という名前は、あなたが使っている任意のフレームワークの名前に置き換えてもよい筈です。

はじめに

事実上すべての Yii アプリケーションはデータベースの上に構築されます。Yii はデータベースの取り扱いにおいて非常に柔軟ではありますが、以下に述べる設計上の選択をすれば、そうでない場合に比べて、ものごとがより一層都合良く進みます。

最初に、ごく大まかに言うと、Yii アプリケーションではアクティブ・レコードを多用しますので、設計上の考慮では、複雑な SQL クエリを構築しようとする人間のことよりも、アクティブ・レコードを使う上での最適化を中心に置くべきです。実際、以下に述べる設計のガイドラインの多くは、SQL フレンドリーなスキーマを作成するための良い習慣とは真っ向から対立するものです。

しかし、このガイドラインのほとんどは、他人が読んで理解できるコードを書けるようにすることを目的としています。コードにおいては名前は 意味を持つ ものであり、一貫性に欠ける命名規約はコードを追うことを非常に難しくするのです。

このことはフォーラムやチャットの #yii チャンネルで助力を求める場合に特に当てはまります。正しい意味を反映しない変な名前を使っていると、コードが何をやっているのかを明確にするための逆質問ばかりを沢山もらって、実際の問題に対してはあまり助力を得られないということになりかねません。

一貫性が非常に重要なのです。

とは言え、以下はガイドラインに過ぎず、規則ではありません。これらのガイドラインに従わなくても、コードは動きます。しかし、これらのガイドラインを採用する方が、楽な道を歩むことが出来ます。

テーブル名は単数形にする、複数形にしない

SQL テーブル は複数の事物を保持するものと考えられますが、モデル はその中のたった一つの事物です。$model = new Comments() という記述は何か変です。この違和感が、リレーションを定義するときをはじめとして、どこまでも付いて回ることになります。

テーブルは comments でなく comment, invoices でなく invoice としましょう。そして対応するモデル名も単数形 (Comment, Invoice) にします。

どうしても DB スキーマを変更できない場合は、少なくとも、Yii のモデル・クラスの名前を単数形に変更します。ただし、コード中に追加のコメントを記して、この不整合に対する注意を喚起しておきましょう。

フィールド名にテーブル名を追加しない

これは伝統的な SQL スキーマ設計では当り前の習慣ですが、アクティブ・レコードを使う場合には、鬱陶しいものになります。たとえば、category テーブルでは、こうです。

category.sql

-- 駄目                            -- おっけ
create table category (            create table category(
    category_id    INTEGER ...,        id    INTEGER ...,
    category_name  VARCHAR ...,        name  VARCHAR ...,
    category_value INTEGER             value INTEGER
);                                 );

Category.php

<?php
$category = new Category();
// ぐえ                            // 良いよね
$category->category_id             $category->id
$category->category_name           $category->name
$category->category_value          $category->value

長い名前のやり方は、手作業で SQL クエリを組む場合にちょっとだけ読みやすくなるのは事実ですが、アクティブ・レコードで使うのには向いていません。

モデルのクラス名にテーブル・プレフィックスを含めない

Yii はテーブル・プレフィックスの考え方をサポートしています。これは共有ホスト環境で すべての アプリケーションが単一のデータベースを共有する場合に有用なものです。例えば、ブログのテーブル名の頭には blog_ を付ける、時間管理のテーブル名の頭には time_ を付けるなどすると、同じデータベースの中でお互いに干渉することなく動作させることが出来ます。

tbl_ というプレフィックスは、多くのチュートリアルやサンプルで見かけます。

しかし、クラス名 にはこれらのプレフィックスを決して含めるべきではありません。なぜなら、干渉を回避する必要が無いからです。ブログのアプリケーションは、時間管理のアプリケーションとは全くの別物です。

Comment.php

<?php
class TblComment extends CActiveRecord {       // 駄目
class Comment extends CActiveRecord {          // おっけ

コードの至る所でプレフィックスを目にするのは気が散る元です。

テーブル自体の ID カラムは "id" という名前にする

たいがいのテーブルは、それ自体の独立した単一カラムのユニークなプライマリ・キーを持っています(int NOT NULL AUTO_INCREMENT PRIMARY KEY というのがよくある例です)。これが id という名前 (commentid とか postid とかではなく) を持っている方が、物事がほんの少しスムーズに動くようになります。

名前が何であっても、Yii はデータベース・スキーマを読んでプライマリ・キーが何であるかを正しく判断します。しかし、システムの他の部分ではそれに従うことが出来ず、キーが id であることを明らかに前提としている場合もあります。

例 : CArrayDataProvider はキーが id であると見なしています。これは keyField 属性によってオーバーライド出来るものではありますが、そもそも、そういう事をする必要が無い方が好都合です。

ただし、この規則が当てはまらない場合もいくつかあります。例えば、テーブルがマルチ・カラムのプライマリ・キーを持っている場合や、テーブルのプライマリ・キーが他のテーブルの ID への外部キーである場合などです。

意味を持つフィールドをプライマリ・キーにしない

古典的な設計上の失敗の一つとして、実際の意味を持つプライマリ・キー、というのがあります。以下の例では、username が user テーブルのプライマリ・キーになっています。

user.sql

-- これやっちゃ駄目!
CREATE TABLE user (
    name   VARCHAR(16) PRIMARY KEY,  -- やめれ
    email  VARCHAR...
    ...
)

これをやると、面倒なことが二つ起きます。

1. これは 4 バイトの整数でなく、16 文字のフィールドであるため、他からの 参照 が非常に非効率的になります。このことは、多くの参照を持つ規模の大きいアプリケーションでは、現実的なパフォーマンス上の問題になり得ます。
2. 外部キー制約がこのシステムで有効になっている場合 ユーザ名の変更 が非常に困難になります。テーブルのフィールドとそれに対するすべての参照を同時に変更しなければならず、仮にそれが可能であったとしても、非常に高く付く処理になります。

整数のプライマリ・キーを作って、name をユニークにする方が断然すぐれています。

user.sql

-- はるかに良い
CREATE TABLE user (
    id     INTEGER PRIMARY KEY AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL UNIQUE,
    email  VARCHAR...
    ...
);

こうすれば、ユーザ名の変更は 1 レコードの更新だけで済みます。

データベース・スキーマで外部キー制約を定義する

たいていのデータベースでは、このフィールドは別のテーブルのプライマリ・キーを指し示す ID を保持している、というような、テーブル間の関係を定義することが出来ます。これが外部キー制約と呼ばれるもので、他から参照されている行が削除されるのを防止するなどして、参照一貫性を保持する上で役に立ちます。

MySQL の InnoDB は外部キー制約をサポートしています。MyISAM では、外部キー制約は 定義 することは出来ますが、強制 することは出来ません。Yii はスキーマからこういう関係を読み取る方法を知っており、Gii/Giix ツールが適切なリレーションを自動的に作成してくれます。

しかし、たとえ Yii が外部キー制約を考慮しないとしても、外部キー制約はデータベースの参照一貫性を維持するための不可欠な要素です。これについて学ぶためには、ウェブ上に沢山のチュートリアルがありますので参照して下さい。

外部キーのフィールドは "id" で終る名前にする

前項と関連しますが、他のテーブル、例えば user の ID を保持するフィールドには user ではなく userid という名前を付けます。と言うのは、まず間違いなく、テーブルに含まれるすべての外部キーについて、それに対応する リレーション を定義する必要があるからです。

Yii では、クラス変数、DB フィールド、仮想属性、リレーションは単一の名前空間を共有します。従って、$model->user をテーブルの外部キー および リレーションの 両方 を示すものとして定義することは出来ません。

外部キーを userid という名前にすれば、BELONGS_TO リレーションである $model->user が自然で分りやすい名前になります。

Post.php

<?php
class Post extends CActiveRecord {
   public function relations()
   {
       return array(
          'user' => array(self::BELONGS_TO, 'User', 'userid')
       );
   }

メモ : id でなく Id や _id を好む人もいます。これは純然たる好みの問題です。ただし一貫性を失わないようにしましょう。

リレーションはその性質に応じた単数形/複数形の名前にする

一貫性と他人にとってのコードの読みやすさというテーマに沿って、リレーションもそれぞれの性質に応じた単数形/複数形の名前を持つべきです。

• HAS_ONE - 単一のモデルを返す : 単数形
• BELONGS_TO - 単一のモデルを返す : 単数形
• HAS_MANY - モデルの配列を返す : 複数形
• MANY_MANY - モデルの配列を返す : 複数形

配列を返すリレーションは、モデルを一つだけ返す場合であってもそれを 配列 に入れて返します。この事実が複数形の名前の根拠となることに注意して下さい。

コードを見ただけで、リレーションが配列を返すのか単一のモデルを返すのか、区別できるようにすべきです。

<?php
    $model->post
    $model->comments
    $model->author
    $model->members

定義を見ないと分らないと言うようでは、コードの可読性と保守性を大いに低下させていると言わざるを得ません。