blog マニュアル 開発

【基本設計】DB設計・テーブル定義書の書き方（SQL）

こんにちは、すずきです。
直近でテーブル定義書の記事を複数書いていますが、今回がまとめの総集編になっています。
こちらの記事１本でテーブル定義書が書けるようになります！

ポイント

  • テーブル定義書の概要
  • テーブル定義書の必要項目

本記事に掲載されているテーブル定義書は鈴木の自己開発のものになります。

目次
  1. テーブル定義書とは
  2. 全体的な注意事項
  3. テーブル名
  4. 論理名
  5. 物理名
  6. データ型
  7. 桁（バイト）数
  8. Not Null　（空欄を禁止）
  9. 半角/全角
  10. 英字/数字
  11. PRIMARY KEY　（主キー）
  12. FOREIGN KEY　（外部キー）
  13. デフォルト値　（初期値を指定）
  14. 形式(備考)
  15. テーブル定義書テンプレ・完成版

テーブル定義書とは

テーブル定義書とは、データベース内のテーブルを定義したもの。
要件定義をもとに必要なテーブルのカラムの定義や制約を定める設計書のことを指します。

要件定義をもとに必要なテーブルを決めます。
今回に関しては、タスク管理アプリのテーブル定義書になっているので、ユーザーテーブルとタスクテーブルを作成していきます。

全体的な注意事項

全体的な注意事項は下記４つです。

  • テーブル名、カラム名ともに大文字を利用しない。
  • テーブル名やカラム名ともに複数単語の連携はスネークケース
  • 英語表記で記載する。
  • 略名は使用しない
すずき

１つずつ見ていきましょう！

テーブル名、カラム名ともに大文字を利用しない。

DBによって大文字と小文字を区別するもの、しないものがあるため小文字で統一する。

名前OK / NG
PASSWORDNG
PasswordNG
passwordOK

　

テーブル名やカラム名ともに複数単語の連携はスネークケース

キャメルケースはNG

スネークケースとは

スネークケースとは、英単語を複数合わせるときに、単語間のスペースをアンダースコア（_）に置き換える方式のこと。
例　user_name　user_id　start_date　など

キャメルケースとは

キャメルケースとは、英単語を複数合わせるときに、各単語や要素語の先頭の文字を大文字で表記する方式のこと。
例　userName　userId　startDate　など

名前OK / NG
userNameNG
UserNameNG
usernameNG
user_nameOK

　

英語表記で記載する。

基本的には、ローマ字でなく英語表記をする。

名前OK / NG
ringoNG
appleOK

　

略名は使用しない（絶対ではない）

名前OK / NG
start_dtNG
maker_cdNG
start_dateOK
maker_codeOK
すずき

案件によっては、略名は使用されるらしいので、開発するチームの書き方に統一しましょう。

　

テーブル名

今回は、「ユーザー」テーブルと「タスク」テーブルを作成します。テーブル名は、基本は複数形に統一します。

テーブル名は基本複数形にすること！

すずき

テーブル名のレコードを複数持つため複数形にしています！

テーブル名(論理)テーブル名(物理)OK / NG
ユーザーuserNG
ユーザーusersOK
タスクtaskNG
タスクtasksOK

論理名

論理名は、実際にコンピュータで読み込むものでなく、設計業務内で使う名前になります。論理名を英語に変えたのが、物理名になります。

用語意味
物理「物理○○」という形で登場することが多い。「実際の○○」という意味で使われます。
論理「論理○○」という形で登場することが多い。「見かけ上の○○」という意味で使われます。
物理名コンピュータに使用する実際の名前（本名）
論理名業務で使う日本語名（仮名）

物理名

先ほど紹介した、実際に使う名前です。冒頭にご紹介したように名前は、英語でスネークケース方式で表記します。
基本的に名前の付け方は、ある瞬間の状態を表す名前を命名します。
例　name　age　is_deleted　など

必ず追加しなければいけないカラム
カラム名役割
id主キー
created_at登録日時
updated_at更新日時
すずき

上記のカラムはテーブルに絶対に入れないといけないので、必ず入れましょう。

データ型

データ型はそれぞれの項目に合ったものを入力しましょう。基本的に利用されるデータ型をご紹介します。
下記は、SQLで使用される基本的なデータ型です。

数値型

データ型名カテゴリ説明
INT整数型一般的な整数型。範囲は -2,147,483,648 から 2,147,483,647。
SMALLINT整数型小さめの整数型。範囲は -32,768 から 32,767。
TINYINT整数型非常に小さい整数型。範囲は 0 から 255（符号なし）または -128 から 127（符号あり）。
BIGINT整数型大きな整数型。範囲は -9,223,372,036,854,775,808 から 9,223,372,036,854,775,807。
FLOAT浮動小数点型単精度の浮動小数点型。精度と範囲は実装に依存。
REAL浮動小数点型浮動小数点型。通常、FLOATと同義で使用されるが、精度は実装に依存。
DECIMAL固定小数点型精度を指定できる固定小数点型。主に金額などの精度が重要なデータに使用。
NUMERIC固定小数点型DECIMALと同義。精度を指定できる固定小数点型
すずき

プロジェクトでは、INTとDECIMALが特に頻繁に使用される傾向にあるよ！

文字列型

データ型名カテゴリ説明
CHAR固定長文字列型固定長の文字列を格納するためのデータ型です。データの長さが指定された文字数に満たない場合、残りのスペースは空白文字で埋められます。
VARCHAR可変長文字列型可変長の文字列を格納するためのデータ型です。格納するデータの長さに応じて、必要な分だけのストレージが使用されます。指定された長さの上限を超えるデータは格納できません。
TEXT大文字列型非常に長い可変長の文字列を格納するためのデータ型です。通常、VARCHAR よりも大きなサイズの文字列を格納するために使用されます。データベースごとに制限が異なりますが、かなりの大容量を扱うことが可能です。
CLOB大文字列型大規模な文字データを格納するためのデータ型です。TEXT と同様に非常に長い文字列を扱うことができますが、データベースシステムによっては異なる内部処理や制限がある場合があります。
すずき

プロジェクトでは、CHARとVARCHARとTEXTが特に頻繁に使用される傾向にあるよ！

日付と時刻型

データ型名カテゴリ説明
DATE日付型年、月、日を表す日付型です。
TIME時刻型時、分、秒を表す時刻型です。
DATETIME日付時刻型日付と時刻の両方を表す日付時刻型です。
DATETIME2日付時刻型DATETIME型の拡張版で、精度が向上した日付時刻型です。ナノ秒までの精度を持つことができます。
TIMESTAMP日付時刻型日付と時刻の両方を表すデータ型で、通常はUNIXタイムスタンプとして秒数で格納されます。データベースによっては、自動的に挿入または更新される日時を格納するために使われます。
すずき

作ったやつ間違えてた！日時の場合は、DATEじゃなくてDATETIME！

通貨型

データ型名カテゴリ説明
MONEY通貨型大きな通貨の値を格納する。高精度の通貨計算が必要な場合に使用されます。例えば、金融トランザクション、給与計算、請求書の金額など。
範囲: -922,337,203,685,477.5808 から 922,337,203,685,477.5807（小数点以下4桁）
SMALLMONEY通貨型小さな通貨の値を格納する。小規模の金額を管理する際に使用されます。例えば、少額の取引、予算管理、日常的な経費など。
範囲: -214,748.3648 から 214,748.3647（小数点以下4桁）
すずき

大規模システムや金融系は、MONEY。中小規模システムや一般的なビジネスアプリケーションはSMALLMONEY！

ブール型

データ型名カテゴリ説明
BOOLEANブール型真 (TRUE) または偽 (FALSE) のいずれかの値を格納する

バイナリ型

データ型名カテゴリ説明
BINARY固定長バイナリ型固定長のバイナリデータを格納する
VARBINARY可変長バイナリ型可変長のバイナリデータを格納する
BLOB大バイナリ型大きなバイナリデータ（Binary Large Object）を格納する
すずき

バイナリデータとは、テキストデータ以外のコンピュータが見てわかるデータのこと！

謎に論理削除フラグの桁数をデータ型に入れてしまってますが、これは間違いなので、修正します。

桁（バイト）数

すずき

データ型の部分でミスが多かったので皆さんも気をつけてください！

次は、桁（バイト）数です。
桁（バイト）数とは、データがコンピュータのメモリやストレージにどれだけのスペースを必要とするかを示す単位のこと！
1バイト＝8ビット
桁（バイト）数の決め方を見ていきましょう！

桁（バイト）数の決め方

桁数は、基本的にデータ型によって変わります。

一般的なデータ型のバイト数（UTF-8エンコーディング）

データ型バイト数
TINYINT1バイト
SMALLINT2バイト
INT4バイト
BIGINT8バイト
FLOAT4バイト
DOUBLE8バイト
DECIMAL(p, s)(整数部の桁数 + 小数部の桁数 + 1)バイト
CHAR(n)nバイト
VARCHAR(n)実際の文字数 × 文字のバイト数（UTF-8で1〜4バイト）
DATE3バイト
TIME3バイト
DATETIME8バイト
TIMESTAMP4バイト

UTF-8エンコーディングの場合の文字のバイト数

文字種類例バイト数
英語（アルファベット）A,B,C,D......1バイト
数字0,1,2,3......1バイト
日本語（漢字）漢,字....3バイト
日本語（ひらがな）あ,い,う,え....3バイト
日本語（カタカナ）ア,イ,ウ,エ....3バイト

上記の２つの表をもとに実際のテーブル定義書にバイト数を入力していきましょう。

ユーザーテーブルのバイト数

ユーザーIDはデータ型がCHAR型なのでパスワードに必要な文字数がそのままバイト数に表記されます。パスワードは、10桁の英数字での表記にしたいため、10バイトです。

ユーザー名に関しては、今回のアプリは日本向けのアプリなので、日本語の10文字表記にしたいと思っています。データ型がVARCHARなので、実際の文字数（10文字） × 文字のバイト数（日本語で3バイト）＝30バイト

すずき

CHARとVARCHARはよく使うので、計算方法を間違えないようにしましょう！

Not Null　（空欄を禁止）

Not Nullとは、空欄を禁止する項目です。Nullが「何もないことを表す目印」という意味は、駆け出したエンジニアだったらご存知のはず！
なので、必ず入力をしなければいけないところに「Not Null or ◯ or YES」を入力し、必ずしも必要ではないところを空白にします。

すずき

無知なすずきがしたミスは、Not NullとYESを同じ表に混在させてしまったこと。
必ず 「Not Null or ◯ or YES」のどれかで統一しましょう。

表の画像を見て分かる通り、初めのユーザー登録は、全部必須で入力してほしいため、Not Nullにしております。逆にタスクテーブルでは、タスク詳細〜状態までは、別に入力がなくても問題ない項目のため空白にしています。

半角/全角

これは、簡単ですね。入力する文字が英数字だけの場合は半角で、日本語も使う場合は全角です。

すずき

AIイラストがよくできてる！

英字/数字

これは、上記に似ていますが、入力するときに、英字（アルファベット）を使う場合は英字に◯、数字を使う場合は数字に◯。
ネット検索で出てくるテーブル定義書には、基本入っていない項目でしたが、現場によっては、必要かと思いますので、上記のように入力しましょう。

PRIMARY KEY　（主キー）

次はPRIMARY KEY（主キー）です。※PKに省略します。
物理項目でも紹介しましたが、PKは必須項目です。

PKがどうして必須項目なのか特徴から見ていきましょう。

PRIMARY KEY（主キー）の特徴

  • 一意に特定できるカラムのこと。
  • 重複したデータを入力するとエラーとなる。
  • 必ずデータが格納されていないといけないため、NULLを挿入することはできない。
  • 表に一つのみ定義することができる。
すずき

一意とは、重複しないということ。

皆さんがイメージしやすいところでいうと、国民のマイナンバーがPKにあたります。
自分の基本情報はマイナンバーに登録されているので、マイナンバーを入力すれば、顔や名前・住所など個人情報を照会することができます。

同じ使い方になるので、登録したデータをデータベース内で検索する際にPKを利用します。
そのためテーブル定義でPK（id）を作らないと検索することができないので、必ず入力しましょう。

FOREIGN KEY　（外部キー）

次は、FOREIGN KEY　（外部キー）です。※FKに省略します。
FKはテーブル間の関係性を設定するためのキーです。もう少し詳しく書いてみます。

テーブル間の関係性を設定する

今作っているテーブル定義書で例えると、
このタスク管理アプリで、3人のユーザーが同時にタスクを登録したと仮定します。

タスクID（PK）タスク名タスク詳細
0001〇〇商事　商談〇〇商事に訪問して商談を行う。持ち物は...
0002撮影YouTubeの撮影を新宿で行う。
0003ブログ記事作成ブログ記事のプロットは......

例えば上記のようなタスクが3人の手によって作られました。PKは書いてあるものの、誰が作ったタスクなのかを判別するものがありません。
そのためFKを設定し、テーブルの関係性を設定する必要があります。

ユーザーテーブル

ユーザーID（PK・FK）ユーザー名
user_0001ユーザー１
user_0002ユーザー２
user_0003ユーザー３

タスクテーブル

タスクIDユーザーID（FK）タスク名タスク詳細
0001user_0003〇〇商事　商談〇〇商事に訪問して商談を行う。持ち物は...
0002user_0001撮影YouTubeの撮影を新宿で行う。
0003user_0002ブログ記事作成ブログ記事のプロットは......

上記のようにユーザーテーブルのPKをタスクテーブルのFKで設定すると、タスクID:0001を設定したのは、ユーザーID:user0003と関連づけることができるのがFKです！



デフォルト値　（初期値を指定）

次は、デフォルト値です。デフォルト値とは、初期値を指定することです。
今回の定義書で利用している部分は、論理削除フラグの列で利用しています。今回のように、初期値の場合だと未削除で1を入力すると削除されるなどの、初期値から変更したら変化が起きる場合などに利用することがあります。

表記方法は、今回の0やNULLを入力する場合もあります。

上記２つのようなケースがほとんどです。
タスク管理とかでも間違って消しちゃったものが、すぐ復元できなかったらかなりストレスです。なので、そういった面でも論理削除フラグは、登録日時・更新日時の用に記載する必要があります。

形式(備考)

日付形式

最初に間違えてしまった例で言うと日付の形式を「形式(YYYY/MM/DD)」という表記で入力してしまってました。
これは、単純な思考で大文字、小文字にしてはいけません。正解は、「形式(yyyy/MM/dd)」になります。
では、なぜyとdは小文字でMは大文字なのか下記図をみて理解しましょう。

日時フォーマットは日時パターン文字列で指定されます。日時パターン文字列内では、引用符で囲まれていない'A' - 'Z'および'a' - 'z'は、日付または時間文字列のコンポーネントを表すパターン文字として解釈されます。テキストは単一引用符(')で囲むことで解釈を回避できます。"''"は単一引用符を表します。ほかのすべての文字は解釈されず、フォーマット中に出力文字列へ単純にコピーされるか、解析中に入力文字列に対して一致させられます。

次のパターン文字が定義されます(ほかの'A' - 'Z'および'a' - 'z'のすべての文字は予約済み)。

文字日付または時刻のコンポーネント表示例
G紀元テキストAD
y年年1996; 96
Y暦週の基準年年2009; 09
M年における月(状況依存)月July; Jul; 07
L年における月(スタンドアロン形式)月July; Jul; 07
w年における週数値27
W月における週数値2
D年における日数値189
d月における日数値10
F月における曜日数値2
E曜日の名前テキストTuesday; Tue
u曜日の番号(1 =月曜、...、7 =日曜)数値1
a午前/午後テキストPM
H一日における時(0 - 23)数値0
k一日における時(1 - 24)数値24
K午前/午後の時(0 - 11)数値0
h午前/午後の時(1 - 12)数値12
m分数値30
s秒数値55
Sミリ秒数値978
zタイムゾーン一般的なタイムゾーンPacific Standard Time; PST; GMT-08:00
ZタイムゾーンRFC 822タイムゾーン-0800
XタイムゾーンISO 8601タイムゾーン-08; -0800; -08:00
クラスSimpleDateFormat

上記があります。そのため、YYYY/MM/DDだと全く意味の違うものになってしまうので、必ずこの表を元に日付などの形式は、作成しましょう。
yyyy/MM/dd hh:mm:ss　
（DBによっては、YYYY-MM-DD HH:MI:SS)の表記をしましょう！

値と説明の対応表

優先度（0：高、1：中、2：低）
状態（0：未対応、1：処理中、2：完了）
論理削除フラグ（0：未削除、1：削除）

上記を選択肢から選んでもらう項目の表記になります。
数字で表記する場合は、0から作成しましょう。別に数字でなくても問題ないので、ここは臨機応変にチームで統一しましょう。



テーブル定義書テンプレ・完成版

これで完成です！ここまでいかがでしたでしょうか。
命名規則を揃えたり、表記方法・形式を揃えるのは徹底して制作していきましょう！

参考記事

データベースオブジェクトの命名規約 - Qiita
オブジェクトの命名規約DB設計によく携わっていた頃に多くのプロジェクトで共通で規定されていた規約をまとめてみました。ここでは オブジェクト とし...
Qiita
SQLのデータ型一覧│数値型や日付型など主要6つの型を詳細解説 | アナリティクス沖縄│DataAnalytics
SQLで使われるデータ型について。数値型・通貨型・文字列型・日付型/時刻型・ブール型・バイナリ型の主要6つの型を解説します。 それぞれ、範囲や形式...
アナリティクス沖縄│DataAnalytics
DB設計 テーブル定義書・SQL
Zenn

 

無料でHP制作をする

  • 本日
  • 週間
  • 月間

  1. 【キングダム】番吾の戦い（はんごのたたかい）誰が死ぬ！？史実を元に解説 11PV

  2. 【基本設計】DB設計・テーブル定義書の書き方（SQL） 8PV

  3. 【2short.ai】YouTubeの切り抜き動画をたった10秒で作るAIツール　 8PV

  1. 【キングダム】番吾の戦い（はんごのたたかい）誰が死ぬ！？史実を元に解説 264PV

  2. 【基本設計】DB設計・テーブル定義書の書き方（SQL） 155PV

  3. 【2short.ai】YouTubeの切り抜き動画をたった10秒で作るAIツール　 37PV

  1. 【キングダム】番吾の戦い（はんごのたたかい）誰が死ぬ！？史実を元に解説 264PV

  2. 【基本設計】DB設計・テーブル定義書の書き方（SQL） 155PV

  3. 【2short.ai】YouTubeの切り抜き動画をたった10秒で作るAIツール　 37PV

  • この記事を書いた人

鈴木陽介

現役Javaエンジニア

2000年生まれ

もうすぐ書籍を出版します。

-blog, マニュアル, 開発