Greetings from London Gatwick Airport. Messed-up app of the day: National Express self-service ticketing kiosk, London Heathrow Airport, Terminal 5.
I'm connecting through London on my way home. Unfortunately, my connection requires a bus ride from Heathrow (LHR) to Gatwick (LGW). Not my favorite, but I've done it before and survived. Bus is run by National Express. It's a £19 ticket; apparently only £17 if you're doing the BA/AA ugly connection thing I'm doing. I don't remember having to pay that myself before, but today I'm glad I did.
One way to buy your ticket is through one of the self-service ticket machines outside baggage claim. Here's approximately how that went:
Machine: Where will you be going?
Me: London Gatwick.
Then the machine I was doing business with popped a widget that showed the number of qualifying journeys the machine had found so far. It's one of those "watch the number change really fast" fields. I watched it count through 10, and then 50, and then 100 (jaw drop), and then eventually 500, until finally, about a minute later, the counter reached roughly 2,000. Finally, it said, "2,000 qualifying journeys found", or something to that effect.
Then it showed me the first half dozen or so of those "qualifying journeys."
- 11:10 Heathrow to Gatwick
- 11:20 Heathrow to Gatwick
- 11:30 Heathrow to Gatwick
- ...
It was 10:55 at the time. I chose 11:10.
"Brilliant."
Let me summarize. This thing computes the schedule for each of the next 2,000 bus rides you might want to take. You only look at six of them. The cost to you for all this information you don't look at is (N+1)*60 seconds of your time, where N is the number of people ahead of you in the queue for this machine. If there are six or more people ahead of you in your queue, then you're probably going to miss the next bus.
I don't know whether it's an Oracle application behind this or not. It doesn't matter. It's stupid either way.
There's a simple solution. Begin by asking approximately when the user wants his bus ride. The default value should be "now." I don't know what percentage of people would ever want to change that value, but I doubt I ever would. Then instead of returning 2,000 qualifying journeys sorted by departure time, return only journeys whose departure time is within ±1 hour of his choice. Of course, list only those departure times that occur in the future.
SQL like this would do it:
select departure_time, origin, destination, ...
from whatever1, whatever2, ...
where origin = :here
and destination = :there
and departure_time between :your_time - 1/24 and :your_time + 1/24
and departure_time > sysdate
order by 1
Okay, that's functionally dirty because what happens if someone specifies a :your_time value of 3am, and there are no buses scheduled anywhere within 2am-4am?
Then be clever and use analytic functions to find the 10 rows that have departure_time that are closest to :your_time. Be a developer.
The functional difference we're trying to achieve is to stop burning computing and communication resources producing 1,990 rows that nobody wants to see anyway. The benefit? Reduction of human suffering in situations wherein frustrated people who are trying to make their connections are missing buses because a machine that takes their money is spending time making calculations nobody needs.
