DB・テーブル設計のプラクティス
DB 設計、テーブル設計のよくある考慮事項、プラクティスをまとめる。
目次
命名規則
- 全体
- テーブル名、カラム名ともに小文字のみ、スネークケースとする。ケースインセンシティブに扱われるケースでの混乱を回避するため
- 英語を使う。ローマ字で書かない
- 略名は使用せず、長くなっても正しく書く
data・infoなど汎用的過ぎて意味の伝わらない単語は使わない
- テーブル
- 複数形が基本
- カラム名
- Boolean 型 :
is_・has_などを使う。flagは真偽の意味が伝わらないので使用してはならない - Date 型 :
【受動態】_on - Timestamp 型 :
【受動態】_at - システム的なデータ :
created_at(登録日時)・updated_at(更新日時) を持たせる - 他テーブルの主キー :
【テーブル名の単数形】_id
- Boolean 型 :
- 参考：データベースオブジェクトの命名規約 - Qiita
- 参考：Railsの基礎知識 | Railsドキュメント
テーブル設計
- 複数データを複数のカラムで「列持ち」しない。拡張性に欠ける・Null チェックが必要になるため。行持ちする
- 姓・名
- カラム長：姓30文字・名文字で60バイトが妥当
- 法人名
- カラム長：137文字・411バイト
- 郵便番号
- 型：整数型ではなく VARCHAR とする (先頭の
0に意味があり、算術演算の対象ではないため) - カラム長：7 or 8文字 (ハイフン込みなら8バイト)
- 参考：郵便番号は整数型か - ockeghem's blog
- 型：整数型ではなく VARCHAR とする (先頭の
- 住所
- カラム長：161文字・483バイト
- 電話番号
- 型：整数型ではなく VARCHAR とする (先頭の
0に意味があり、算術演算の対象ではないため) - カラム長：国番号3文字・以下15文字・ハイフン3文字で21文字 (21バイト) が妥当
- 参考：電話番号計画 - Wikipedia … E.164 にて定められている
- 型：整数型ではなく VARCHAR とする (先頭の
- メールアドレス
- カラム長：ローカル部64文字・ドメイン253文字で、全体の最大値は254文字 (254バイト)
- 参考：メールアドレス - Wikipedia
- 参考：メールアドレスの長さ - Qiita
- 参考：RFC2821(Simple Mail Transfer Protocol (SMTP)) … 4.5.3. にて定められている
- URL
- カラム長：2048文字 (2048バイト) … RFC 2616 では長さの規定はないが、IE で解釈できる最大桁数を元に設定しておく
- 参考：URL の最大長は、2,083 文字Internet Explorer
- 参考：名前、電話番号、メールアドレス、郵便番号等の最適なmaxlengthはいくつか調べてみた | Be full stack
コード値
- 性別
- ISO 5218 で生物学的性別 (Gender ではなく Sex) を表記するためのコード値が標準化されている
0: not known (不明)1: male (男性)2: female (女性)9: not applicable (適用不能)- 参考：ISO 5218 - Wikipedia
- 参考：システムで「性別」の情報を扱う前に知っておくべきこと - Qiita
- 参考：DBテーブルに性別カラムを作るなら、男=1, 女=2が標準 - Qiita
- コード値はマスタテーブルに意味を寄せることで、外部キー制約により不適切な値が入らないよう制御できる
- 都道府県
- JIS X 0401 で定められている
- 参考：全国地方公共団体コード - Wikipedia
- 参考：JIS X 0401都道府県コード - CyberLibrarian
- 参考：都道府県マスタを作る
- 言語コード
- ISO 639 で定められている。ISO 639-1 は2文字、ISO 639-2 では3文字のコードを定めている
- 日本語は
jp(ISO 639-1)、jpn(ISO 639-2) - 参考：ISO 639言語コード - CyberLibrarian
- 参考：言語コードと国コード … MIME ヘッダなどで使う
en-USといった表記は RFC 4646 → RFC 5646 によるもの - 参考：IETF言語タグ - Wikipedia
- 国名コード
- ISO 3166-1 で定められている
- 日本は
392(ISO 3166-1 numeric)、JP(ISO 3166-1 alpha-2)、JPN(ISO 3166-1 alpha-3) - 参考：ISO 3166-1 - Wikipedia
- 参考：ISO 3166-1国名コード - CyberLibrarian
ダミーデータ
- 電話番号
03-1234-5678(3000未満が未設定)070・080・090…0x0-00xx-xxxx〜0x0-09xx-xxxx- 参考：記入例に使うダミー電話番号の探し方 | You Look Too Cool
- 参考：総務省｜電気通信番号制度｜電気通信番号指定状況 （電気通信番号計画（令和元年総務省告示第6号）第1第4項による公表） … 割当のある番号一覧が確認できる
ナレッジ
- 郵便番号 ⇔ 住所を求めて名寄せするのはほぼ無理
- 郵便番号からは住所の一部分までしか分からない
- 住所データはどの文字列までを検索対象とすれば良いか見極めづらく郵便番号を探しづらい
- 地域によって特別な郵便番号が割り当てられている場所もあったりして、すんなりお互いの情報を求めたり名寄せ処理を行ったりするのは困難
- 参考：日本郵便が公開する郵便番号データをそのまま利用するのがなぜ難しいか。そして、住所から郵便番号を求めるのがなぜ難しいか［PR］ － Publickey