However, conflicts often occur when multiple staff members or systems try to book the same operating room at overlapping times. Left unchecked, these conflicts can cause delays, coordination issues, and even risks to patients.
A reliable solution is to use assertions in the database. With Oracle Database 23c, hospitals can enforce complex scheduling rules directly at the database level, ensuring no two surgeries are scheduled in the same operating room at the same time. This eliminates human error and protects hospital operations.
The Problem: Double Booking Operating Rooms
Surgery scheduling in hospitals typically follows this workflow:
- A surgeon requests a procedure.
- An operating room is assigned.
- Start and end times for the surgery are set.
Even with this workflow, overlapping bookings can happen if the database does not strictly enforce scheduling rules.
Example of a scheduling conflict:
| Room | Surgery | Start Time | End Time |
|---|
| OR-305 | Knee Replacement | 7:00 | 9:00 |
| OR-305 | Hip Replacement | 8:15 | 10:15 |
Here, the surgeries overlap by 45 minutes, which can lead to:
- Delays in procedures
- Staff coordination problems
- Risk to patient safety
- Inefficient use of operating rooms
Relying solely on application logic or manual checks is risky—especially when multiple systems interact with the same database.
Enforcing Scheduling Rules with Assertions
Assertions are database-level rules that guarantee certain conditions are always true. For hospital scheduling, the main rule is:An operating room cannot host more than one surgery at the same time.
This ensures that time intervals for surgeries in the same room never overlap.
Example Data Model
Operating Rooms Table:
SQL> CREATE TABLE operating_rooms(
or_id NUMBER PRIMARY KEY,
or_name VARCHAR2(50),
department VARCHAR2(50)
);
Surgeons Table:
or_id NUMBER PRIMARY KEY,
or_name VARCHAR2(50),
department VARCHAR2(50)
);
Surgeons Table:
SQL> CREATE TABLE surgeons(
surgeon_id NUMBER PRIMARY KEY,
surgeon_name VARCHAR2(100),
surgeon_specialty VARCHAR2(50)
);
Surgery Schedule Table:
SQL> CREATE TABLE surgery_schedule(
surgery_id NUMBER PRIMARY KEY,
surgeon_id NUMBER,
or_id NUMBER,
start_time TIMESTAMP,
end_time TIMESTAMP,
CONSTRAINT fk_surgeon FOREIGN KEY (surgeon_id) REFERENCES surgeons(surgeon_id),
CONSTRAINT fk_room FOREIGN KEY (or_id) REFERENCES operating_rooms(or_id)
);
Without assertions, the database could allow conflicting schedules:
surgery_id NUMBER PRIMARY KEY,
surgeon_id NUMBER,
or_id NUMBER,
start_time TIMESTAMP,
end_time TIMESTAMP,
CONSTRAINT fk_surgeon FOREIGN KEY (surgeon_id) REFERENCES surgeons(surgeon_id),
CONSTRAINT fk_room FOREIGN KEY (or_id) REFERENCES operating_rooms(or_id)
);
Without assertions, the database could allow conflicting schedules:
| Room | Surgery | Start Time | End Time |
|---|---|---|---|
| OR-305 | Knee Replacement | 7:00 | 9:00 |
| OR-305 | Hip Replacement | 8:15 | 10:15 |
How Assertions Detect Conflicts
An assertion prevents overlapping surgeries by checking time intervals:CREATE ASSERTION no_room_schedule_conflict
CHECK (
NOT EXISTS (
SELECT s1.or_id
FROM surgery_schedule s1
JOIN surgery_schedule s2
ON s1.or_id = s2.or_id
AND s1.surgery_id <> s2.surgery_id
WHERE s1.start_time < s2.end_time
AND s2.start_time < s1.end_time
)
);
Logic:
Two surgeries conflict if:
- Surgery A start < Surgery B end
- AND Surgery B start < Surgery A end
If a conflict exists, the assertion fails and the database blocks the transaction, preventing overlapping surgeries.
Example:
Valid Schedule:
| Room | Surgery | Start Time | End Time |
|---|---|---|---|
| OR-402 | Appendectomy | 6:30 | 7:30 |
| OR-402 | Gallbladder Removal | 7:30 | 9:00 |
Conflict Attempt:
| Room | Surgery | Start Time | End Time |
|---|---|---|---|
| OR-402 | Appendectomy | 6:30 | 7:30 |
| OR-402 | Hernia Repair | 7:00 | 8:00 |
Result: ERROR – assertion NO_ROOM_SCHEDULE_CONFLICT violated.
The conflicting schedule is automatically blocked.
Why Hospitals Should Use Assertions
Assertions provide several advantages in hospital scheduling systems:- Prevent Critical Operational Errors – Conflicts are blocked immediately.
- Protect Patient Safety – Eliminates risks caused by overlapping surgeries or delays.
- Ensure Centralized Data Integrity – Works across multiple systems like hospital administration portals, scheduling apps, and emergency systems.
- Simplify System Design – Reduces reliance on triggers, manual checks, or complex application validations.
Assertions can also help manage:
- Surgeon availability – Prevents double booking of surgeons.
- ICU bed allocation – Ensures ICU reservations never exceed capacity.
- Medical equipment scheduling – Avoids conflicts for MRI, X-ray, or CT scanners.
- Staff shift compliance – Keeps nursing shifts within legally allowed hours.
