The Database Design Resource Center

Hornet's Nest

by Peter

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?

Comments for Hornet's Nest

Average Rating starstarstarstarstar

Click here to add your own comments

Sep 09, 2016
starstarstarstarstar NEW
by: write my resume

That's interesting. i've heard similar stories earlier. What is the main reason
here? What do you think?

Oct 07, 2015
The company's data NEW
by: Simon Collins

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.

buy instagram followers

Oct 05, 2015
Review NEW
by: Anonymous

Luckily, the hornet home will last just the momentum season, and will be empty after the first hard stop or a few ices. essay writing help service. After that, you can securely thump the home down and have a great trinket.

Aug 14, 2015
Good post NEW
by: Magdalena Harris

Hi! First of all, thanks for your site! You have really great articles! May I ask to do a favor for me, please?
I would like to advertise my new project FindCostumes on your site, I really love your content and articles and will pay you the amount you want.
I prefer banner ads, but if you could suggest other ways I will gladly review it, please, answer me!

Thank you!

Aug 08, 2015
Education NEW
by: Anonymous

Education prevents a person to sign a false or fake agreement and treaty because he/she is able to read and write. An uneducated person cannot sign a cheque until and unless takes assistance from an educated friend.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Worst Database Design Experience.

Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant

Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:

What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials

Database Normalization eBook:

Database Normalization eBook

Copyright © / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.