Geek asks and answers

The proposed method of processing non-overlapping ranges (for example, planning)

I have seen such problems several times and am trying to solve the best way to store ranges in a non-overlapping way. For example, when planning a resource that only one person can use at a time. Basically what I saw looks something like this:

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

  • What is the best way to prevent new recordings from being overwritten on an existing schedule, for example, if Miss Scarlett wants to reserve the library from 11:00 to 11:30? The built-in constraint will not work, and I don’t think it can be done easily in a trigger. A procedure that handles all inserts that initially look for an existing conflict in a table?

  • Secondly, what is the best way to deal with the concurrency problem? Say Miss Scarlet wants a library from 13:00 to 15:00, and Mrs. White wants from 14:00 to 16:00. The procedure from (1) will find both of these graphs acceptable, but clearly taken together, they are not. The only thing I can think of is manually locking the table or some kind of mutexes.

  • What is a good primary key for the table above, (room, start_time)?

+5
source share
1 answer

, , , "". , , 30 , - 8:00 20:00, 24 .

--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

PK UK, . , . .

( ), , .

+4

More articles:


All Articles