【学習】Mysqlのテーブルについて考える。名前、年齢、テーブル間のつながり。

はじめに

MYSQLを学習するにあたり、もっと先の事を考えたテーブル構造、というものを意識する機会がありました。
今回は私が作成しているミニアプリ？のテーブル構造の改善例をまとめておきたいと思います。

環境

・PC：Window
・使用エディタ：VsCode
・一応下記記事の練習環境で作成しているDBになりますが、他の言語・フレームワークでも同じことがいえる？

改善前のmysqlは？

CREATE TABLE IF NOT EXISTS member (
    id          INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(255) NOT NULL,
    age         INTEGER NOT NULL,
    occupation  ENUM('会社員', '学生', '主婦（夫）', 'その他') NOT NULL,
    drink       ENUM('炭酸', '清涼飲料', 'お酒', '水', 'お茶') NOT NULL,
    remarks     TEXT
)

memberテーブルの中身をこのような構造にしていました。
この状態から改善していきたいと思います

改善：「name」の「255」は多すぎる

[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
可変長文字列です。 M はカラムの最大長を文字数で表します。 M の範囲は 0 から 65,535 です。 VARCHAR の有効な最大長は、最大行サイズ (65,535 バイト、すべてのカラムで共有されます) と使用される文字セットによって決まります。 たとえば、utf8 の文字は 1 文字につき最大 3 バイトを必要とする場合があるため、utf8 の文字セットを使用する VARCHAR カラムは、最大 21,844 文字になるように宣言できます。 セクション8.4.7「テーブルカラム数と行サイズの制限」を参照してください。

つまり、現実的に255文字の名前を持つ人はいない可能性が高いことと、不要なデータを使用することを避ける為、60文字に変更

改善2：自動的に年齢更新できるようにする

現在のままでは、年齢が変わるたびに手動での修正が必要になる構造となっています。これは非常に面倒くさい😢
なので以下記事と記事内にある公式マニュアルを参考に修正
ageをbirthdayに変え、DATA型にする

改善3：後から情報増えた時、修正を容易にするために

現在、memberテーブル上でoccupationとdrinkがENUM型で定義されています。データが少ないならこれでいいですし、以降変更は全くないのならこのままでも問題ないかと思います。

ですが、ポイ活とかでよくみる質問内容は選択肢が変わるもの。なのでこちらも修正が容易な構造に変更してみたいと思います。

• occupationとdrinkをそれぞれ別のテーブルに分ける。
• occupationとdrinkの選択肢は、ID と文字列のリストで管理 する。
• memberテーブルでは、選択肢のID（外部キー）を保持する。

改善4：作成日時と更新日時

保守運用の観点からcreated_at・updated_atを各テーブルに追加します。
SQL の DEFAULT CURRENT_TIMESTAMP と ON UPDATE CURRENT_TIMESTAMP を活用します

記事から追加する理由を簡潔にまとめると下記のような事が挙げられます

• データ変更履歴の管理 → いつ作成されたのか、いつ更新されたのかが分かる
• デバッグやトラブルシューティング → 変更日時を追えることで、データの問題を特定しやすくなる
• 保守性の向上 → 更新日時を使って、最近更新されたデータを取得しやすい

ここまでの内容をまとめてみる

👇初期データを入れるSQLを別に管理

sql\seed.sql

-- 職業リスト
INSERT INTO occupation (name) VALUES 
('会社員'), ('学生'), ('主婦（夫）'), ('その他');

-- 飲み物リスト
INSERT INTO drink (name) VALUES 
('炭酸'), ('清涼飲料'), ('お酒'), ('水'), ('お茶');

👇スキーマ定義（テーブルの形）だけ書く

sql\mysql.sql

-- 職業テーブル (occupation)
CREATE TABLE IF NOT EXISTS occupation (
    id   INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(60) NOT NULL UNIQUE,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 飲み物テーブル (drink)
CREATE TABLE IF NOT EXISTS drink (
    id   INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(60) NOT NULL UNIQUE,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS member (
    id          INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(60) NOT NULL,
    birthdate   DATE NOT NULL,
    occupation_id INTEGER NOT NULL,
    drink_id     INTEGER NOT NULL,
    remarks     TEXT,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (occupation_id) REFERENCES occupation(id),
    FOREIGN KEY (drink_id) REFERENCES drink(id)
);

その他参考記事

・長さ制限を考えるうえで

さいごに

テーブル構造をもっと深ぼっていくことの大切さが少しわかりました。自分目線でなくて、他の人にもわかりやすいように、保守運用だったり、先の事を意識していくことが重要…
奥深いなぁ、と感じた今日この頃。
今回の記事が何か参考になれば幸いです。