ブログ

テーブル設計チェックリスト

執筆 更新日

テーブル設計に正解表はありません。同じ要件を三つのチームに渡せば、三つのスキーマが返ってきます。それでも、実務で問題になる場所はだいたい決まっています。名前をどう付けるか、データ型を何にするか、主キーをどう取るか、NULLを許すか、インデックスをどこに置くか。この五つで曖昧なまま進んだ判断が、数か月後に修正作業として戻ってきます。

以下は完成したスキーマではなく、テーブル設計で名前・キー・データ型・NULL・インデックスを決めるときの判断基準です。最後のチェックリストに同じ項目をまとめています。

命名：規則を先に決める

名前はスキーマの中でいちばん長く残ります。データ型はあとからALTERで変えられますが、名前はアプリケーションのコードやクエリ、ドキュメントにまで広がっているので、変えるコストが桁違いです。

テーブル名

単数か複数かを決めて揃えます。 どちらが正しいという話ではなく、揃っていることが答えです。複数形（members、posts）のほうがやや多いのは、user のような単数名が予約語とぶつかるDBMSがあるからです。どちらを選んでも、一つのスキーマの中で混ざらなければ問題ありません。

小文字とアンダースコアを基本にします。 order_items のような書き方なら、大文字小文字の扱いの違いで悩まずに済みます。サーバーのOSによって扱いが変わるDBMSがあるため、大文字を混ぜると環境を移したときに表面化します。

接頭辞は情報を持つときだけ付けます。 すべてのテーブルに付く tb_ は何も伝えません。一方、一つのDBに複数のサービスが同居する構成なら、shop_orders のようなドメイン接頭辞は役に立ちます。

中間テーブルは二つの名前をつなぎ、意味が出たら改名します。 投稿とタグをつなぐ post_tags は、つなぐだけなのでこの名前で十分です。ここに「誰が」「いつ」タグを付けたかが加わると、もう連結専用ではないので、post_taggings のようにそれ自体で読める名前のほうが適切です。

カラム名

いくつか規則を決めておくだけで、後の設計が速くなります。

  • 主キーは id。 membersテーブルの中でわざわざ member_id と書く理由はありません。参照する側で member_id になることで関係が見えます。
  • 外部キーは 対象テーブルの単数_id。 member_id、category_id の形です。同じテーブルを二回参照するときは、writer_id、approver_id のように役割を前に付けます。
  • 時刻は _at、日付は _date。 created_at DATETIME と birth_date DATE を区別しておけば、定義を開かなくても中身が分かります。
  • 真偽値は is_ か has_ から始めます。 active だけだと値が1のときにどちらの状態か迷いますが、is_active なら迷いません。
  • 単位は名前に入れます。 price より price_jpy、weight より weight_kg です。単位をカラムの説明にだけ書いても、クエリを書く人は読みません。

略語の一覧を作っておきます

qty、amt、cd、dt のような略語は現場ごとに少しずつ違います。三つ以上使うつもりなら、略語と正式名の対応表を定義書のどこかに残しておくとよいです。あとから参加した人が reg_dt を登録日と読むか登録日時と読むかで迷わずに済みます。定義書に何を入れるかはテーブル定義書の作り方にまとめてあります。

主キー：サロゲートキーを基本に、例外は意図的に

主キーは設計の中でいちばん戻しにくい決定です。参照するテーブルが増えるほど、変更コストが跳ね上がります。

基本はサロゲートキーです。 id BIGINT AUTO_INCREMENT のような値には業務上の意味がないので、業務ルールが変わっても影響を受けません。メールアドレスを主キーにすると、会員がアドレスを変えた瞬間に、その値を参照していた全テーブルを直すことになります。

自然キーが適する場面もあります。 国コードや通貨コードのように国際標準で固定された値、そして中間テーブルの (post_id, tag_id) 複合キーが代表です。値が変わらないと言い切れて、参照側で結合を減らせるなら自然キーのほうが単純です。

INTとBIGINTの境界を先に決めます。 INTの上限は約21億です。会員テーブルなら届きませんが、ログや履歴のように行が速く積み上がるテーブルは最初からBIGINTが安全です。稼働中に大きなテーブルのINTをBIGINTへ変えると、長いロックを伴います。

UUIDを主キーにするときに知っておくこと

UUIDは目的がはっきりしているときに使います。複数サーバーで挿入前にキーを作る必要があるとき、連番が外に出ると困るときが典型です。注文番号が1ずつ増える値なら、競合他社が一日の注文量を推測できてしまいます。

代わりに二つのコストが付いてきます。容量が増え、ランダムに生成されたUUIDは挿入位置がインデックス全体に散らばります。 B木インデックスは値が順番に入るときがいちばん効率的で、ランダムな値は毎回別のページに触れるため、挿入性能とキャッシュのヒット率が一緒に落ちます。よく使われるUUIDバージョン4がこれにあたります。

この問題を減らすために作られたのが UUIDバージョン7 です。2024年のRFC 9562にまとめられた形式で、先頭48ビットにミリ秒単位の時刻を入れ、残りは実装に応じてより細かな時刻・カウンタ・乱数で構成します。そのためバージョン4より時間順に並び、挿入位置もインデックスの右端寄りに集まります。ただし、同じミリ秒内の生成順まで常に保証されるわけではなく、そこは生成器の実装次第です。PostgreSQL 18には uuidv7() 関数が入り、ほかの環境ではライブラリで生成する方法が一般的です。

もう一つよく使われる構成は、役割を分けることです。内部の結合と外部キーは数値のサロゲートキーで処理し、APIやURLに出す識別子だけUUIDを別に持ちます。性能と露出の問題をそれぞれ別のカラムで解決する形なので、規模が大きくなったサービスでよく見かけます。

データ型の選び方

データ型はデータが溜まってから変えるのが面倒です。最初から完璧である必要はありませんが、次の基準を守るだけで後の手戻りは大きく減ります。

文字列

VARCHAR を基本に、桁数は根拠を持って決めます。名前のカラムを何でも VARCHAR(255) にするのは、255に意味があるからではなく昔の既定値の名残です。メールアドレスは255、表示名は50、郵便番号は固定長の CHAR というように、データの性格に合わせれば十分です。

本文のように上限が読めない値は TEXT 系を使います。MySQLの TEXT は65,535バイトが上限で、utf8mb4の日本語ならおよそ2万字前後です。長文を扱うサービスなら MEDIUMTEXT が必要になります。

VARCHAR(255)にUNIQUEを張ってよいのか

ネット上でよく見かける助言に「utf8mb4では191文字を超えるとインデックスを張れない」というものがあります。いまは条件付きでしか正しくないので、そのまま従うと不要な制約を自分で作ることになります。

InnoDBのインデックスキー接頭辞の上限は、行フォーマットとページサイズで変わります。既定の16KBページでDYNAMICまたはCOMPRESSEDなら3,072バイト、REDUNDANTやCOMPACTなら767バイトです。ページサイズを8KBや4KBにすると、3,072バイトの上限も比例して小さくなります。utf8mb4は1文字最大4バイトなので、767バイト基準だと191文字が限界になり、あの助言が生まれました。

MySQLは5.7.9から既定の行フォーマットがDYNAMICなので、既定の16KBページなら VARCHAR(255) は最大1,020バイトで3,072バイト以内に収まります。この条件ではメールアドレスのカラムにUNIQUEを張れます。 古いサーバーから移したテーブルや、行フォーマット・ページサイズを変えた環境では上限が異なるため、エラーが出たらその設定を確認します。

データ型ごとの上限値

設計中によく調べることになる数字をまとめておくと便利です。MySQL基準です。

データ型 範囲・上限 実務で当たる場面
INT 約 -21億 〜 21億 ログ・履歴テーブルで到達する
INT UNSIGNED 0 〜 約43億 負の値が出ないときだけ
BIGINT 約 ±922京 実質的に心配は不要
VARCHAR 行全体65,535バイトの範囲で カラムが多いと桁数を削ることに
TEXT 65,535バイト 長文は MEDIUMTEXT
DATETIME 1000年 〜 9999年 範囲が問題になることはほぼない
TIMESTAMP 1970年 〜 2038年 2038年の上限は実在する制約
DECIMAL 全体65桁、小数30桁 精度が足りないことはない

このうち実際によく当たるのは二つです。行全体のサイズが65,535バイトに制限されるので、VARCHAR(255) を数十個並べたテーブルは作成時にエラーになります。そしてInnoDBテーブルのカラム数上限は1,017個です。この数字に近づいているなら、型の問題ではなくテーブルを分けるべき合図だと読むほうが正しいです。

文字セットと照合順序も設計項目です

データ型だけ決めて文字セットを飛ばすと、あとでデータが壊れます。MySQLで日本語と絵文字をきちんと保存するには utf8mb4 が必要です。名前の似た utf8 は最大3バイトまでのutf8mb3の別名で、絵文字のような4バイト文字が来ると保存に失敗します。MySQL 8.0でutf8mb3は非推奨とされたので、新しく作るスキーマなら迷う必要はありません。

照合順序（コレーション）は比較と並べ替えの規則です。大文字小文字を区別するか、アクセントを区別するかがここで決まります。名前の末尾が _ci なら大文字小文字を区別せず、_cs なら区別します。メールアドレスにUNIQUEを張りつつ大文字小文字を区別しない照合順序を使うと、[email protected] と [email protected] が同じ値として扱われますが、これは多くのサービスでむしろ望ましい挙動です。逆にコード値やハッシュのように大文字小文字が意味を持つカラムなら、照合順序を個別に指定します。

文字セットと照合順序はデータベース・テーブル・カラムのそれぞれで指定できるため、混ざりやすい部分です。照合順序の違うカラム同士を結合するとインデックスが効かず遅くなることがあるので、スキーマ全体で一つに揃えておくほうが安全です。

数値

整数は想定行数を基準に INT と BIGINT を選びます。負の値が出ないなら UNSIGNED で範囲を倍にできますが、計算の途中で負になる場面がないかを先に確認します。

金額と率は DECIMAL です。 FLOAT や DOUBLE は二進の浮動小数点なので0.1を正確に表せません。一件ずつ見れば問題なく見えても、何万件も合計すると1円ずれます。DECIMAL(15,2) のように全体桁数と小数桁数を明示し、税率や為替レートは小数桁を余裕を持って取ります。

日付と時刻

DATE は日付だけ、DATETIME と TIMESTAMP は時刻まで持ちます。誕生日のように時刻が意味を持たない値を DATETIME にすると、深夜0時をまたぐ比較で取りこぼしが起きます。

タイムゾーンの方針を先に決めます。 一国内のサービスでも、サーバーが複数リージョンにある、あるいは将来海外に広げると、保存されている時刻の基準が問題になります。UTCで保存して表示時に変換する方式がいちばん扱いやすいです。MySQLの TIMESTAMP はセッションのタイムゾーンで変換され、2038年の上限もあるので、この違いを知ったうえで選びます。

真偽値と状態

MySQLには専用の真偽値型がないので TINYINT(1) を使います。大事なのは、その値が本当に二つで終わるかを先に確かめることです。承認が「承認・却下・保留」の三つになった瞬間、真偽値では表せません。

状態のカラムに ENUM を使うと手軽ですが、値を追加するたびにテーブル定義の変更が必要です。状態が増える見込みがある、あるいは状態ごとに表示名や並び順といった付帯情報が付くなら、コードテーブルを置いて参照するほうが広げやすくなります。

JSONカラム

JSON は、丸ごと保存して丸ごと読むデータに向きます。ユーザー設定値や外部APIのレスポンス原文のように、構造がよく変わり検索対象ではない値がこれにあたります。

逆に、その中の値で検索したり結合したりするなら、カラムとして取り出すべきです。JSON配列にタグを入れると、カンマでつないだ文字列カラムとまったく同じ問題を抱えます。判断の根拠はデータベース正規化の第1正規形の項に例つきであります。

NULLの方針：「ない」と「空」を分ける

NULLは、値がまだ存在しないという事実を記録する正常な状態です。問題は、NULLと空文字と0を同じ意味で混ぜたときに起きます。電話番号のカラムにNULLの行と空文字の行が混在すると、触るクエリすべてに条件が二つ必要になります。

基本方針はNOT NULLです。 必ず値があるべきカラムはNOT NULLで塞ぎ、必要ならデフォルト値も付けます。そうすればアプリケーションが入れ忘れてもデータが欠けません。

NULLを許すときは理由を残します。 「まだ入力していない」と「あえて空にした」を区別する必要があるならNULLが正解です。その意図をカラムの説明に一行書いておけば、次の担当者が勝手にNOT NULLを掛けることもありません。

三値論理を押さえておきます。 SQLではNULLとの比較は真でも偽でもありません。WHERE status != 'DONE' はstatusがNULLの行を落とします。集計でもCOUNTはNULLを数えず、AVGはNULLを除いて平均を出します。レポートの数字が合わないとき、最初に見る場所です。

UNIQUE制約とNULLの関係もDBMSによって違います。多くのDBMSはNULL同士を別の値として扱うため、UNIQUEのカラムに複数行のNULLが入ります。一意性が本当に必要なら、NOT NULLも一緒に掛けるのが確実です。

日付と金額のカラムで繰り返される落とし穴

時刻のカラム

created_at と updated_at は全テーブルに置きます。 これがないと、行がいつ入ったのかを後から追う方法がありません。障害を調べるとき、この二つの有無が調査時間を決めます。デフォルト値と自動更新をDB側でやるかアプリケーション側でやるかはチームで一つに決めればよく、決めてあること自体が大事です。

削除の扱いを先に決めます。 行を実際に消さず deleted_at に時刻を残すソフトデリートは、復旧と履歴の面で有利ですが、すべての検索クエリに条件が一つ増えます。半分のテーブルにだけ適用すると、消したはずのデータがどこかで再び見えることになるので、テーブル単位で方針を決めて定義書に残します。

論理削除とUNIQUE制約がぶつかる問題

ソフトデリート（論理削除）を入れると、ほぼ必ず出会う場面があります。会員のメールアドレスにUNIQUEを張った状態で退会を論理削除にすると、退会した人と同じアドレスでは再登録できません。 行が残っているのでUNIQUEに引っかかります。

解き方はDBMSによって分かれます。

  • PostgreSQLは部分インデックスできれいに解決できます。 CREATE UNIQUE INDEX ... ON members (email) WHERE deleted_at IS NULL のように条件を付ければ、生きている行同士だけで一意性を判定します。
  • MySQLに部分インデックスはありません。 代わりに削除フラグをUNIQUEの組み合わせに含める方法を取ります。ただしNULL同士は別の値として扱われるため、生きている行では一意性が担保されず、削除されていない状態を固定値で持つ別カラムを作って組み合わせに入れる形がよく使われます。
  • 削除時に値を書き換える方法もあります。 退会処理でアドレスの後ろに削除時刻を付け、[email protected] の形にすればUNIQUEの衝突は消えます。個人情報の保管方針とも絡む選択なので、企画と一緒に決める話です。

どの方法を選ぶにしても、論理削除を入れると決めたらUNIQUEの張られたカラムを先に洗い出す順番が正しいです。登録できない問題は、たいてい運用に入ってから見つかります。

金額のカラム

金額には通貨が要り、換算が絡むならレートと適用時点まで残さないと、後から計算を再現できません。注文明細に注文時点の単価をコピーして持つのも同じ理由です。これは重複ではなく、その時点の事実の記録です。

リレーションと制約：DBに任せるもの

外部キー制約を張るかどうかを決めます。 張れば不正な参照はDBの側で止まり、関係がスキーマに残るので、そこから生成した図にも表れます。大量投入やシャーディングの環境では制約が負担になる場合もありますが、一般的な業務システムなら張る利点のほうが大きいです。張らないと決めたなら、その事実と理由を文書に残さないと、次の人が「関係がない」と読んでしまいます。

削除規則はリレーションごとに決めます。 基本はRESTRICTで止め、親が消えたら子も意味を失う関係にだけCASCADEを指定します。退会時に投稿をどう扱うかは技術判断ではなく業務ルールなので、企画と一緒に決めるのが筋です。

UNIQUEは単一カラムだけでなく組み合わせにも掛けます。 同じ会員が同じ商品をカートに二度入れられないようにするには、(member_id, product_id) の組み合わせにUNIQUEが必要です。アプリケーションで確認してから入れる方式は、同時リクエストで抜けます。

CHECK制約は値の範囲を守るのに使えます。 評価が1から5の間であるべきなら CHECK (rating BETWEEN 1 AND 5) で止められます。

ここにバージョンの落とし穴があります。MySQLがCHECK制約を実際に強制するようになったのは8.0.16からです。 それ以前のバージョンは構文を受け付けるだけで黙って無視していました。古いスキーマをそのまま持ってきた場合、CHECKが書いてあっても検証されていなかった可能性があるので、バージョンを上げる前に既存データが制約を通るかを確認します。通らないデータが残っていると、強制を有効にした時点でエラーになります。定義だけしておいて今は強制したくない場合は NOT ENFORCED を付けられます。移行の途中段階で使える選択肢です。

インデックス：分かっているものだけ決める

インデックスは読み取りを速くする代わりに、書き込みを遅くし容量も使います。設計段階では確実なものだけ決め、残りは実際のクエリを見てから足すほうが正確です。

外部キーのカラムにはインデックスを置きます。 親を基準に子を探す検索は必ず発生します。MySQLのInnoDBは外部キーのカラムに自動で作りますが、すべてのDBMSがそうではないので確認が必要です。

複合インデックスは列の順番が肝心です。 (member_id, created_at) のインデックスは、会員で絞る検索と会員の中での新着順に効きますが、作成日だけで絞る検索には効きません。等値条件に使う列を前に、範囲や並べ替えに使う列を後ろに置くのが基本です。

作った理由を残します。 名前と列だけ並んだインデックスは、次の担当者が消してよいか判断できません。「会員別の新着一覧用」という一行が定義書にあれば、必要なインデックスが生き残ります。

あとから広げることを見込んだ選択

コード値はテーブルで管理します。 注文状態や会員ランクのように増えうる項目は、コードテーブルに置いて参照します。コードごとに表示名、並び順、使用可否まで持てるので、画面に出すときも扱いやすくなります。

履歴が要るかを先に判断します。 値が変わったときに前の値を知る必要がある項目なら、履歴テーブルかスナップショットのカラムが今すぐ必要です。あとから足しても、その間のデータは戻せません。

多言語はカラムではなく行で増やします。 name_ja、name_en のようにカラムを増やすと、言語が増えるたびにテーブル構造の変更が要ります。翻訳テーブルを置いて言語コードで分ければ、言語の追加はデータ登録で終わります。

PostgreSQLならここが変わります

ここまでの基準はほぼそのまま当てはまりますが、判断が変わる箇所がいくつかあります。

文字型の桁数で悩む理由が少ないです。 PostgreSQLでは varchar(n) と text に性能差がありません。桁数の制限は保存方式ではなく業務ルールを表す手段なので、本当に制限が要る値にだけ桁数を置き、残りは text にするスタイルがよく見られます。

連番のカラムはIDENTITYを推します。 serial は古い書き方で、標準構文の GENERATED ALWAYS AS IDENTITY が推奨されています。シーケンスの所有権と権限の扱いがすっきりします。

時刻は timestamptz を基本にします。 名前と違ってタイムゾーンを保存するのではなく、UTCに正規化して保存し、読み出し時にセッションのタイムゾーンへ変換する型です。タイムゾーンの扱いをコード側で細かく書かずに済むので、複数地域を扱うサービスなら既定値にする価値があります。

部分インデックスがあります。 上で見た論理削除とUNIQUEの衝突が WHERE deleted_at IS NULL の一行で解決します。条件に合う行だけがインデックスに入るのでサイズも小さくなります。MySQLにはない機能なので、二つのDBMSをまたぐ設計ならこの違いを知っておく必要があります。

大文字小文字の扱いがほぼ逆です。 PostgreSQLは既定で大文字小文字を区別し、引用符なしの識別子は小文字に畳まれます。MySQLから移したスキーマで名前が想定と違って見えるなら、たいていこの規則が原因です。

同じ設計を二つのDBMS向けに出す必要があるなら、データ型の対応表を先に作っておくほうが安全です。DBMS間の文法の違いはSQLからER図を自動生成するにパーサーの視点でまとめてあります。

設計レビュー用チェックリスト

設計が終わったら、図を前に次の項目を順に確認してみてください。

  • テーブル名の単数・複数の規則が揃っているか
  • 予約語と重なる名前がないか
  • カラム名の規則（外部キー・時刻・真偽値）が一貫しているか
  • すべてのテーブルに主キーがあるか
  • サロゲートキーと自然キーの選択に根拠があるか
  • 行が速く増えるテーブルの整数型が足りているか
  • 金額のカラムがDECIMALか
  • 時刻カラムのタイムゾーンの基準が決まっているか
  • created_at と updated_at があるか
  • 削除の方式（物理・論理）がテーブルごとに決まっているか
  • NULLを許可したカラムに理由が書かれているか
  • 同じ概念のカラムがテーブル間で同じ型か
  • 外部キーがリレーションのN側にあるか
  • 削除規則がリレーションごとに決まっているか
  • 重複を防ぐべき組み合わせにUNIQUEがあるか
  • 外部キーのカラムにインデックスがあるか
  • 数えれば出る値を保存していないか
  • 一つのマスに複数の値を入れたカラムがないか
  • コード値が複数テーブルに散らばっていないか
  • 各カラムに一行の説明があるか

エンティティやキーを決める順番そのものがまだ不慣れなら、ER図の書き方に掲示板の例で整理してあり、テーブルをどこまで分けるかの基準はデータベース正規化にあります。

よくある質問

主キーは必ずサロゲートキーがよいのですか？

業務テーブルの多くはサロゲートキーが無難です。メールアドレスや法人番号のように業務上の意味を持つ値は、いつか変わったり再利用されたりして、そのたびに参照している全テーブルを直すことになります。ただし国コードや通貨コードのように値が固定されたマスタや、中間テーブルで二つのキーを組む複合主キーは、自然キーのほうが単純です。

金額はどのデータ型で保存すべきですか？

DECIMALを使います。FLOATやDOUBLEは二進の浮動小数点なので0.1のような値を正確に表せず、何万件も合計すると小数点以下でずれていきます。精算や税計算で1円合わない問題の多くはここから始まります。通貨が複数あるなら、金額カラムの隣に通貨コードのカラムを置くほうが安全です。

NULLを許可しないほうが常によいのですか？

そうとは限りません。NULLは値がまだ存在しないという事実を記録する正常な状態です。問題になるのは、NULLと空文字と0を同じ意味で混ぜて使う場合です。「まだ入力していない」と「あえて空にした」を区別する必要があるならNULLが適切で、その区別が不要ならNOT NULLにデフォルト値を置くほうがクエリは単純になります。

設計段階でインデックスはどこまで決めますか？

外部キーのカラムと、検索条件がはっきりしているカラムまでで十分です。残りは実際のクエリとデータが揃ってから実行計画を見て追加するほうが正確です。インデックスは読み取りを速くする代わりに書き込みを遅くし、容量も使うので、推測で先に作ると費用だけが残りがちです。

データベース設計では命名規則と主キーの方針を先に決め、実際のデータに合う型を選びます。NULLと削除の方針も記録しておけば、テーブルごとに同じ判断を繰り返さずに済みます。

設計が固まってDDLが手元にあるなら、WorksCove ERDに貼り付けてダイアグラムとテーブル定義書を作る方法もあります。貼り付けから結果まではSQLからER図を自動生成するに画面つきでまとめてあります。