質問: 現在時間単位での予約システムを作っているのですが、そのロジックというか設計が思いつかないです。皆さんの知恵をお借りしたいと思って投稿させていただきました。 テーブル設計は以下のとおりです * ユーザテーブル * シフトテーブル(日毎にレコード作成、シフト開始時間終了時間を値として持つ) * date(シフトの日付) * start(開始時刻) * end(終了時刻) * 予約テーブル(シフトテーブルに1:多として持たせる。何時から何時までの予約が入っているか値として持つ) システムとしては以下の通りです。 ユーザを複数作成し、ここのユーザ毎にシフト時間を決めます。例えば20時~23時のような感じです。 他ユーザで例のユーザシフトの間に予約を入れます(なんの予約かは特に必要ないと思うので省略します)。20~23なので20~22の予約が入ったとします。 ここで、予約情報を管理するため、予約テーブルを使用し誰のどのシフトに対して予約が入ったのかをレコードとして保存します。 ここでまた他のユーザから予約を入れようとした時、20~22の間は予約できますが、すでに22時の予約が入っているので、22~23時までの予約しかできないようになります。この判定は予約テーブルとシフトの時間から抽出できるかと思います。 問題は日付を超える場合だと思います。シフトレコードは日毎に作成しているので、日をまたぐような予約だと、二つのシフトレコードを考慮しないといけないと思います。これだとあまりスマートな手法なのかなというのが引っかかっています。なので日を超える予約は不可にしようと思っています。 ですが24時周辺は予約が入りやすいので、一日の終わり時間を4時ごろにしようと思います。 4時にした場合やはり日は超えてしまうので、見かけ上だけ日を超えさせておいて、裏で指定時間-4といった風に組んでやればなんとか出来るかなと考えています。 説明が下手で申し訳ありません。訂正点などご指摘いただけると訂正いたしますので、回答よろしくお願いします。 --- 2016年1月1日のシフトが25~27時だった場合、シフト上は1日のシフトですが、データとしては2016-01-02 1:00始まりとなると思います。 このような場合、どうやって1月1日のシフトとして認識すれば良いのでしょうか?
回答: > > シフトテーブルは日毎に作成している > > > ここがすべての問題の元凶でしょう。 日毎にテーブルが増えるなんてゆうのは、アンチパターンの代表格です。 date(シフトの日付) start(開始時刻) end(終了時刻) これが、間違いの元。 シフトの日付は不要 datetime型で start, datetime型で end を持っていればすむことでは? これで、イメージできるんじゃないかと思いますが。 データベース定義 ======== スタッフテーブル ``` CREATE TABLE `Staff` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'PK', `name` varchar(32) DEFAULT NULL COMMENT 'スタッフ名', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; ``` | id | name | | --- | --- | | 1 | スタッフ名 | シフトテーブル ``` CREATE TABLE `Shift` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'PK', `staff_id` int(11) DEFAULT NULL COMMENT 'スタッフID', `start` datetime DEFAULT NULL COMMENT '勤務開始日時', `end` datetime DEFAULT NULL COMMENT '勤務終了日時', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; ``` | id | staff_id | start | end | | --- | --- | --- | --- | | 1 | 1 | 2017-01-01 22:00:00 | 2017-01-02 02:00:00 | | 1 | 1 | 2017-01-02 22:00:00 | 2017-01-03 02:00:00 | 予約テーブル ``` CREATE TABLE `Reserv` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'PK', `staff_id` int(11) DEFAULT NULL COMMENT 'スタッフID', `start` datetime DEFAULT NULL COMMENT '開始日時', `end` datetime DEFAULT NULL COMMENT '終了日時', `name` varchar(32) DEFAULT NULL COMMENT '予約氏名', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; ``` | id | staff_id | start | end | name | | --- | --- | --- | --- | --- | | 1 | 1 | 2017-01-01 23:00:00 | 2017-01-02 01:00:00 | お客さん | 以下のコメントに対する回答 ============= > > 取得はそれで大丈夫ですが、表示する場合です。1日は何時から何時まで, 2日は何時から何時まで、という風に。カレンダーのように表示するのもいいですが。 > > 表示する場合は、先ほど行ったように、同じ日にシフトが二つ表示される場合などありませんか? > > > 登録時に1日の25時から26時(UI)で登録したのに、いざ一覧表示を見てみると2日にシフトがある。実質は変わりないのですが、使用するユーザからするとおかしな挙動になりませんか? > > > ``` SELECT st.name AS 'スタッフ名' , DATE_FORMAT(sf.start, '%Y年%m月%d日') AS '勤務日' , TIME_FORMAT(sf.start, '%H:%i') AS '勤務開始時間' , IF( DATEDIFF(sf.end,sf.start)>0 , concat(TIME_FORMAT(sf.end, '%H') + 24 * DATEDIFF(sf.end,sf.start), ':', TIME_FORMAT(sf.end, '%i')) , TIME_FORMAT(sf.end, '%H:%i') ) AS '勤務終了時間' FROM Shift sf INNER JOIN Staff st ON sf.staff_id = st.id ``` !イメージ説明 ついでにおまけ ======= スタッフテーブルに「時給」を追加 ``` CREATE TABLE `Staff` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'PK', `name` varchar(32) DEFAULT NULL COMMENT 'スタッフ名', `salery` int(11) DEFAULT NULL COMMENT '時給', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; ``` 残業時間、深夜勤務時間を算出し、日給を計算 (多分、MySQLでしか動かないだろうけど...) ``` SELECT st.name AS 'スタッフ名' , DATE_FORMAT(sf.start, '%Y年%m月%d日') AS '勤務日' , sf.start AS '勤務開始日時' , sf.end AS '勤務終了日時' , TIME_FORMAT(sf.start, '%H:%i') AS '勤務開始時間' , IF( DATEDIFF(sf.end,sf.start)>0 , concat(TIME_FORMAT(sf.end, '%H') + 24 * DATEDIFF(sf.end,sf.start), ':', TIME_FORMAT(sf.end, '%i')) , TIME_FORMAT(sf.end, '%H:%i') ) AS '勤務終了時間' , @hour:=HOUR(TIMEDIFF(sf.end,sf.start)) + MINUTE(TIMEDIFF(sf.end,sf.start)) / 60 AS '勤務時間(h)' , @exceed:=IF( HOUR(TIMEDIFF(sf.end,sf.start)) + MINUTE(TIMEDIFF(sf.end,sf.start)) / 60 - 8 > 0 , HOUR(TIMEDIFF(sf.end,sf.start)) + MINUTE(TIMEDIFF(sf.end,sf.start)) / 60 - 8 , 0 ) AS '残業時間(h)' , @night:=HOUR( TIMEDIFF( LEAST(sf.end, DATE_FORMAT(ADDDATE(DATE(sf.start), INTERVAL +1 DAY), '%Y-%m-%d 07:00:00')) , GREATEST(sf.start, STR_TO_DATE(CONCAT(DATE(sf.start), '22:00:00'), '%Y-%m-%d %H:%i:%s')) ) ) + MINUTE( TIMEDIFF( LEAST(sf.end, DATE_FORMAT(ADDDATE(DATE(sf.start), INTERVAL +1 DAY), '%Y-%m-%d 07:00:00')) , GREATEST(sf.start, STR_TO_DATE(CONCAT(DATE(sf.start), '22:00:00'), '%Y-%m-%d %H:%i:%s')) ) ) AS '深夜勤務(h)' , st.salery AS '時給' , FLOOR(@hour * st.salery + @exceed * st.salery * 0.25 + @night * st.salery * 0.25) AS '給与' FROM Shift sf INNER JOIN Staff st ON sf.staff_id = st.id ``` !イメージ説明