Since there has been some, er, ah, "discussion" about the C&TS reservations system here, I thought you might like an update.
First, a bit of history. In 1993 or so Kyle Railways implemented a reservations system programmed in FoxPro or Clipper (not sure which - the point is it was a text-based system) for the C&TS. Prior to implementing it, the C&TS had used a general-seating system which simply tracked the tickets sold to control capacity. They had been having trouble with large numbers of no-shows, and saw this new system as a way to improve the load factor on the trains. It also integrated with Kyle's back-office systems in Kansas for accounting, etc.
George Bartholomew purchased this system from Kyle in 1996, and had it converted to operate as a stand-alone database. When Mr. Bartholomew vacated the premises in 1999, he took the system with him. Even if it had been left on the property, I doubt we would have used it. (Those of you not interested in geek talk should skip the next three paragraphs.)
The Kyle / Bartholomew system was a flat table that had a table record pre-built for each seat for each day of the operating season. In other words, there were over 100,000 records in a single database (all empty except for the seat designation), ready for populating by a ticket agent with a customer's information and reservation details, when the season started in May. That's OK, but it meant that if the customer was buying 10 tickets, his name, address, credit card, etc., had to be entered ten times. And if additional cars were needed on a train, their records had to be created manually before an agent could put a customer in that car.
It restricted certain itineraries (i.e. Antonito to Chama, bus return) to certain cars. So if there were only 6 of those tickets sold on a certain day, the other 38 seats might be empty. (This is obviously not the best way to assign seats when you drag the cars up a 4% grade - you want to fill every seat you can, and carry only the cars you need.)
It was also running on DOS, on a seven-year-old (at least) computer. Putting the program onto a Windows 98 computer would have been challenging, to say the least.
Since we had to start from scratch, we completely rethought the whole process. First, the database should be relational - that is, if a customer buys five tickets for his family and friends to ride the C&TS ten times each year, five years from there there will be fifty reservations records, two hundred and fifty seat assignment records, and one customer record. (Why keep 250 copies of the customer's address, or 250 copies of his credit card number?) Second, it should automatically "add" cars as additional capacity is needed. Third, it should allow for us to move, for example, a green coach from Chama to Antonito for a few days if Antonito needs seats. (This will allow us to use the boxcar coaches much less often.)
It goes without saying it had to handle the twelve different trips we offer, and assign tickets to seats in the Antonito or Chama train as needed in the most efficient way (roughly, front to back unless you're getting on or off at Cumbres). It also had to ensure that the buses weren't overbooked.
It also had to interface with our accounting systems to accrue revenue. And we wanted it to allow us to automatically print and mail out or file pre-purchased tickets, to shorten the lines in the depots at train time. (This had an impact on office procedures, which brought in additional training requirements.) We also wanted to be able to use the resulting data to determine how far in advance people buy tickets, which will affect our 2001 advertising campaign. There were other considerations, but you get the idea - we needed much more than a seat-assigning and ticket-printing system.
Originally, my hope was that we could make the whole system web-based. That way the agents could simply go online and do their thing - and browsers could automatically purchase tickets (and assign their own seats) as well. But the telecoms and power infrastructure in Chama isn't robust enough to make that feasible. So we settled on using Microsoft Access as our platform.
Most of it went together pretty quickly and easily - I was able to get enough of it done in my spare time in late February and early March so that our first ticket agent started using it in early March. (Contrary to one posting made here, I haven't charged RGRPC one penny for my work on the system, and I don't plan to.)
But I'm not a programmer. And because of our desire not to spend all our startup money before we ran our first train, we decided to hold off hiring a programmer to automate some of the manual processes until after opening day. This had an obvious negative effect on seat assignments, which (as you can see from the above discussion) can get very complicated. As soon as we were comfortable with committing to a contract, I sent the spec to a good Visual Basic programmer. He delivered the coding in record time, but we spent a week tackling version issues inherent in programming on one system and operating on another.
Once we got those solved (after a few 14-hour days in the Chama depot), we found out that the new seat assignments page was allowing double-booking of the seats. It took a few more days to get to the root cause of the problem.
We loaded the latest and greatest version yesterday after the trains left and tested it all day. The results look good - when I dialled into the database today, I couldn't find a single duplicate record that had been generated by the new page, so I think we've got it this time. (If not, we'll keep working on it.) The ticket agents are now in the process of correcting the duplications made prior to today, most of which occur in the next week or so.
If you had problems getting your tickets this spring, or found someone sitting in your lap when you rode the train, my apologies. What appears from the outside to be one single, annoying breakdown, was actually two different problems (one brought on by our financial decision) that gave the same result - seat assignment problems.
I've put up some "screen shots" of the system at the link below for those of you interested in seeing the system. (I'm going to take it down on Friday, so look quick - no point in giving hackers a roadmap.)
In the future we hope to integrate the system with a card-swipe keyboard and card verification program, to speed up our ticket purchases and cut our card processing costs (banks charge you more to key in a card number than to swipe it in, if you can believe that), and a number of other small things. There is also some talk of it being able to wash the depot windows, but that's probably next season.
JAC
