予約システムでの時間の設計について

MYSQLで部屋を利用するための予約システムを作ろうと考えております。

設計として下記を考えてみたのですがおかしいでしょうか。

要件は下記のとおりです。
・A、B、C３つの部屋の利用予約を時間単位でおこなう
・重複する時間は予約できない
・日を跨ぐ予約はできない

テーブル設計
トランID（int）｜開始時刻(datetime)｜終了時刻(datetime)｜部屋ID(int)

時間についてはdatetime式で2013-03-22　12：00：00　と保存させる予定です。

入力を簡素にするため、時刻は時、分個別のプルダウンで30分刻みにしようと考えています。

そこで分からなくなってきたのがデータ保持の方法なのですが、
例えば10：00－12：00と予約入力された場合、datetime式ですと秒まで保存されますので
2012-03-22 10:00:00 - 2012-03-22 12:00:00 となりますよね。

そうなりますと、別の予約で12：00-15：00を確保したい場合、開始時刻は重複してしまうことに
なりますので、テーブルには2013-03-22 10:00:01 - 2013-03-22 11:59:59と登録したほうが
いいのでしょうか。
ただ、これだと時間を変更したい場合の修正の際に困ったことになりそうです。

またこの設計の場合、　開始時刻と終了時刻が他の予約と重複していないかのSQLを
考える場合、非常にやっかいなような気がしてきました。

何か別のよい設計のヒントがあればご教授願えないでしょうか。

宜しくお願いいたします。

No.5 ベストアンサー 回答者： Siegrune 回答日時： 2013/03/23 00:30

普通（かどうかは知りませんが）こういう要件のときは、

部屋ID=A
で
'2013-03-22 11:00:00'
から
'2013-03-22 15:00:00'
と画面で入力されたとすると、

SELECT count(*) FROM 予約テーブル
WHERE 部屋ID=A
AND 開始時刻<'2013-03-22 15:00:00'
AND 終了時刻>'2013-03-22 11:00:00'

の結果が0でなければ重複しているとしてエラーにします。
00:00は含まれないので、データの持ち方は、00分00秒でＯＫです。

なにをやっているかの補足：
AND 開始時刻<'2013-03-22 15:00:00'
は、開始時刻が入力した終了時刻以降のものは関係ありません。
（今回の予約時間が終わってから始まります。）
AND 終了時刻>'2013-03-22 11:00:00'
は、終了時刻が入力した開始時刻以前のものは関係ありません。
（今回の予約時間が始まる前に終わってます。）
⇒上記以外とは、今回の予約時間が終わる前に始まり（今回の予約開始前か予約開始後かは問わない）
今回の予約時間が始まる前には終わっていない（今回の予約終了後か予約終了前かは問わない）
予約が既にあるということを調べています。

この回答へのお礼

ありがとうございます！

なるほど、そういう発想で検索すればよかったのですね。
目からウロコです。
確かにそれでいけます！

自身の論理思考の足りなさが恥ずかしい限りです・・。

お礼日時：2013/03/23 01:13

No.4 回答者： OKWavex 回答日時： 2013/03/22 23:55

さらに指定時刻（開始）と指定時刻（終了）の間に含まれる開始終了時刻も対象にする必要があるでしょう

この回答へのお礼

何度もありがとうございます！

ご指摘のとおり、BETWEENですと確かにそのようになってしまいました・・。
秒を00でなく30と入れてしまえばBETWEENでも何とかなりそうですが・・。

だんだん頭が混乱してきました！

お礼日時：2013/03/23 01:09

No.3 回答者： OKWavex 回答日時： 2013/03/22 23:36

BETWEENでは常に以上・以下での比較になってしまい、入力開始時刻が終了時刻に一致するだけのものも対象になってしまうのでは？

それよりも、開始時刻同士の一致のみと終了時刻同士の一致のみを条件に含めば開始・終了が両方一致のものも含まれます
～（指定時刻（開始）が開始時刻以上で終了時刻より小） OR （指定時刻（終了）が開始時刻より大で終了時刻以下）

No.2 回答者： OKWavex 回答日時： 2013/03/22 22:37

＞１．２．の問い合わせで件数が０でしたら重複なしと判断できるのですが、

＞いちいち2回の検索をおこなうのもどうなのでしょうか。

ORでつないで１回で検索すれば？
～指定時刻（開始）が開始時刻と終了時刻の間 OR 指定時刻（終了）が開始時刻と終了時刻の間

この回答へのお礼

ふたたびありがとうございます。

なるほど、ORを使えばよかったんですね。

しかし、先ほどのSQLですと別の予約を完全に跨いでしまう時間が入力された場合にはヒットしないことに気づきました。
そこで、ORとbetweenを使ってまた考えてみました。

SELECT * FROM 予約テーブル WHERE 部屋ID=A AND ( 開始日時 BETWEEN '2013-03-22 11:00:00' AND '2013-03-22 15:00:00' OR 終了日時 BETWEEN '2013-03-22 11:00:00' AND '2013-03-22 15:00:00')

理論的にはこれで合っていますでしょうか。

お礼日時：2013/03/22 23:11

No.1 回答者： OKWavex 回答日時： 2013/03/22 22:10

開始時刻のほうの秒だけを常に00秒より大きな値にして設定すれば？

たとえば常に30秒に設定すれば10：00－12：00と12：00-15：00は10：00：30－12：00：00と12：00：30-15：00：00なので重複しませんし、設定時間の変更時に参照しても分単位での値は元の指定値と同じです
あるいは重複確認を値の一致ではなく大小比較で行うことで、時間帯の重複しない開始・終了時刻の一致は重複とみなさないことも可能

この回答へのお礼

ありがとうございます！
秒についてはそのようにすれば、確かに入力時の分にも影響ありませんね。

重複確認についてはお教えいただいた大小比較で2回のselect分を発行する方法を考えました。

１．入力された開始時刻が重複していないか
SELECT * FROM 予約テーブル WHERE 部屋ID=A AND (開始時刻<'2013-03-22 11:00:00' AND 終了時刻>'2013-03-22 11:00:00')

２．入力された終了時刻が重複していないか
SELECT * FROM 予約テーブル WHERE 部屋ID=A AND (開始時刻<'2013-03-22 15:00:00' AND 終了時刻>'2013-03-22 15:00:00')

１．２．の問い合わせで件数が０でしたら重複なしと判断できるのですが、
いちいち2回の検索をおこなうのもどうなのでしょうか。

お礼日時：2013/03/22 22:24