This project was for a life insurance company that had a legacy Life Insurance and Billing system built in dBase. The system allowed the entry of customers, calculated the rates based on their age and a few other statistics, then produced a bill for mailing to the customers and tracked the payment status of the bills.
Over the years, the insurance company had created a separate version of the code with tweaks for each new customer. Things like billing rates, payout tables and other things were implemented for each customer.They generally sold policies to large groups with members like churches, trade organizations etc).
They wanted to modernize the system and re-unify the application so it would work for all current customers and have the flexibility to add future new clients without the risk of bugs, code copies etc.
I generalized the design for different rates per customer, whether they had different rates per gender. I also wrote modules to convert the current data into the new universal format.
One interesting thing was that someone had designed the address information in the Customer table to be very generic. Basically, they had five fields to contain the address named: address1, address2, address3 etc. Of course this is a very bad idea and makes the address data basically unusable. The part that became tricky is that some customers had name/address/city in 3 lines and some in 5. AND where which piece was located was irregular, even within one version for the system. For this I made come conversion utilities that achieved 98% successful conversion of the several thousand records.
One interesting problem is when I began converting the data for a churches’ version and discovered that had missionaries abroad who had life insurance. The address format (I think it was the Philippines) was radically different but I recommended the practical route and manually entered the few than needed to be converted into the new format rather than spend the time to wrote code to convert a few special cases.
Technoloigies used:
VB 6 Oop, Stored procedures in SQL server.