テーブル設計とは

アプリケーションの基本的な一連の流れとして、

フロントエンドからAPIを叩いて、
バックエンドにJSONのオブジェクトを渡し、
データベースのテーブルに値を登録する

という処理がある。

そのJSONのデータを、
テーブルの構造に合わせてレコードとしてうまく登録していくというのが、
テーブル設計として大切になってくる。

特に、
5W1H
つまり、
Who（だれが）、When（いつ）、Where（どこで）、What（なにを）、Why（なぜ）、How（どのように）
を意識しながら設計するとわかりやすくなる。

テーブルに保持するデータは、大まかに分けると二つ。

マスタデータ
トランザクションデータ

マスタとは、`設定`に関わる情報を保持するテーブルのこと。

例えば、会員登録情報 や、マイページ設定 など、
一般的にそういった変更があまり頻繁ではないような設定データ
を保持する用途のテーブル。

CRUD(Create:作成, Read:読込, Update:更新, Delete:削除)
ができる。

※削除といっても、物理的にそのレコード1行分を丸々削除するやり方と、論理的に、delete_flgをもって値は残すけど、意味としては削除させるやり方の2パターンある。

トランザクションとは、日々の`業務処理`に関わる情報を保持するテーブルのこと。

例えば、銀行口座の取引記録や、在庫の記録など、
日々の業務処理に関わるデータ
を徒然なるままに記録・保持する用途のテーブル。

基本的には、
Create：作成, Read:読込
ができる。
※基本的には、Update:更新、 Delete:削除はできない。取引の記録に矛盾が生じたり、整合性が取れなくなってしまうから。

マスタテーブルの基本設計

SQLのCreate文

基本的にデフォルトで必要なカラムは大体こんな感じで、

CREATE TABLE mydb.`master_table_mst` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `start_date` date NOT NULL DEFAULT '1900-01-01' COMMENT '開始日',
  `end_date` date NOT NULL DEFAULT '2382-12-31' COMMENT '終了日',
  `delete_flg` int(11) NOT NULL DEFAULT 0 COMMENT '削除フラグ',
  `corp_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '会社ID',
  `business_office_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '事業所ID',
  `master_data` bigint(20) NOT NULL DEFAULT 0 COMMENT 'マスタデータ',
  `created_by` varchar(36) DEFAULT NULL COMMENT '作成ユーザー',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成日時',
  `updated_by` varchar(36) DEFAULT NULL COMMENT '更新ユーザー',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日時',
PRIMARY KEY (`id`),
UNIQUE KEY `uidx_master_table_mst_01` (`id`,`start_date`,`delete_flg`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='マスタテーブル';

※mydbの部分を自分のdbの名前に変更する
※テーブル名の語尾が_mstになっているが、運用方針にもよるが、_mstとか_trnでテーブル種類を分ける運用。

データ例

レコードの例も以下のような感じになる想定。
※会社や事業所を取り扱わない場合は、corp_idとbusiness_office_idは不要。
　例えば、ユーザーとかだったら、user_idなどに置き換える

id	start_date	end_date	corp_id	business_office_id	master_data	created_by	create_at	updated_by	updated_at
1	2021-02-03	2382-12-31	1	1	optional_data	hoge	2021-02-03 00:00:00	hogehoge	2021-02-04 00:00:00
2	2021-02-03	2382-12-31	1	2	optional_data	hoge	2021-02-03 00:00:00	hoge	2021-02-04 00:00:00
3	2021-02-03	2382-12-31	2	1	optional_data	hoge	2021-02-03 00:00:00	hogehoge	2021-02-04 00:00:00
4	2021-02-03	2382-12-31	3	1	optional_data	hoge	2021-02-03 00:00:00	hoge	2021-02-04 00:00:00

カラムの解説

基本的に、
テーブルには必ずidカラムがあって、ユニークかつオートインクリメントをつけるのが普通。
created_at, updated_atで使う型として
DATETIME型とTIMESTAMP型が選べるが、注意点がある。

TIMESTAMP型はサポートされている範囲が狭いため注意が必要：
範囲：　'1970-01-01 00:00:01' UTC ～ '2038-01-19 03:14:07' UTC

DATETIME型は月や日付に 00 を許容するため、こちらも注意が必要です。
範囲：　'1000-01-01 00:00:00' ～ '9999-12-31 23:59:59'

カラム名	型	長さ	説明
id	BIGINT	20	サロゲートキー(代理キー)：業務上は意味を持たないが、システム的にユニーク(一意)な値をとる。基本は、オートインクリメントなどで連番を振り、プライマリーキー（PK）にも指定。 ※長さはよしなに変更
start_date	DATE		更新する時の履歴を持つための開始日。 ※変更履歴を持たない場合は不要
end_date	DATE		更新する時の履歴を持つための終了日。 ※変更履歴を持たない場合は不要
delete_flg	INT	11	論理削除フラグ。通常は、 0:削除してない、1:削除済って感じ ※長さはよしなに変更
corp_id	BIGINT	20	会社情報 ※長さはよしなに変更
business_office_id	BIGINT	20	事業所情報 ※長さはよしなに変更
master_data	VARCHAR/TEXT/INT/BIGINT	種類による	テーブルに合わせたデータを保持するカラム。カラム名も好きなものに設定する。
created_at	DATETIME		レコードが作成された年月日時間 ※timestampも可能
created_by	VARCHAR	36	誰が作成したか
updated_at	DATETIME		レコードが更新された年月日時間 ※timestampも可能
updated_by	VARCHAR	36	誰が更新したか

`5W1H`の観点からの説明

上記のカラムを
5W1H
に当てはめてみると

1	2
Who（だれが）	created_by, updated_by
When（いつ）	start_date, end_date, created_at, updated_at
Where（どこで）	corp_id, bussiness_office_id
What（なにを）	master_data
Why（なぜ）	カラムの意義: 例えば、会員情報を保持するため
How（どのように）	master_dataの利用目的：例えば、会員情報を特定するため、とか、他のテーブルと紐づけるためとか

と言うことになり、よりカラムの意味と利用目的が明確になってくる。

こういった視点で設計すると良い。

トランザクションテーブルの基本設計

SQLのcreate文

マスタテーブルとは違い、Update:更新、 Delete:削除はしない想定なので、
start_date、end_date、updated_at、updated_by、
は不要。よりシンプルになる想定。

CREATE TABLE mydb.`transaction_table_trn` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `corp_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '会社ID',
  `business_office_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '事業所ID',
  `transactional_div` int(11) NOT NULL DEFAULT 0 COMMENT 'トランザクション区分値',
  `transactional_data` bigint(20) NOT NULL DEFAULT 0 COMMENT 'トランザクションデータ',
  `created_by` varchar(36) DEFAULT NULL COMMENT '作成ユーザー',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成日時',
PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='トランザクションテーブル';

※mydbの部分を自分のdbの名前に変更する
※テーブル名の語尾が_trnになっているが、運用方針にもよるが、_mstとか_trnでテーブル種類を分ける運用。

データ例

例えば、口座の取引金額だとしたら、データはこんな感じになって、

| id |corp_id | business_office_id | transactional_div |transactional_data |created_by | create_at |
|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|
| 1 | 1 |1 |0 | 100000 | hoge | 2021-02-03 00:00:00 |
| 2 | 1 |2 |1 | 20000 | hoge | 2021-02-03 00:00:00 |
| 3 | 2 |1 |2 | 10000 | hoge | 2021-02-03 00:00:00 |
| 4 | 3 |1 |3 | 50000 |hoge | 2021-02-03 00:00:00 |

カラムの解説

カラムの定義は以下のようになる。

カラム名	型	長さ	説明
id	BIGINT	20	サロゲートキー(代理キー)：業務上は意味を持たないが、システム的にユニーク(一意)な値をとる。基本は、オートインクリメントなどで連番を振り、プライマリーキー（PK）にも指定。 ※長さはよしなに変更
corp_id	BIGINT	20	会社情報 ※長さはよしなに変更
business_office_id	BIGINT	20	事業所情報 ※長さはよしなに変更
transactional_div	INT	11	トランザクションのデータが何を意味するかを決めるカラム。 0:入金, 1:出金, 2:送金, 3:引き落としみたいな区分値を設けて使用
transactional_data	VARCHAR/TEXT/INT/BIGINT	種類による	テーブルに合わせたデータを保持するカラム。カラム名も好きなものに設定する。
created_at	DATETIME		レコードが作成された年月日時間 ※timestampも可能
created_by	VARCHAR	36	誰が作成したか

`5W1H`の観点からの説明

上記のカラムを
5W1H
に当てはめてみると

5W1H	対象カラム
Who（だれが）	created_by
When（いつ）	created_at
Where（どこで）	corp_id, bussiness_office_id
What（なにを）	transactional_data
Why（なぜ）	カラムの意義: 例えば、金額のやりとりを保持するため
How（どのように）	transactional_div 例えば、どういった名目のデータかを決める

まとめ

テーブルには2種類ある：マスタテーブル、トランザクションテーブル
マスタテーブル：変更があまりないような設定を保持するテーブル
トランザクションテーブル：変更や削除はしないが、つれつれと記録を時系列に記していくテーブル
テーブル設計とカラム定義するときには、5W1H、つまりWho（だれが）、When（いつ）、Where（どこで）、What（なにを）、Why（なぜ）、How（どのように）を意識することが大切