How A DB Pulled A Company Together!
Five years ago, when he took a chance and started his own business booking rooms for drivers and their crews at auto-racing events, Donald Douglass knew he had a winning idea. But like many home-based entrepreneurs, he faced a formidable task: organizing a mammoth amount of information. As an added twist, the software he chose to accomplish this task had to be easy to set up and run, because Douglass had little computer know-how.
As a driver and official on the International Motor Sports Association (IMSA) circuit, Douglass learned about the lodging problem firsthand. He was acutely aware of the chaos created by the scramble for the few decent rooms near major racetracks, especially in rural areas with little other tourism, like Sebring, Florida, and Watkins Glen in upstate New York.
“Travel was an awful mess,” he explains. “So after a couple of years, I decided I’d try to make a business out of it by bringing some order to it.”
He culled a list of small hotels and inns that most travel agencies didn’t have the time to ferret out. The hotels were happy to give discounts on blocks of rooms if Douglass would handle the continual revisions that racing crews always seem to require. So he was able to give teams not only a wider choice of rooms closer to the tracks than general travel agencies but also lower prices.
Douglass arranged to hook into the airline computer system SABRE by modem, so he could book air travel and rental cars for his clients from his home office in rural western Massachusetts. He uses a correspondent travel agency in Florida to write the tickets. (The SABRE system signals the agency by computer when tickets need to be written.)
The fledgling business, Travel Torque, Inc., took off in a flash. From a gross of about $50,000 in 1986, its first year, Torque topped $1 million last year.
DIVIDE AND CONQUER, THEN LINK
Torque Travel managed this rapid growth without collapsing under the strain because Douglass was able to juggle a lot of data, including races, hotels, teams, credit-card numbers, contacts, number and types of rooms booked, payments owed the hotels, and more, using two IBM-compatible 386 computers, a modem, a fax machine, and a database program called Professional File (Software Publishing Corp.).
He chose the software because it was easy to use. As he began to design his system for organizing data, he saw that he could keep the information manageable by breaking it down into small files, and then using the lookup function to share information among them. This function, common to many flat-file database programs, lets him look up and draw in data from one file while he’s working within another.
Douglass quickly realized that he could reap four major benefits by using lookups:
1. He didn’t need to go to a more complicated (and more expensive) relational database to link information.
2. He could sort, retrieve, and update more rapidly, since files could be smaller.
3. He could save time and increase accuracy by not having to retype the same information, such as names, addresses, and credit-card numbers.
4. He could use database forms to fill out customer invoices and fax covers at the touch of a key.
Taking advantage of these benefits, Douglass created a well-integrated group of nine separate files that share information and save him lots of typing and cross-checking time.
Although Travel Torque’s database task involves matching hotel rooms with race teams, the general principle of dividing data on customers and inventory (or other information) into separate files then pulling some of that data into a third file for orders is one that can be applied to almost any business. Here’s how Douglass tailored the divide-and-conquer-then-link concept to his individual needs.
LEVEL ONE: THE BASICS
Races. Douglass keeps track of all the year’s races in one separate file. As soon as dates are set, he enters them here, along with information on track contacts and the types of cars included in the race: GTP, fancy prototype cars; GTO and GTU, which look like street cars but are pure race cars underneath; or Firehawk, which are slightly modified street cars.
Teams. In another file, he records companies and individual clients. Most date entries are drivers and their crews, but a few are media people, suppliers, and freelancers, like the guy who sells souvenirs. This is where Douglass keeps fax and phone numbers, addresses, and credit-card numbers for up to three contacts per company. He uses this file to send out mailings, informing teams of which hotels and special discounts are available for each race.
Hotels. In a third file, Douglass records each hotel he’s scouted, with phone, fax, address, contacts, miles from track, number and type of rooms, average rates, comments on the quality of rooms and proximity of restaurants, and directions both to the hotel from the airport and to the track from the hotel.
LEVEL TWO: HANDLING ORDERS
Those three files hold most of Torque’s necessary information. However, Douglass added two more files that draw data from the basic three.
Bookings. The first is the Bookings file, where Douglass records the discount rates he’s negotiated and how many rooms he has reserved at each hotel using a simple grid with four rows of room types (single, double, and so on), headed by each day of the week of the race.
Douglass customized the form for this file, using formulas in certain fields. Thus, for example, when Douglass enters the number of rooms of each type reserved on a Monday night, he can press a key and the Totals field adds up all the rooms in each category and enters the sum for that night. Lookup formulas contribute another level of automation, telling the software to go to another file and pull information into a specific field. For instance, the Bookings file finds the exact dates of the event in the Races file as soon as Douglass enters the name of the race. Likewise, it pulls contacts and phone and fax numbers from the Hotels file when a hotel name is entered.
Orders. Whenever a client phones or writes to request room reservatios, the Orders file is called on-screen. It contains a grid exactly like the one in the Bookings file. When for instance, Jim Steiger from Spice Engineering calls Torque to order rooms for the Sebring race in March, Douglass adds a new record to the file. The exact dates for each day of the week are pulled from the Races file, and credit-card numbers, team name, contact, and phone and fax numbers are pulled from the Teams file.
All Douglass has to do is key in codes for the race and the team, add first and second hotel choices, and record the number of rooms needed for each night.
LEVEL THREE: PULLING IT TOGETHER
Many businesses could stop here, using the Orders form as a customer invoice when the order is filled. However, because Douglass has strict limits on inventory, he’s added a third level.
Confirmation. The main database in this level is the Confirmation file. Here Douglass pulls together information from the five previous files into one confirmation record that he prints out and sends to his client. From the Bookings file come the rates for rooms. From the Races file come the dates. From the Orders file the grid is filled in with number and type of rooms the team requires for each night. From the Team file come the team’s full name and address. From the Hotel file are drawn its address, phone number, and directions from the airport and to the track.
The number of rooms at each rate are automatically calculated. Totals are multiplied by the rates to come up with a total room charge. A service rate is multiplied by the room charge to calculate the service charge owed to Torque. (Teams pay their room bills directly to the hotels.)
Three more files. In a final step, Douglass pulls information from this Confirmation file into a Faxes file (which pulls the fax number from the Team file) and an Invoice file (which pulls information from the Races and Bookings file as well), quickly printing out invoices to be faxed with the confirmation statement to the client.
He faxes a full list of registrants and room assignments to each hotel almost daily for two weeks leading up to each event. He keeps those in a Roomlist database, updating them manually. (Fax and contacts are drawn by lookup from the Bookings file.)
Douglass has worded his Confirmation, Faxes, and Invoices forms so they can be printed directly, avoiding the extra step of merging data through a word processor. Many businesses can use this trick, which is especially handy when mailings like invoices and follow-up letters are sent on a client-by-client basis.
HOPES FOR THE FUTURE
The missing link for Douglass is the ability to subtract the rooms ordered from his inventory of booked rooms.
“My problem,” says Douglass, “is that I haven’t had time or experience to move to the next step. A relational database would subtract the confirmation [in the Confirmation file] from hotel inventory [in the Bookings file], and I just haven’t gotten to it. So what we constantly have to do is print out reports of orders for each day for each hotel, then I have to go back and check how many rooms we’ve booked as inventory. Right now it’s not a big deal, but if we get bigger it will be an absolute necessity.”
He’s put a lot of thought into the problem and has tried many ways to solve it. “If we have 20 two-bed rooms on this night,” he explains, “and Jim from Spice has taken 12, that has to go over to an inventory file and be subtracted from the booking file.”
A hint of frustration creeps into his voice. “And then, obviously, it would be very good to build into the system a blockage where you type in a 10 when, say, Joe from Nissan calls, and the system says, ‘Whoops–you don’t have 10 on Tuesday,’ while he’s placing his order and I’m keying it in.”
At this point, Douglass books only IMSA races. However, using the same tracks at different times of the year are other racing conferences including CART, which runs the Indianapolis 500 cars, Sports Car Club of America, and vintage race cars–which may use the same tracks as many as three times a year. So, theoretically, he could be booking hotels four or five times a year that he is booking only once or twice now. But to expand that much, he has to automate matching inventory to orders. He would be swamped otherwise, and Douglass admits that this problem is beginning to restrict growth.
Probably the easiest way around this problem is to redesign the Orders report. Douglass already has it sorted and subtotaled by hotel, showing him in a glance how many rooms are needed. But he is still checking that list against on-screen records in the Bookings file to see how many rooms he has available at each hotel. If he used a lookup formula to pull that information from the Bookings file into his Orders report, he’d save himself some cross-checking time. Right now, Professional File’s lookup capabilities in reports are limited to three (six with a little finessing), making it tricky to get totals for each day of the week. However, the same principle can be applied to other flat-file database, such as Q&A, that allow for more lookups in reports.
Subtracting orders from inventory as they come in, however, is something you need a relational database to do. In the words of one of Professional File’s tech-support people, P.J. O’Keefe, “There’s a very fine line between a relational database and using lookups in a flat-file program–and right there’s where you start to cross it.”