Suggested method of handling non-overlapping ranges (e.g. scheduling)

Question

I've seen this sort of problems a few times and am trying to decide the best way of storing ranges in a non-overlapping manner. For example, when scheduling some sort of resource that only one person can use at a time. Mostly what I've seen is something like this:

PERSON          ROOM        START_TIME      END_TIME
Col. Mustard    Library     08:00           10:00
Prof. Plum      Library     10:00           12:00

1. What's the best way of preventing new entries from overlapping an existing schedule, like say if Miss Scarlet wants to reserve the library from 11:00 to 11:30? An inline constraint won't work and I don't think this can easily be done in a trigger. A procedure that handles all inserts that initially looks for an existing conflict in the table?

2. Secondly, what's the best way to handle concurrency issues? Say Miss Scarlet wants the library from 13:00 to 15:00 and Mrs. White wants it from 14:00 to 16:00. The procedure from (1) would find both these schedules acceptable, but clearly taken together, they are not. The only thing I can think of is manual locking of the table or some sort of mutex.

3. What's a good primary key for the table above, (room, start_time)?

Answer 1

Fast working way for the cases, where you have fixed time ranges, you can store all ranges in separate table then simply link it to the "reserves" table. It can do the trick for fixed ranges, for example you can reserver library only with 30min interval and working hours is like from 8am to 8pm, just 24 records needed.

--Person table---------------
ID   PERSON         ROOM
1    Col. Mustart   Library
2    Proof. Plum    Library

--Timeshift table------------
ID   START_TIME   END_TIME
1    08:00        08:30
2    08:30        09:00
....
24   19:30        20:00

--Occupy table----
DATE            TIMESHIFT    PERSON
TRUNC(SYSDATE)   TS_ID        P_ID
08/12/2012         4           1
08/12/2012         5           1
08/12/2012         9           2 
08/12/2012         10          2 

Now you make it PK or UK and your database driven check is ready. It will be fast, with little data overhead. However using the same routine for every second will be not that effective.

More universal and complex way is to let some procedure (or trigger) check, is your range occupied or not and you will have to check all current records.