So I’ve been asked to add a feature to one of the apps that I nominally took over when my former manager left … they want a management pane where certain authorized super users (to be decided later) can add data to the main lookup tables, and must be able to do clean up by deleting data which has been entered erroneously … including cleaning up any references to the now missing data.

I’m currently trying to figure out what database tables I need to be concerned with, and I just have to vent, because this system is the worst mess I’ve ever seen.

• There’s one database supporting five or more applications…
• There are 87 tables (with names like tblAQ_DcSs, tblAQ_SwNw, tblSFM, and tblSW_PWSOS, tblTestCaseTestLayout )
• There are three duplicate user tables: tblPeopleLookup, tblUser, and tblUser3 — tblUser2 is a view onto an external user database which is what is supposedly being used … and apparently, tblPeopleLookup is some sort of mapping from tblUser2 to tblUser3 … and tblUser1 is the original user table. I don’t know why these are all still here — I can only hope none of these others are still being used.
• There are 144 stored procedures (with names like sp_Fix, sp_Fix2, sp_Fix3, spLeftToTestMulti, spLeftToTestMulti2, spLeftToTestMulti3, sp_Whatever, and the awesome spTestCaseTestLayoutTestsUpdate, spTestCaseTestLayoutTestsSelect, etc.)
• There are no Foreign Keys. Yeah. None.

Technically, there are lots of foreign keys — it’s just that none of them are declared as such, so there’s no referential integrity (did I mention that there’s an access database floating around out there with linked tables and a hard-coded login which the end-users pass around to each other so they can insert data into some of the tables by hand because the original developers didn’t get around to writing this management app that I’ve been asked to write now?)

You can tell that some of the columns should be Foreign Keys, because obviously a column in a “tblReq_Tag” table named “Feature_ID” must be an external lookup of some sort … but there’s 86 other tables … and at least two of them have Primary Keys called “Feature_ID” ...

So, I’m spending a lot of time searching the source code and the 144 stored procedures … An astonishing number of these stored procedures involve cursors and multiple nested case statements. I just picked one at random which I thought sounded simple: spEnterGroupResults ... it’s about 150 lines of SQL, and it uses a single cursor variable “crsUnit” which it redefines three separate times onto three different queries which it iterates over. Each of these queries involves joins onto nested subqueries, and I count myself lucky because the tricky part is actually enclosed in a transaction, and at least this one isn’t doing all of that just to dynamically generate a further SQL query to execute.

So yeah, I’m literally looking through source code to try to understand the database design. The problem is that there are more than five different applications, each using slightly different technologies.

• One of them which has never been migrated from classic ASP ... with the business logic written entirely in Javascript, and the data handling performed entirely by sending huge XML files back and forth to a “do all” webservice.
• One of them was written in VB.NET in VS 2003, and has never been upgraded.
• The rest are in C# — with most in VS 2005, and at least one in VS 2008 and C# 3.0 — some are Asp.Net, some are rich client …

The tables I’m most concerned with right now (for this app) have some ahem ... impressive design decisions of their own. Of the 8 tables that I’m looking at directly (I think these are the only ones I need to modify as part of this app), five of them have multi-column primary keys that involve more than half the columns in the table, including columns which are, in fact, unconstrained foreign keys. And there are so far 5 foreign key looking columns which I haven’t been able to find the primary key column for …

Edit: Oh yeah, and half of these tables have columns like [Enabled] [char](1) NULL … That’s a boolean value folks, stored in the database as a y or a n … and it’s nullable even though a null (or any value other than y or n, really) will most likely blow up some code somewhere. And no, there’s no script constraint or trigger to ensure that this doesn’t happen (I checked). For extra fun, the other half of the tables use ‘bit’ columns for things like this — because they were written after I started working with this team (on a different project) and happened across one of these char columns during our one and only code review ever and wondered aloud why we needed to pretend it was still 1992. Why they just switched, without changing the others, I’ll never know…