<MySQL>テーブル設計とカラム定義の入門〜実践と参考テンプレート

#テーブル設計とは
アプリケーションの基本的な一連の流れとして、

- フロントエンドから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文
基本的にデフォルトで必要なカラムは大体こんな感じで、

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

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

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


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

#####SQLのcreate文

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

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


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