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

#### 大前提


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

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


## Eloquent 前提の命名規則
#### テーブル、カラム共通
- テーブル名、カラム名ともに小文字でスネークケース（foo_bar）
- ローマ字禁止。英語でがんばる。
- 省略語は禁止。
  - flag を flg、code をcd などすると、混在してバグを生みます。
#### テーブル名
- テーブルは原則複数形。
- 複数単語の合成語の複数形に自信がないなら単数形（**影で笑われる** よりもマシ）
  - 複数でも単数でも 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 を並べてしまうのかもしれないが、デメリットしかない。<a href="https://www.google.com/search?q=NULL%E6%92%B2%E6%BB%85%E5%A7%94%E5%93%A1%E4%BC%9A" target="_new">「NULL撲滅委員会」で検索</a>すると、その主張や論争を知ることができる。なお、**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 が二重にならないような配慮が必要。

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

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

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

```php
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 の用法については、<a href="https://www.google.com/search?q=faker%20%E3%83%81%E3%83%BC%E3%83%88%E3%82%B7%E3%83%BC%E3%83%88" target="_new">「faker チートシート」等で検索</a> する。

#### データシーダー

- シーダーは、関連（リレーション）する複数のテーブル（モデル）をまとめて、１つのシーダーとする。
例えば、5個の会社とそれに紐づくユーザーを5～20作成するシーダーは次のように書く。
create に与えた引数は、ファクトリーで定義したランダム値より優先する。
```php
Company::factory()->count(5)->create()->each(function ($company) {
   User::factory()->count(rand(5, 20))->create([
      'company_id' => $company->id
   ]);
});
```
- または次のように書くこともできる。
```php
$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 の中だけの制限なので、ループさせるとたちまち重複エラーとなる。ファクトリーに任せず、シーダー側でユニーク性を担保する工夫が必要となる。
```php
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 を作成](https://qiita.com/mikomey/items/323649a8affc8a6e68f8)