Approximate Date: 1989-1991 Computer: IBM XT/AT Clone (80286)
Tools & Technologies that didn’t exist: Internet/Intranet, PC level relational databases, Data management tools, User management tools, SSDs, Thumb drives.
Historical remarks: Office networking was still new but gaining momentum through the efforts of companies like 3COM and Novell. Microsoft Windows existed but wasn’t really used seriously until the introduction of Windows 3.1 when the Graphical User Interface design of the tool finally started to come into its own. There was still no internet and only a select few companies were active in email. Computers where still being used predominantly as single user disconnected tools for accounting and administrative tasks. “Word Perfect” was the word processor of the day and Lotus 1-2-3 was the spreadsheet tool in use worldwide. Dbase was the preferred database tool a bit beyond the average user at that time.
Epson Repair Tracking System
At the time I was working for Epson Canada as a front line telephone support tech with special focus on Networking design and architecture, programming and virus investigations. Put simply, when I wasn’t answering customer questions, I was building different network architectures, playing with Novell servers and collecting viruses to disassemble and analyze.
My role in the support and investigation of customer issues over the phone had been very successful so they expanded my role to include the processing of on-site warranty requests. The challenge was that on-site warranty contracts were sold by device and number of devices but the company only really recorded the name and location where the warranty had been sold, not the actual device being warranted. So I set to work designing a system for this task.
These were the days were relational data was still only on larger multiuser servers like Sybase on UNIX. All I had was Dbase and Clipper which wasn’t relational, but I needed to represent a fully relational structure. Epson could have an unlimited number of contracts with companies that could have an unknown number of office locations in an unknown number of countries. Each office could have an unknown number of employees and each employee could have an unknown number of devices under a specific contract.
Keeping in mind that this was before anything like SQL, I broke the structure down to Companies, Locations, Employees and Devices and built flat data tables of each one ensuring that all records had a unique record number. Then, to satisfy my need to link a record from one table to the record of another table, I created what I called index tables which contained nothing more than record number pairs. For example, the “CompanyEmployee” index would contain the company record number in the first column and the employee record number in the second column. Naturally the left side of the index would be duplicated a lot but the right side of the index would always be unique and would allow limitless associations with minimal data housekeeping for employee moves and removals. Naturally this also meant that I had to know which company an employee worked for and what location they worked at before I could enter the employee information into the database in order for the indices to be properly initialized for that entry. But this worked well as it followed the natural knowledge path.
As customers requested on-site service, I populated their details into the database, dispatched the techs and requested a copy of the on-site contract. I would then populate all of that customers serial numbers into the database along with the contract sign dates. It didn’t take long before the database began catching on-site service requests for hardware that wasn’t covered under contract and a huge contract scam was uncovered wherein clients would purchase a contract for a single device. Since Epson was only checking if a customer had a contract for a printer and never checked WHICH printer the service agreement was for, purchasing a contract for a single printer got you a year of on-site warranty support for every printer in that location! Some offices even tried swapping broken equipment with regular callers for on-site service dispatch system in the hope that Epson would associate the device with the person. My new Epson Repair Tracking System was now catching that too.
The end result was 2 fold;
- An instant increase in on-site service contract sales
- My role was expanded again to include Authorized service center parts requests management.
Because my newly expanded duties, I was given staff to mentor into the role of Technical Telephone support.
Retrospective
Although the idea of multi table relational schemas are common place today the tools needed to perform those tasks simply didn’t exist 30 years ago. Dbase did have a “Set Relationship” feature for relating tables of data but it imposed interaction limitations and I don’t like limits so I coded around it with enormous success.
It’s been my experience that avoidable limits are simply place holders for the Band-Aids you’ll need to support the new features you didn’t know were coming. They’re also usually the birthplace of Technical Debt! Fail to plan and you’re simply planning to fail!
Part of software engineering is looking at a business process and finding ways to utilize the strengths of technology to find new efficiencies.