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

前回のDB設計

newDBスキーマ

修正① エンティティ

✅users
usersID [ PK ]
user_name [ NOT NULL ]

→ 役職(role)は、わざわざ表示する意味ない

✅shifts
shiftsID [ PK ]
shift_day [ NOT NULL ] DATE
shift_time [ NOT NULL ]

UNIQUE [ shift_day, shift_time ]

→ 曜日、時間を分かりやすくシンプルにする

✅shift_members
shiftsID [ NOT NULL ]
usersID [ NOT NULL ]

UNIQUE [ shiftsID, usersID ]

修正② ER図

仮

修正③SQLを日本語で説明する

①主テーブル : shifts
②絞り込み条件 : shift_dayが「週の開始日(月)〜終了日(日)」の範囲にあるもの
③結合１ : shift_memberを結合し、usersIDを取得
④結合２ : usersを結合しuser_nameを表示する
⑤並び順

MySQLを書く

DBを作成

CREATE DATABASE shift_management;
USE shift_management;

shiftsテーブルの作成

CREATE TABLE shifts (
shiftsID INT AUTO_INCREMENT,
shift_day DATE NOT NULL,
shift_time VARCHAR(11) NOT NULL,
PRIMARY KEY (shiftsID),
UNIQUE(shift_day, shift_time)
);

usersテーブルの作成

CREATE TABLE users (
usersID INT AUTO_INCREMENT,
user_name 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)
);

レコードを挿入する

shiftsのレコード

INSERT INTO shifts ( shift_day, shift_time) VALUES
('2026-01-01', '05:00-10:00'),
('2026-01-01', '10:00-13:00'),
('2026-01-01', '13:00-17:00'),
('2026-01-01', '17:00-22:00'),
('2026-01-01', '22:00-05:00');

SELECT * FROM shifts;

usersのレコード

INSERT INTO users ( user_name ) VALUES
( 'Matsuda'),
('Otsuka');

SELECT * FROM users;

shift_membersのレコード

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

SELECT * FROM shift_members;

結合

SELECT
s.shiftsID,
s.shift_day,
s.shift_time,
u.user_name
FROM
shifts s
JOIN
shift_members sm
ON s.shiftsID = sm.shiftsID
JOIN
users u
ON sm.usersID = u.usersID;

---

user_nameを1行にまとめたい

SELECT
s.shiftsID,
s.shift_day,
s.shift_time,
GROUP_CONCAT( u.user_name ORDER BY u.user_name SEPARATOR ',') AS members
FROM
shifts s
JOIN
shift_members sm
ON s.shiftsID = sm.shiftsID
JOIN
users u
ON sm.usersID = u.usersID
GROUP BY shiftsID, shift_day, shift_time;

---

新しい知識

GROUP BY

「同じシフト枠」シフト単位で1グループにまとめる

GROUP BY shiftsID, shift_day, shift_time;

---

GROUP_CONCAT

同じグループ内の値を繋げる

GROUP_CONCAT( u.user_name ORDER BY u.user_name SEPARATOR ',') AS members

※本来ならuser_nameに名前が表示される

---

ORDER BY

名前をアルファベット順に並べる

ORDER BY u.user_name

---

SEPARATOR

名前の区切りを','にする

SEPARATOR ','
SEPARATOR '/'

---

AS members

カラム名を「members」にする

AS members