THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

Multi-purpose columns

I have run across a real doozie of a db design that I just have to share. It is an OLTP database but it starts with the notion that there are no updates or deletes. Everything is an Insert. If a change is made to an existing row a new one is inserted that has all the data from the previous one and of coarse the changes. This means that the table no longer can use the proper PK (ID column) and includes an identity column for the PK (Key column). So now there are views everywhere so that when ever you access the table it runs a sub-query to determine which is the most recent row for that ID and assumes it is the one you want. That wasn't enough for this designer they couldn't stop there:). There is another column in the table that normally holds a FK to another table as the name implies (xxx_ID). Yesterday I found out that in reality that this column can hold the ID from one of at least 3 different tables. Sort of a multi-purpose feature I guess. Pretty slick right. Now it requires logic to determine which referenced tables you are dealing with at any time and join on the correct one. The process hsould be pattented.  OK but they didn't stop there either. It turns out that in these views that determine the correct rows they join this table with the related table. The problem is that in some cases they join using the ID column and others the Key column. So apparently depending on which side of the bed you got up on, what time of day it is etc. you join on one of several columns in several tables to the same column in this table. It must have all been part of a master plan to retire off the maintenance of this app due to the hourly vs. fixed rate:) 
Published Friday, July 21, 2006 4:45 PM by Andrew Kelly

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



a.m. said:

Reminds me of an app I used to work on that used GUIDs in the database to emulate unique subtyping.  The "parent" tables didn't have to know the child type, because it was assumed thet the GUIDs would never be duplicated in the child tables.  So to find the type, you just join to each child table until you get a match... Until that fateful day when there was a bug in the app that inserted the same GUID into multiple child tables.  Oops.

July 25, 2006 10:48 AM

Dis4ea said:

If there's one thing I really really REALLY hate it's got to be those kinds of solutions.  I saw a project where the database stored the business entities definition as an xml.  They even had a whole configurator app to design these entities.  All data was saved in a generic table and the data retrieved was then parsed according to the xml metadata from the 'businessEntity' table.

Every consultant that had to work with that 'framework' tried to get rid of it... to no avail since the author had a way of selling it to management.

I'm glad we have some influence on the architecture at my current project...
July 26, 2006 1:31 PM

k_nitin_r said:

Have you ever had those times when the requirements you've been given are incomplete but you still have to hand in a relational model? I had to do that on my current project, and I explicitly stated that it was a work-in-progress and that we're waiting on the client to give us the complete requirements. I'll bet I'll have to redo most of the work, since the model handed in has lots and lots and lots of assumptions.

A previous project had a client who could just about never make up his mind when we were kind enough to show up a finished version of each phase of the project - we were still on phase one when we should've been on phase four of the seven phase project. We then changed our process to go the waterfall way from that point on, got a sign-off on the requirements and proceeded to the end. When we informed the client about the cost of changing the final product, he decided to take what we had to give him to start off with and plan the maintenance later. Just knowing that we had to perform maintenance on the application for the customer was a thought none of the developers wanted to live with, so all the members of the development team quit!

For next time, the note for our project manager would be to set out with a contract mentioning high-level requirements along with the price, and send out an impact-analysis document with the cost of any changes suggested by the client to the requirements.
August 3, 2006 3:27 AM

JT's Ghost said:

The proliferation of database management tools amoung the general population only insures that those of us who refactor and optimize databases for a living will be fat and happy for years to come. We'll just keep turning those lemons to lemonade for $@! / hour.

January 17, 2007 9:24 PM

loemrntdherid1 said:

May 20, 2018 8:21 PM

Leave a Comment


This Blog


Privacy Statement