trigger to solve these woes or what?

We've an application at work that has a number of problems with duplicate/overlapped reservations for the same site. This was written by an outside vendor, and now we're attempting to clean up their mess. We'll call this a "double-booking issue".

The design of the application is to allow the public to reserve facilities at our state parks (in PA). Each facility,ie. a campsite, cabin, private dock, etc, has a date range of availability. We call that a season. A season has an open and close date. So, each site has a season. Once the seasons are established for all sites, the dba runs a utility called populatedates. This places an entry into a table for EVERY SITE, for EVERY DAY that it is available. The table is very large, and we remove old data out of it when it gets over 2 months old.

What makes the table even larger is that some sites allow multiple reservations per day: ie. WhiteWater Boating. This has 8 timeslots a day, and up to 60 people per slot. So, if the season is from may through september, you can 7*60 * # days in season rows in this table! WOW!!

Anyway, each time a reservation is entered in the system, an entry is created into another table called: reservations. The reservation has an arrival date and a departure date and # of people in the party. The corresponding rows in the "availability dates" table have a flag that now contains the reservation# to show that they are "booked".

Unfortunately, the brilliant programmers used this method to check whether a site was open:

select count(*) into Kount
from available_dates
where site_id = my_site
and reservable_date between arrival_date and departure_date and reservation_id is null;
if kount = 0 then

   update available_dates
   set reservation_id = my_res_id
    where site_id = my_site
    and reservable_date between arrival_date and departure_date     and reservation_id is null;
end if;

• there's other code that differentiates for the # of people in the party based on the site type. The problem occurs in that two users can attempt to grab the same site. The one simply WAITS on the other. When the 1st person commits, the second one then overwrites his work. OOPS! This isn't good.

I was tempted to write some select statements with a for update on the end and if I couldn't get the rows that I wanted, then I would think someone else is trying to do something with the rows that I want. BUT, there are SEVERAL avenues in the system that allow this activity. It's going to take a while to cleanup the number of screens that are "miscoded".

So....
1. Is this "availability dates" table really needed? (It's used throughout the entire application now which consists of over 100 screens and 45+ reports, so it will be difficult to remove in the NEAR FUTURE).

2. Should I attempt to encapsulate all calls that reference this table into a package and make that the interface to this "cumbersome" table?

3. Should I also use the "select for update" to initially make sure the site is available for all days in question?

4. Should I make use of some form of triggers? (not sure what without preventing a mutating table) that I would capture the site#, start, end and # of people, then after the statement is processed, check for an overlapping reservation in the system, or check that the # of people in the party matches the # of rows "reserved/flagged" in the database?

Any suggestions are appreciated!! This is one of the bigger problems in this system, and I'd like to get it fixed ASAP. Right now, my approach is #2 and #3.

I was somehow thinking that #1 combined with #4 would also work, possibly still using a package though. Received on Sat Jan 13 2001 - 22:36:25 CST