建议的处理非重叠范围的方法(例如，安排时间) [英] Suggested method of handling non-overlapping ranges (e.g. scheduling)
问题描述
我已经看过几次此类问题，并且正在尝试确定以不重叠的方式存储范围的最佳方法.例如，在安排某种资源时，一次只能一个人使用.通常我所看到的是这样的:
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
-
防止新条目与现有时间表重叠的最佳方法是什么，比如说Scarlet小姐是否想在11:00至11:30保留图书馆?内联约束不起作用，而且我认为这很难在触发器中完成.一个处理所有最初在表中查找现有冲突的插入的过程?
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?
第二，处理并发问题的最佳方法是什么?假设Scarlet小姐想要从13:00到15:00图书馆，而White太太则想要从14:00到16:00.(1)中的过程将发现这两个时间表都是可以接受的，但显然将它们合在一起是不可接受的.我唯一能想到的就是手动锁定表或某种互斥锁.
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.
上面的表(房间，开始时间)有什么好的主键?
What's a good primary key for the table above, (room, start_time)?
推荐答案
在固定时间范围的情况下，快速的工作方式是，您可以将所有范围存储在单独的表中，然后将其链接到"reserves"表.它可以解决固定范围的问题，例如，您只能以30分钟的间隔重新存储库，工作时间从上午8点到晚上8点，仅需要24条记录.
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
现在将其设置为PK或UK，并且数据库驱动的检查已准备就绪.它将很快，几乎没有数据开销.但是，每秒使用相同的例程并不会有效.
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.
这篇关于建议的处理非重叠范围的方法(例如，安排时间)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
