From: CK

To: Editor

Date: 21 May 2004

 

So, at my job we have installed the latest update to a major help desk and inventory tracking system.  After several false starts we finally got our data transferred from an earlier version of the product to the new version. Then problems started happening.  First, the provided ERD which said User's FULLNAME was the referential key was incorrect, it was actually USERID. This necessitated a quick UPDATE query to sync the old imported records USERIDs to the actual USERIDs of the Users.  Next, several other parts of the system started producing odd errors.

 

After much prodding in the database (since the corporate support contract we had purchased apparently did not cover any database support), I finally found out what was going on.  Six separate tables were deriving their primary keys from a value stored in a seventh table.  Each of these tables hold independent, unrelated data.  This was entirely undocumented on the ERD, as well as the written documentation.  This meant that if you had purged part of the data as we did because it was questionable (the old version did not produce good inventory data), that unless you were very careful, you ran the risk of causing the entire product to fail.

 

The PKs generated are sequentially derived from a LASTITEM value stored in the table.  It seems to me that making use of an IDENTITY value in the primary key of the SQL tables would be better.  In addition, none of the integrity and consistency logic is in the database.  It's all embedded in the programs used to access the database.  The product was originally written in FoxPro many years ago.  When they moved to SQL they didn't bother changing the architecture to match the abilities of the database system and instead continued to treat it the way they did in FoxPro.

 

It makes for quite a headache to support.

 

 

From: Fabian Pascal

To: CK

 

Any comment would be superfluous.

 

 

Posted 09/03/04