【DB作成前に】ちゃんとDB設計する上で覚えておいたほうがいい9つのこと

43テーブルのDB設計をしてみて思った、DB設計する上で認識しておくべきこと。ちゃんと設計することで、シンプルなSQLクエリだけで開発できるようになる。

覚えておいたほうがいいこと

テーブル名は複数形にする

以下のようにusersやdepartmentsなど複数形にして名前を決定する。

CREATE TABLE users (
    username VARCHAR(50) NOT NULL
    password CHAR(36) NOT NULL
    department_id CHAR(36) NOT NULL
    solt CHAR(36) NOT NULL
    PRIMARY KEY (username)
    FOREIGN KEY (department_id) REFERENCES departments (department_id)
)

CREATE TABLE departments (
    department_id CHAR(36) NOT NULL
    department_name VARCHAR(50) NOT NULL
    PRIMARY KEY (department_id)
)

IDをidにしない

IDのカラム名をidにしない。user_id、role_idなど。○○_idにする。

例外として、独自にコーディング規約がidとして付けると記載があるならコーディング規約に従う。

NOT NULL制約

なるべくNULL値を入れない。

UNIQUE制約

重複バグがおきない。DISTINCTが必要ない。データ追加(INSERT)時ラク。
WHERE句での条件一致で役に立つ。無駄なSQLを発行しなくてよい。

INSERT INTO calender (date) VALUES ('2022-12-25');

個人的にはUNIQUE制約が一番好き。

外部キー制約

複数の箇所にデータ(文字列など)を保存しない。idを付けて外部キーを設定する。

テーブルをちゃんと分ける

テーブル構造(カラム)が同じになっていてもテーブルを分ける。

UUID / ULID

UUID、ULIDを使う。むやみにAUTO INCREMENTしない。

カラムを無駄に増やさない

わざわざ年月日を分けているテーブル。意味ない。

CREATE TABLE calender (
    date DATE NOT NULL UNIQUE -- 日付
    year VARCHAR(4) NOT NULL -- 年
    month VARCHAR(2) NOT NULL -- 月
    day VARCHAR(2) NOT NULL -- 日
    PRIMARY KEY (date)
)

SELECT concat(year, '/', month, '/', day) FROM calender;
-- 結果: 2022/12/25

これだけ。クエリで対応すべき。

CREATE TABLE calender (
    date DATE NOT NULL UNIQUE -- 日付
    PRIMARY KEY (date)
)

SELECT DATE_FORMAT(date, %Y/%m/%d) FROM calender;
-- 結果: 2022/12/25

予備カラムを設定しない

いつ使うかも分からない予備のカラムを設定しない。その都度ALTERを使って対応すること。

/*
データ型、制約も不明な予備1、予備2。とりあえずVARCHAR(255)設定
運用後、予備1が数値だけのデータだったらVARCHARで運用？
*/
CREATE TABLE calender (
    date DATE NOT NULL UNIQUE -- 日付
    reserve1 VARCHAR(255) -- 予備1
    reserve2 VARCHAR(255) -- 予備2
    PRIMARY KEY (date)
)

予備を設定している時点で設計がちゃんとできていない証拠。だいたい文字列型で設定しがち。不安だからで設定してはいけない。無駄は省こう。

予備が本当にほしいなら、どんな用途で使用するか？など、ちゃんと要件を確定して具体的なカラム名と型を決めること。