Hornet's Nest

by Peter
(Canada)

I worked in the IT department of a software firm in Canada for several years on a project designed using Visual Basic 6 and Oracle 7. The application's database needed to exist in Canada, the UK, and Australia. Since the application was written as a Windows app., it was fat-client. Asking the users in the UK and Australia to access a database in Canada had obvious, unacceptable performance problems. Browser based application development just wasn't functional enough back then. I think it was approx. 10-15 years ago.

To solve this problem, each location had it's own database. They are connected using DBLINKs. The application's "architect" decided to invent his own replication solution. For each table which needed replication, a trigger was created to store the table's PK info. along with a date stamp into a separate table, called a replication table. Every so often, a procedure would execute which would read from the replication table and attempt to insert or update the data into each other database which did not have the record.

Three databases caused problems with the sequence generators which were used to produce PKs., so each database's sequence had its own start number range for this.

Sometimes the replication procedure would fail when it attempted to either insert or update a record because this solution did not take into account the FK relationships, so the FKs were just plain dropped to solve this problem. The company's data warehousing department because very frustrated. The ERD diagram which I produced to aid in resolving this was a hornet's nest. PK and FK were documented, but did not actually exist. I wish I still had a picture.

I can remember several 4am phone calls because the application would no longer run due to data-related problems.

The replication procedure would produce errors by inserting the table name and PK info. into an error table. Each error had to be fixed manually, and we received several dozen per day. Each table which required replication also had it's own error table. And don't forget, this scheme is multiplied for each of several dozen tables which needed replication across all three databases.

The Company Contact table had 1 field to store the phone number. For reasons I never understood, this was inadequate to store international phone numbers. So, more columns were added to store the dialing code, area code, prefix and phone number. How is anyone to know upon which column(s) to report?

As the years passed, we upgraded to version 11i of Oracle. We investigated using Oracle's built-in replication features. It worked well, but for reasons unknown to us, it corrupted its own data dictionary tables in the process. The database became unusable. So back to the tried and "trusted" solution.

What do we learn from this?