時間単位での予約システムのロジックが思いつかない

Question

現在時間単位での予約システムを作っているのですが、そのロジックというか設計が思いつかないです。皆さんの知恵をお借りしたいと思って投稿させていただきました。

テーブル設計は以下のとおりです

• ユーザテーブル
• シフトテーブル(日毎にレコード作成、シフト開始時間終了時間を値として持つ）
• date(シフトの日付)
• start(開始時刻)
• end(終了時刻)
• 予約テーブル(シフトテーブルに1:多として持たせる。何時から何時までの予約が入っているか値として持つ)

システムとしては以下の通りです。

ユーザを複数作成し、ここのユーザ毎にシフト時間を決めます。例えば20時23時のような感じです。
他ユーザで例のユーザシフトの間に予約を入れます（なんの予約かは特に必要ないと思うので省略します）。2023なので20~22の予約が入ったとします。

ここで、予約情報を管理するため、予約テーブルを使用し誰のどのシフトに対して予約が入ったのかをレコードとして保存します。

ここでまた他のユーザから予約を入れようとした時、2022の間は予約できますが、すでに22時の予約が入っているので、2223時までの予約しかできないようになります。この判定は予約テーブルとシフトの時間から抽出できるかと思います。

問題は日付を超える場合だと思います。シフトレコードは日毎に作成しているので、日をまたぐような予約だと、二つのシフトレコードを考慮しないといけないと思います。これだとあまりスマートな手法なのかなというのが引っかかっています。なので日を超える予約は不可にしようと思っています。

ですが24時周辺は予約が入りやすいので、一日の終わり時間を4時ごろにしようと思います。
4時にした場合やはり日は超えてしまうので、見かけ上だけ日を超えさせておいて、裏で指定時間-4といった風に組んでやればなんとか出来るかなと考えています。

説明が下手で申し訳ありません。訂正点などご指摘いただけると訂正いたしますので、回答よろしくお願いします。

---

2016年1月1日のシフトが25~27時だった場合、シフト上は1日のシフトですが、データとしては2016-01-02 1:00始まりとなると思います。
このような場合、どうやって1月1日のシフトとして認識すれば良いのでしょうか？

Answer 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

Comments

[kopio]

>シフトテーブル(日毎にレコード作成、シフト開始時間終了時間を値として持つ）
>シフトテーブルは日毎に作成しているので、日をまたぐような予約だと、二つのシフトレコードを考慮しないといけないと思います。

この2文を見る限り、シフトテーブルのレコードを日単位で作成しているとしか読み取れません。

[退会済みユーザー]

シフトレコードの間違いです。申し訳ないです。

[退会済みユーザー]

テーブルの定義を提示するのが解決への近道だと思うんですけどねー

[退会済みユーザー]

ご指摘ありがとうございます。シフトテーブルの項目を追加させていただきました。

[退会済みユーザー]

構造を提示するなら、CREATE TABLE ... を提示する方が間違いがないですよ。

[退会済みユーザー]

start,endに関しては納得する部分もありますが、新たな疑問が生まれたので、追記しておきました

[退会済みユーザー]

どこが追記部分？

[退会済みユーザー]

> 2016年1月1日のシフトが25~27時だった場合、シフト上は1日のシフトですが、
> データとしては2016-01-02 1:00始まりとなると思います。
> このような場合、どうやって1月1日のシフトとして認識すれば良いのでしょうか？

start の日付固定でいいと思いますけど。

[退会済みユーザー]

どうやらrubyではDateクラスでの25時表記ができないようです。

[退会済みユーザー]

意図が伝わっていないようですね…。

[退会済みユーザー]

どういう意図でしょう？
日付固定というのは2016-01-02という風にせず、2016-01-01としてデータベースに保存するということではないのでしょうか？

[退会済みユーザー]

詳しく回答に追記するので待ってください。

[退会済みユーザー]

詳しい回答ありがとうございます。イメージしやすく分かりやすいのですが、この例だと、開始時間が1日を超えてないですよね。もし超えた場合は2日のデータと成ると思います。2日と表記するのであれば、このデータが1日のものであるとどういう風に判断すればよいのでしょうか？

[退会済みユーザー]

質問の意味がわからないのですが。一体何がわからないのでしょう？
start が 1/1 23時　end が 1/2 1時になっているじゃないですか。
そこをなんで、25時と入力する必要があるのでしょう？

[退会済みユーザー]

startが1/1 25時 endが1/1 26時とした場合、これは1日分のシフトですが、保存されるデータは1/2 1時 1/2 2時となり、2日のシフトのようになってしまうと思います。 2日のシフトは別にあり、20時-21時だと仮定します。そうすると2日のシフトが2つあることになりませんか？

例えば一ヶ月のシフトを一覧で表示する時など、2日の欄にシフトが二つあり、1日は空白に成ると思います。本来であれば1日の欄に25時-26時という風に入れたいのですが。

[退会済みユーザー]

シフトテーブルのデータを確認して
「 2日のシフトは別にあり」この前提が間違っている。

[退会済みユーザー]

「startが1/1 25時 endが1/1 26時とした場合」なんでこうなる？
「startが1/2 1時 endが1/2 2時」で入力しなきゃいかんでしょ

[退会済みユーザー]

シフトテーブルのid(1)は1日分のシフト、id(2)だと思いますが、これは2日分のシフト
id(3)を作った時、25時-26時だったら3日のシフトではなく4日のシフトとして保存するということですよね。システムの意図としては3日のシフトとしたいのですが、4日のシフトとなってしまいます。

ここに引っかかっているのですが。

[退会済みユーザー]

違います。
なんか変な思い込みが邪魔しているような気がする…
普通にカレンダー通り、時計どうりの時間で入力するだけのことでしょ。

[退会済みユーザー]

では月のシフトを日毎に一覧で表示する場合など、どのように表示されると考えていますか？

[退会済みユーザー]

select * from Shift where YEAR(start) = 2017 and MONTH(start) = 1;
これで取れる

[退会済みユーザー]

取得はそれで大丈夫ですが、表示する場合です。1日は何時から何時まで, 2日は何時から何時まで、という風に。カレンダーのように表示するのもいいですが。
表示する場合は、先ほど行ったように、同じ日にシフトが二つ表示される場合などありませんか？

登録時に1日の25時から26時（UI）で登録したのに、いざ一覧表示を見てみると2日にシフトがある。実質は変わりないのですが、使用するユーザからするとおかしな挙動になりませんか？

[退会済みユーザー]

上記のSQLを25時〜26時ですると、勤務日は2017-1-2になりませんか？

[退会済みユーザー]

「SQLを25時〜26時でする」←意味不明です。
何を懸念しているのか、具体的に伝える努力をしてくださいな。

[退会済みユーザー]

シフト開始時刻が1月1日の25時の時、見かけ上は1月1日ですが、実際にDBに保存されるデータは1月2日の1時です。

この時、開始時刻の日付から、勤務日を求めると1月2日が取られます。ここが懸念しているところで、実際とって欲しい勤務日は1月1日です。

[退会済みユーザー]

「この時、開始時刻の日付から、勤務日を求めると1月2日が取られます。ここが懸念しているところで、実際とって欲しい勤務日は1月1日です。」
↑
これって、単純に特定の会社でのローカルルールでしょ？
じゃあ、一体、何時までは前日扱いにするのかという取り決めを回答者と共有せずに、そんなこと言われても、話通じないでしょ。

[退会済みユーザー]

確かにその通りですね。
前日扱いは28時までとした場合、やはり勤務日というフィールドは必要なのではないでしょうか？

開始時刻からも計算してもとめられますが、画面に一覧表示する場合などに毎回計算するのは無駄な気がします。

[退会済みユーザー]

あってもいいし、なくても問題ない。28時ということは、勤務開始時間から4時間減算し、日付部分を取り出せば、勤務日は取得できる。

[退会済みユーザー]

DB的には負荷がかかりにくいのは、勤務日をフィールドとして持つ方だと思うのですがどうでしょうか？
4時間減算とは言っても、24時を超えているという条件分岐をさせる必要があると思います。
これを、全てのレコードに対して行うのは非効率かなと感じています。なので、最初に計算し勤務日として持つ方法が良いと思います。ご意見伺いたいです。

[退会済みユーザー]

「推測より、計測せよ」

[退会済みユーザー]

> 4時間減算とは言っても、24時を超えているという条件分岐をさせる必要があると思います。
根拠ある？

[退会済みユーザー]

意見だけお聞かせください。

[退会済みユーザー]

自分ならSQLで処理する。

会社テーブルに締め時間カラムをもって、SQL側で処理し、ソースコードのメンテナンス性を優先します。

[退会済みユーザー]

その理由はなぜでしょう？

[退会済みユーザー]

ソースコードの保守性優先

[退会済みユーザー]

ありがとうございます。
とても参考になりました。

長い間お付き合いありがとうございました！

[退会済みユーザー]

お疲れさんでした。

Answer 2

シフトテーブルは日毎に作成しているので、

無駄に処理がややこしくなるのでやめた方が良いです。ご質問を読む限り、日毎にテーブルを分ける必然性はないように思います。

単純に一つのテーブルに日付時刻形式で管理すれば何の問題もないと思います。Rubyは使ったことがないのでよく判らないのですが、たぶん日付時刻形式のデータ型は用意されているでしょうし、DBMSでも同様のデータが扱えます。そうすれば、朝9時出社～翌日夜10時退社、などというブラック勤務にも対応できます（対応してはいけません……）。
UI等に表示する際は、開始日時に対して日付のみを指定（あるいは当該日付の00:00～23:59までと指定）して検索すれば、指定した日付から開始するシフトのレコードを抽出できます。

Comments

[退会済みユーザー]

すいません。テーブルではなくレコードの間違いです。

[catsforepaw]

前提が変わってしまいますね……。とはいえ、やはり同じことです。日付時刻形式でシフト時間を管理すれば、日またぎなどの問題は起こりません。

Answer 3

シフトテーブルは日毎に作成している

何も得しないので、私も止めた方がいいと思います。

---

日を超える予約は不可

それでは、ユーザ側から見て使いにくいシステムです。
これがたとえば宿泊などの場合、長期予約のお客を逃して、ビジネス的にも痛いです。

---

一日の終わり時間を4時ごろにしようと思います。
4時にした場合やはり日は超えてしまうので、見かけ上だけ日を超えさせておいて、
裏で指定時間-4といった風に組んでやればなんとか出来るかな

そうした独自の約束事を作ってしまうと、「年末は年をまたいでしまうので～」
などとルールがルールを呼び、「月割引の追加」など仕様変更のたびに複雑化します。
属人性が高く、長期間のメンテがしづらいシステムになってしまいそうです。

---

• class Time (Ruby)
• class Date (Ruby)
• class DateTime (Ruby)

じゃあどうすればいいのかというと、（Railsでの拡張もありますが）
Rubyの時刻系ライブラリを使うと、時刻や日付を容易に扱えます。

23時に4時間足すと翌日の3時とか、日またぎの演算も使いこなせばスマートに実現できます。
これを使い、「24時＝0時で翌日に変わる」など、常識的な時刻の概念と一致させます。
また、予約期間は開始日時と終了日時で決める、というのがシンプルでしょう。

そして、この組み込みライブラリをラップした日付APIを作り、
予約ロジックのレイヤーやDBアクセスのレイヤーから分離すると、
テストも変更もしやすいし、分かりやすい設計になると私は思います。

Answer 4

「4時」を「28時」として扱えば解決しそうですが。
日またぎも「23~25時」の様に処理できる。

Comments

[退会済みユーザー]

回答ありがとうございます。
例えばシフトを23~26時にする場合など、シフトレコード二つのデータを記述しないといけないのではないでしょうか？当日のシフトレコードに23~24時、翌日のシフトレコードに0~2時のような感じで。

[swordone]

1日を24時までと決めればそうでしょうが、どう考えてもそれはこの状況においては不都合なわけですよね？だから1日の終わりを4時にするなら4時~28時という時間管理の仕方も十分考えられると思いますが。

Answer 5

シフトテーブル(日毎にレコード作成、シフト開始時間終了時間を値として持つ）

問題の根幹はここにあるように思います。
シフトテーブルが1レコード＝1日となっている以上、どこかで「1日の区切り」は発生します。
例え1日の終りを4時に設定したとしても、「4時またぐ予約」が発生した場合にはやはり同様の問題に直面します。あくまで多いか少ないか、発生する可能性が高いか低いかの違い過ぎません。

解決策としては思いつくのは2つ。

• シフトレコード及び予約レコードを、日をまたぐ場合は2つに分割

※この場合、同日のレコードが2つ以上発生することを念頭に置いて設計する必要があります。

• 「日付」「開始時刻」「終了時刻」という形式で持っているのであれば、日付と時刻を統合して「開始日時」「終了日時」の形式で持つ

個人的には、「1つのシフト」「1つの予約」が1レコードとなる後者が好みです。