はじめに
先日、自作Railsアプリのテーブル設計を見直していて、いくつか「このDB設計で大丈夫だったのか」と気になることがありました。
調べていくうちに、設計の時に「何を考えるべき」かが整理できてきたので、
これからDBを設計する上で、どんな観点で考えると良いかの参考になれれば幸いです。
この記事で出てくるキーワード整理
Step 1. 型の選び方は適切か
│ ├─ 整数型 (INTEGER / BIGINT)
│ └─ 文字列型 (string / text)
Step 2. 制約は設定されているか
│ ├─ NOT NULL制約
│ └─ 外部キー制約
Step 3. スナップショットが必要か
│ └─ その時点のデータを固定して保存
Step 4. 列挙型の選び方は適切か
│ ├─ enum とマスタテーブル
│ └─ CHECK制約
└─ Step 5. 削除の方針は決まっているか
├─ 論理削除
└─ 物理削除と外部キー
Step 1. 型の選び方は適切か
整数型の選び方
Railsでテーブル、カラムを作成する際、整数が入るからint、Railsならt.integerと安易に考えてはいけません。
たとえば、t.integerならDBにはINTEGER型が作られるし、自動で作成されるidカラムは BIGINT になっています。
自作アプリ↓
create_table "users", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", comment: "ユーザー情報を管理するテーブル", force: :cascade do |t|
t.string "name", null: false, comment: "ユーザー名"
t.string "email", null: false, comment: "メールアドレス"
t.string "password_digest", null: false, comment: "パスワードハッシュ（6文字以上）"
t.integer "role", default: 2, null: false, comment: "ユーザー権限（1:admin管理者, 2:owner店舗オーナー）"
t.integer "language_id", default: 1, null: false, comment: "言語ID（1:日本語）"
t.index ["email"], name: "index_users_on_email", unique: true
end
CREATE TABLE `users` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL COMMENT 'ユーザー名',
`email` varchar(255) NOT NULL COMMENT 'メールアドレス（一意）',
`password_digest` varchar(255) NOT NULL COMMENT 'パスワードハッシュ（6文字以上）',
`role` int NOT NULL DEFAULT '2' COMMENT 'ユーザー権限（1:admin管理者, 2:owner店舗オーナー）',
`language_id` int NOT NULL DEFAULT '1' COMMENT '言語ID（1:日本語）',
PRIMARY KEY (`id`),
UNIQUE KEY `index_users_on_email` (`email`),
KEY `index_users_on_deleted_at` (`deleted_at`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='ユーザー情報を管理するテーブル';
実際に発行されるSQLを確認するには、rails dbconsole から SHOW CREATE TABLE で`見るのが一番わかりやすいです。
SHOW CREATE TABLE users;
schema.rb だけでなく、実際のDBも確認する習慣をつけるのが大事だと思いました。
また、整数型には以下のような種類があります。
型 サイズ 最大値(unsigned) 用途
────────────────────────────────────────────────────
TINYINT 1 byte 255 フラグなど
SMALLINT 2 bytes 65,535 小規模マスタ
INTEGER 4 bytes 約43億 ステータス・区分
BIGINT 8 bytes 約1844京 主キー・外部キー ★
INTEGER と BIGINT の違いは 32bit と 64bit の違いで、bit数が増えたときにカバーできる範囲が違います。
INTEGER だと約43億レコードで枯渇してしまう可能性があります。1日1万レコード程度の挿入であっても、長期的には安全側に BIGINT を選んでおくのが一般的なようです。
ただし「全部 BIGINT にすればいいのでは」と思ったのですが、BIGINT は INTEGER の2倍のサイズを使うため、大量レコードに対するインデックスサイズが肥大化してしまったり、キャッシュ効率のが低下してしまったりしてしまうようです。
場合にもよりますが、実務だと、
- 主キーと外部キー (
~_id) は必ずBIGINT - ステータスや区分みたいな小さな数値は
INTEGER
みたいにすると良さそうです。
外部キーは参照先の主キーと同じ型にする必要がある。
Rails のデフォルトで id は BIGINT なので、外部キーも BIGINT になります。
文字列型の選び方
t.string を書くと自動的に varchar(255) のデータ型で作成されます。
t.string と t.text の使い分けは、「その文字列がどのくらい長いか」で判断しますが、名前やタイトルのような短い文字列なら t.string、URLや説明文みたいな長さが予測しにくいものなら t.text を選ぶのが適切です。
ただ、varchar には検索やインデックスの効率が高いという側面もあるようなので、適宜判断する必要がありそうです。
- 225文字を超える文字が入ることはない場合は、
t.string - 何文字入るかわからない場合、
t.text
データ型参照
Railsのマイグレーションファイルや作成されたDBがどうなっているか簡単に確認できるように載せておきますm(_ _)m
RailsとDBのデータ型の比較
├─ t.string → varchar(255)
├─ t.text → text
├─ t.integer → int
├─ t.bigint → bigint
├─ t.decimal → decimal
├─ t.boolean → tinyint(1)
├─ t.date → date
├─ t.datetime → datetime(6)
└─ t.json → json
文字列系
├─ VARCHAR(n) 可変長 最大n文字 名前・メールなど
├─ TEXT 最大 65KB 説明文・コメント
├─ MEDIUMTEXT 最大 16MB 長いテキスト
└─ LONGTEXT 最大 4GB 大量テキスト
数値系
├─ TINYINT 1 byte 0 ~ 255 フラグ
├─ SMALLINT 2 bytes 0 ~ 65,535 小規模マスタ
├─ INTEGER 4 bytes 0 ~ 約43億 ステータス・区分
├─ BIGINT 8 bytes 0 ~ 約1844京 主キー・外部キー ★
└─ DECIMAL(p,s) 可変 正確な小数 金額・価格 ★
日時系
├─ DATE 生年月日・発行日
├─ DATETIME 作成日時・更新日時
└─ TIMESTAMP タイムゾーン影響あり
その他
├─ BOOLEAN true/false
├─ JSON JSON文字列
└─ BLOB バイナリデータ
Step 2. 制約は設定されているか
NOT NULL と制約の考え方
NOT NULL を書いていないカラムは 当たり前ですが、、NULL が入る可能性があります。必須の情報に NULL が混じっていると、表示や検索で不整合が発生しやすくなります。
ここで「モデル側とDB側、どちらで制限すべき」かも考えてみましょう。
Railsの慣習では、バリデーション（範囲チェックなど）はモデル層の validates で行い、データ整合性の安全网としての制約は DB 層で行うのが一般的です。
つまり NOT NULL や外部キー制約は DB 層で、範囲チェックなどは モデル層で対応するのが妥当とされています。
外部キー制約の役割
外部キー制約をつけていない場合に何が起こるかを考えてみましょう。
外部キー制約がない場合、存在しないIDが外部キーに保存されることがあります。
これは「孤児レコード」と呼ばれ、その記録がどの親レコードに紐づくかが不明になります。
create_table :posts do |t|
# t.references で外部キー制約も自動で付く
t.references :user, null: false, foreign_key: true
end
外部キーに NOT NULL をつけるかどうかは、「その関連が必須か任意か」で判断します。
記事と著者のように必ず紐づく関連なら NOT NULL、タグのように付けない場合もある関連なら NULL 許可にすると適切です。
- 外部キーを必須にしたい場合、
NOT NULLつける - 外部キーにはするもののその関連が任意の場合、
NOT NULLつけない
Step 3. スナップショットが必要か
スナップショットの必要性
あるカラムの値が後から変わった場合に、過去のデータも変わってしまうことを考えてみましょう。
例えばイベント参加記録に対してユーザー名を user_id だけで参照していると、ユーザー名が変わった後に古い参加記録を見る時には新しい名前が表示されてしまいます。
イベント参加記録みたいなものは「その時点の情報」を永続化する必要があります。
スナップショットなし
└─ user_id: 1 → JOINで名前を取る → 変更後に古い参加記録も新しい名前になる ❌
スナップショットあり
└─ user_name: "田中太郎" → 参加時点で固定 → いつでも正しい ✓
Railsの実装としては before_create コールバックで、レコード作成時にスナップショット対象のカラムを埋めと良さそうです、。
class EventAttendance < ApplicationRecord
belongs_to :user
belongs_to :event
before_create :snapshot_info
private
def snapshot_info
self.user_name = user.name
self.event_name = event.name
end
end
イベント名もイベント側で変えられる可能性があるので固定しておくべきです。
「後から変わりうる情報」はスナップショットに、「その時点で決まった値」はそのまま保存するのが判断の基準になります。
スナップショット対象の判断は「その値がマスタデータとして後から変わりうるか」で考えると簡単です。ユーザー名やイベント名は変わりうる対象、参加日や参加区分は変わらない対象です。
Step 4. 列挙型の選び方は適切か
enum とマスタテーブルの使い分け
ステータスなどの状態管理で、「公開済み」「公開」「published」表記がブレる可能性があります。
これを防ぐためには、整数コードで管理するのが妥当です。
Railsでは enum を使うと、DBの中身は整数で保存されるのに、アプリ側では名前で扱えるようになります。
class Post < ApplicationRecord
enum :status, {
draft: 0,
published: 1,
archived: 2
}
end
enum は少なくて静的なパターン（2〜10種類）には適切ですが、パターンが多くて動的に変わりうる場合はマスタテーブルにする方がいいです。
マスタテーブルにすると、新しいパターンの追加や無効化がコード変更なしで対応できます。
enum の整数値には意味がある。よって、途中で値を変えたり並び順を入れ替えたりすると、既存データの意味が変わってしまいます。値の追加は末尾に限定して、削除も簡単にできなくなる。
CHECK制約とモデル側のバリデーション
CHECK制約は DB側で値の範囲や条件を制限できます。
ただし Railsでは慣習として、範囲チェックはモデル側で対応するのが一般的です。
モデル側にすると変更が簡単で、エラーメッセージも返せるためです。
CHECK制約にすると、範囲が変わるたびにmigrateが必要になります。
class Post < ApplicationRecord
TITLE_MAX = 100
validates :title, length: {
maximum: TITLE_MAX
}
end
定数にしておくと、範囲が変わった時にも変更箇所が1つに集中し、フォームのmax属性なども同じ値を参照できます。
CHECK制約は金融系のような「絶対に誤りが許されない」場合のみ使うのが妥当です。
Step 5. 削除の方針は決まっているか
論理削除（deleted_at）を使っていれば、レコードは残るので外部キー制約に引っかかることはありません。
しかし論理削除なしで外部キー制約がある場合に削除したとなると、削除の順序を管理する必要があります。
子テーブルを先に削除し、親テーブルを後に削除するのが基本的なやり方です。
もう一つの方法として ON DELETE CASCADE があります。
これは親レコードが削除されると子レコードも自動で削除されますが、イベント参加記録みたいなデータに使うのは危険です。
削除されてはいけないデータは論理削除が適切です。
イベント参加記録のような履歴データに ON DELETE CASCADE を使うと、親レコードの削除で記録が永久に消えてしまいます。
削除されてもいいデータ → 削除順序の管理 or CASCADE
削除されたくないデータ → 論理削除 (deleted_at)
まとめ
調べていて「こうすべき！」という絶対に正しい答えがないと感じました。
何を作るかによって設計も変わるので、「こうすべき」よりも「なぜこうするか」を理解しておくことの方が大事だと思います。
この記事をまとめ直した際にも、自分の自作アプリのスキーマを見直して「これ大丈夫だったのか」と気になることがまだ残っていました。
実務で設計する際にポイントを思い出せる記事になっていれば幸いです。
設計の考える流れ
Step 1. 型の選び方は適切か → 整数型・文字列型の選び方
↓
Step 2. 制約は設定されているか → NOT NULL・外部キー制約
↓
Step 3. スナップショットが必要か → 過去データの固定
↓
Step 4. 列挙型の選び方は適切か → enum かマスタテーブルか・範囲チェック
↓
Step 5. 削除の方針は決まっているか → 論理削除か物理削除か