Eloquent 前提の MySQL データベース設計 #PHP

大前提

Laravel の Eloquent リレーションを前提 とする。
原則として DB ビルダーは使わない。つまり、SQL クエリ（が想像できるコード）は書かない。
たとえ DB ビルダーを使ったとしても as でテーブル名の省略しない（可読性優先）。
Eloquent で Join 禁止。子テーブルの値を参照するならリレーション、検索判定するなら whereHas がある。
原則として select も書かない。
- select で絞るほど多数のカラムがあようなら、「横持ち」から「縦持ち」に設計しなおすべき。

ゆえに、生の SQL クエリ（DBビルダー含む）を書く前提である巷のベストプラクティスとは、真っ向から反する命名規則となる。

Eloquent 前提の命名規則

テーブル、カラム共通

テーブル名、カラム名ともに小文字でスネークケース（foo_bar）
ローマ字禁止。英語でがんばる。
省略語は禁止。

テーブル名

テーブルは原則複数形。
複数単語の合成後の複数形に自信がないなら単数形（影で笑われる よりもマシ）
- 複数でも単数でも Eloquent モデルの $table は必ず書く。
接頭辞は原則つけない。
設計規模が大きすぎるときは接頭辞を検討する。例として：
- m_ ：変更がすくないマスター系テーブル
- t_ ：ユーザーによる変更される（トランザクション系）
- l_ ：ログテーブル

カラム名

カラム名にはテーブル名を含めない。
- product_name や user_name ではなく、name とする。
- リレーション前提なので $product->name が美しい。
- join は使わないし、原則 select も書かないので name を区別する必要はない。
プライマリーは必ず id。
_id は他テーブルへのリレーションキー。
- 論理名が「なんとかID」であっても、物理名は _key や _code、_no 等とする。
- DB レベルの外部キー制約は無理にふる必要はないが、インデックスはふる。
Boolean 型は is_、has_、use_等（_flag 禁止）
Data 型は _on。
Datetime 型は _at（Timestamp型は 2038 年問題があるので避ける）

原則は NOT NULL

すべてのカラムは 原則 NOT NULL とする。
nullable として良いのは……
- 必須ではないユニークカラム。
- text カラム（DEFAULT を持てないので）
- 初期値を持たない日付カラム。
nullable でないなら、必ず DEFAULT を定義 する。
- 未定義の文字列なら ""、数値なら 0 を指定。
- カラムにより適切な初期値があるならそれ。

未入力という概念がある数値については、0 を表示しないという処理を laravel でできるので、DB 的には NOT NULL default 0 のほうが扱いやすい。0 を含めた入力値の平均を求めるなら nullable を検討する（想像できないけど）。

NULL を許可しない理由を一言でいうと「MySQL だから」。MySQL ドキュメント「8.4.1 データサイズの最適化」から……

可能な場合は、カラムを NOT NULL として宣言します。それにより、インデックスを適切に使用し、各値が NULL であるかどうかをテストするためのオーバーヘッドがなくなることで、SQL の操作が速くなります。カラムあたり 1 ビットでいくらかのストレージ領域も節約します。テーブルで実際に NULL 値が必要な場合、それらを使用します。単にすべてのカラムで NULL 値を許可するデフォルトの設定を避けます。

Oracle では空文字列を NULL として扱う ので、Oracle 起源のシステムは NULL が原則となっている。MySQL 開発者は Oracle マスターに引け目があるのか、右にならって NULL を並べてしまうのかもしれないが、デメリットしかない。「NULL撲滅委員会」で検索すると、その主張や論争を知ることができる。なお、PostgreSQL でも同様 のはず。

Laravel には、空文字列を NULL に置換するミドルウェア ConvertEmptyStringsToNull がデフォルトで組み込まれているので忘れずに削除する。……Laravel のマイグレーションのデフォルトは NOT NULL なのに、一貫性がないとないと感じるのは自分だけ？

なお、Quasar という Vue フレームワークを使ったとき、Quasar が空文字を NULL に置き換えていた。メンバーが API を半分使ってしまった後で気づき、大いに困った。

また、必ず default を定義 するのは、まずオブジェクトとしての空レコードを作成する……というコードが必要になる可能性もあるので。カラム指定がなくてもレコード作成が成功する 前提を保つ。

論理削除は使わない

論理削除を導入するとユニークを担保できない。
削除に見えるもものの多くはステータス変更である。
他テーブルとリレーションが発生する前のゴミレコードは物理削除してよい/したい。
証跡が必要なら audits ログで対応する。

マイグレーション

開発初期

開発初期はテーブルごとに新規作成マイグレーションを作成する。
開発中の構造修正もこの新規作成マイグレーションを書き直す。
データシーダーを作成し、構造変更してもテストデータをリフレッシュできるようにする。

クライアント環境へのデプロイ後

php artisan schema:dump でデータダンプを取得し、既存マイグレーションは database/migrations_backup/ へ移動する（この作業はプロジェクトマネージャが実施）
以後の構造修正は、修正単位ごとにテーブル更新のマイグレーションを作成し、１テーブル１マイグレーションにはこだわらない。
マスターデータ内容に追加や修正が必要な場合は、データシーダーを更新するだけでなく、マイグレーション内に insert や update を記述する。
ただし、migrate:fresh --seed したときはシーダーよりマイグレーションが先に走るので、insert が二重にならないような配慮が必要。

// migrate:refresh --seed ではないときに実行
if (!in_array('--seed', $_SERVER['argv'], true)) {

マイグレーションのルール

default をつけ忘れない。
すべてのカラムに comment を定義する。
同様にテーブルそのものにも comment を定義する。
リレーションキーは id の型に合わせて unsignedBigInteger とし、インデックスをつける。
timestamps() は使用せず、datetime で定義する。
コンポジット（多対多対応の中間）テーブルは複合プライマリーを使う。
- ロードバランサーでクラスターを組んでいるとき、プライマリーではない複合ユニークキーでは更新エラーとなる。
複合プライマリーは逆引きには対応できないので、副キー側に独立したインデックスをふる。

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name')->commen('ユーザー名');
    $table->string('email')->unique()->comment('メールアドレス');
    $table->integer('company_id')->default(0)->index()->comment('会社');
    $table->datetime('email_verified_at')->nullable()->comment('メール確認日時');
    $table->string('password')->comment('パスワード');
    $table->rememberToken()->comment('リメンバートークン');
    $table->datetime('created_at')->nullable();
    $table->datetime('updated_at')->nullable();
});
DB::statement("ALTER TABLE users COMMENT 'ユーザー';");

// コンポジットテーブル
Schema::create('project_user', function (Blueprint $table) {
    $table->integer('project_id')->comment('プロジェクト');
    $table->integer('user_id')->comment('ユーザー');
    $table->primary(['project_id', 'user_id']);     // 複合プライマリ
    $table->index('user_id');                       // 副キー側のインデックス
});
DB::statement("ALTER TABLE project_user COMMENT 'プロジェクト・ユーザー';");

ファクトリー

モデルごとに必ずファクトリーを書く。
ファクトリーは、テーブル（モデル）ごとに１レコードのダミーを定義するもので、そう難しく考えることはない。
テーブル定義に DEFAULT が指定されているなら、ファクトリーは空でもレコード作成は成功する。
それっぽい値がほしいカラムにだけ、faker や now()->subDays(rand(7, 30)) 等で値を定義すれば十分。
ダミーを生成する faker の用法については、「faker チートシート」等で検索する。

データシーダー

シーダーは、関連（リレーション）する複数のテーブル（モデル）をまとめて、１つのシーダーとする。
例えば、5個の会社とそれに紐づくユーザーを5～20作成するシーダーは次のように書く。
create に与えた引数は、ファクトリーで定義したランダム値より優先する。

Company::factory()->count(5)->create()->each(function ($company) {
   User::factory()->count(rand(5, 20))->create([
      'company_id' => $company->id
   ]);
});

または次のように書くこともできる。

$rows = [
    ['company_code' => 'test1'],
    ['company_code' => 'test2'],
    ['company_code' => 'test3'],
    ['company_code' => 'test4'],
    ['company_code' => 'test5'],
];
foreach ($rows as $row) {
   $company = Company::factory()->create($row);
   $max = rand(5, 20);
   foreach ($i = 0; $i < $max; $i++);
       User::factory()->create([
          'company_id' => $company->id
       ]);
    });
  }
}

上記いずれの場合も、User モデルに ユニークカラム（例えば email）があった場合は注意が必要。ファクトリーの unique 指定は一度の ceate の中だけの制限なので、ループさせるとたちまち重複エラーとなる。ファクトリーに任せず、シーダー側でユニーク性を担保する工夫が必要となる。

Company::factory()->count(5)->create()->each(function ($company) {
   User::factory()->count(rand(5, 20))->create([
      'company_id' => $company->id,
      'email' => uniqid() . '@example.com',
   ]);
});

[参照] マイグレーションで積み上げたテーブル構造を可視化するのは次の記事を参照。

MySQL テーブル構造の md を作成