jfw.id.au

It’s a beautiful thing, referential integrity, when used properly. It provides such power and stops situations like the one that I am currently dealing with at work from eventuating.

A few months ago we launched a redeveloped version of most of our core systems at work. I didn’t feel it was ready for launch, but we won’t go there now. At the time we launched we imported all records for the current financial year. I always believed that we needed to have all information across, but management decided that it wasn’t.

1 week before the end of the financial year I was informed that the auditors that we had in had informed management that we had to have all the information across, and that I had to have it done before the end of financial year. FUN! NOT!

Any way, to cut a long story short I am now trying to pick the last few pieces of information up out of the old system and pull it into the new one. I’ve gotten 98% of it done and these last 100 are annoying the hell out of me. Or rather, the lack of any form of referential integrity on the database is annoying me. I’ve got orphaned data in the old system that I have to find some way of pulling into the new one. The only answer I could use was to create dummy records for the deleted information and to map the orphaned data to the dummy ids. This is not ideal, and I personally think it’s a bit of a “dirty hack” to borrow another developers favourite phrase.

The old system was a mishmash of technologies over a postgresql database. It’s not like we couldn’t have foreign keys in the system. Hell, even access supports them, and in my current situation I would have prefered an Access system with foriegn keys over the postgres one without.

To make matters worse, we’re migrating to a single platform. DotNet over Oracle. This should be much better, but no it’s currently no better - at least from where I sit. The problem is that the developers that were the initials on this project didn’t implement foreign keys in the database.

I’ll have the last laugh though. Eventually propper foreign keys will be implemented on the system and the developers here that dont like it will just have to get over it. If I am to be responsible for the database, and I believe this is what a DBA is responsible for, I am going to ensure that we are making the best use of the power and functionality that it provides.