シフト管理CURDアプリ 〜DB設計編〜

DB設計/テーブル設計

参考教材

復習記事

DBスキーマ

『シフト作成の時短化を目的としたCRUDアプリ』

エンティティ
・users
name,role

・shifts
shift_date, start_time, end_time, memo

・shift_members

ER図

※全然理解できていない

SQLを日本語で説明しよう

①主テーブル　：　shifts
②絞り込み条件　：　shift-dateが「週の開始日〜終了日」の範囲にあるもの
③結合１　：　shift-membersを結合し、usersIDを取得
④結合２　：　usersを結合し、name/roleを表示する
⑤最後に表示したい項目　：　shift-memo（シフト全体に向けたお知らせメモ）
⑥並び順　：　
曜日　時間　メモ
　月　名前
　火

NOT NULL / UNIQUE制約

『DB設計が設計で終わるか、実務で使えるか』を分ける

NOT NULLやUNEQUE制約では、このシステムで"絶対に守らないといけない"ルールを、DBにどこまで責任を負わせるかを決める

NOT NULL
空で保持されるとシステムが壊れるもの

UNIQUE制約
同じものが2つ存在すると意味が壊れるもの

※全然理解できないため、今回は生成AIの出した制約に従います
※shift_memoは空でもいいのでNULL OK

DDLを書いてみよう

MySQLを作成

brew install mysql
brew services start mysql

mysql -u root -p

DBを作成

CREATE DATABASE shift_management;
USE shift_management;
SHOW TABLES;

SQLエディタを使う

TablePlus

参考にするサイト

各テーブルを作成しよう

shiftsテーブルの作成

CREATE TABLE shifts 
( shiftsID INT AUTO_INCREMENT, 
shift_date DATE NOT NULL, 
start_time TIME NOT NULL, 
end_time TIME NOT NULL, 
shift_memo TEXT , 
PRIMARY KEY (shiftsID));

AUTO_INCREMENT
ID自動採番

usersテーブルの作成

CREATE TABLE users 
( usersID INT AUTO_INCREMENT, 
user_name VARCHAR(20) NOT NULL, 
user_role VARCHAR(20) NOT NULL, 
PRIMARY KEY (usersID));

shift_membersテーブルの作成

CREATE TABLE shift_members
(usersID INT NOT NULL,
shiftsID INT NOT NULL,
PRIMARY KEY (usersID, shiftsID),
FOREIGN KEY (usersID) REFERENCES users(usersID),
FOREIGN KEY (shiftsID) REFERENCES shifts(shiftsID));

各テーブルにレコードを挿入しよう

usersのレコード

INSERT INTO users (user_name, user_role) VALUES
('Matsuda', 'Manager'),
('Kuramoti', 'Staff');

SELECT * FROM users;

shiftsのレコード

INSERT INTO shifts ( shift_date, start_time, end_time, shift_memo)
VALUES
( '2025-01-01', '05:00', '10:00', '朝ピークと昼ピークの準備'),
( '2025-01-01', '10:00', '13:00', '昼ピークに備えてください'),
( '2025-01-01', '13:00', '17:00', '夕方ピークの準備をしてください'),
( '2025-01-01', '17:00', '22:00', '補充や前出しをしっかり行ってください'),
( '2025-01-01', '22:00', '05:00', '掃除をしっかりしてください');

shift_membersのレコード

INSERT INTO shift_members ( usersID, shiftsID)
VALUES
(1, 1),
(2, 1);

各テーブルを結合しよう

参考にした教材

SELECT
s.shiftsID,
s.shift_date,
s.start_time,
s.end_time,
s.shift_memo,
u.user_name,
u.user_role
FROM
shifts s
JOIN
shift_members sm
ON s.shiftsID = sm.shiftsID
JOIN
users u
ON sm.usersID = u.usersID;

応用案