Mike Ault's thoughts on various topics, Oracle related and not. Note: I reserve the right to delete comments that are not contributing to the overall theme of the BLOG or are insulting or demeaning to anyone. The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer.

Wednesday, February 22, 2012


A recent problem with the phone company here in Georgia brought to mind that basics are called basics for a reason. I was traveling for a couple of days and the wife was staying with her mother while I was gone. I called to pick up any voice mails that might have been left as I was expecting some important calls. Imagine my shock when I received a message saying “We are sorry, this number has been disconnected” for both the home and home office numbers.

I called the technical support line and after nearly an hour (mostly on hold) I was told the issue would be resolved within 24 hours. Of course you know what happened, in 24 hours I was back on the phone with a different support person who told me the same thing and again the next 24 hours later. On the third call they said there was an order that no one could see that was preventing any further orders from being processed on the account.

In the old days we would have called this order that no one can see a hanging tuple or an orphaned record. This means a record was inserted into the ORDERS table without a corresponding entry in the CUSTOMER table (just taking a swag here). Now when someone tries to insert a new record into the ORDERS table the primary key (probably containing a composite of phone number and something else) keeps them from inserting a new value.

Now, I happen to know this phone company is using Oracle as their support database that logs the support requests and tracks them. In a properly designed database referential integrity between the CUSTOMER and ORDERS would prohibit an entry into the ORDERS table without a corresponding entry residing in the CUSTOMER table. This is if the referential integrity is defined at the database level and enforces with optimized, internalized database triggers.

A few years ago, in tandem with the whole object oriented design paradigm and tied in with the rapid design methodologies and with the “Let’s make it generic so if we have to change databases we can” movement, the referential integrity was moved into the application code. What this did was open the database to the possibility of entering bad data if you didn’t use the application, or, if the application has an error. Now, this is all supposition on my part but it appears either there is a glitch in the application logic (but why would it only effect one customer?) or someone did some manual inserts, updates or deletes on the database bypassing the application logic.

This is what happens when you ignore basics and try to rewrite things into the application layer that should be left to the database engine. So now I am on a full week without home or home office phones. What is that little gizmo you can plug into the computer and get phone service through? I think I have their website here somewhere…

No comments: