May 5, 2006 at 2:35 am
Oooh I'm having fun this week....
I have a third party SQL app where the data integrity is enforced programmatically, and I'm trying to reverse engineer it.
Oh dear. This means first off that there are no rules to refer to in SQL server, which is fun. Secondly I'm finding consistency problems all over the place.
What I'm finding is that it depends which entry point I use to build a query what results I get. Essentially each table has an FK to the tables it is related to, but these FKs aren't consistent, the programmers have dropped a few clangers along the way.
OK, so I've rebuilt the same query 5 ways starting at each of the 5 tables respectively and by using NOT IN I can see where the consistency problems are, but this is awfully hard work, and I have another 10 or so queries to go.
Has anyone had any experience of this kind of nonsense? Any tips they'd like to share?? Anything useful would be appreciated - I've half a mind to run the tables through Oracle so I can use minus and coalesce queries....
BTW - I remember a discussion about 2 months ago regarding the advisability of enforcing data integrity through code, after looking at this mess I can quite authoritatively state that anyone who thinks enforcing integrity through code is a good idea is completely bonkers in the nut....
May 5, 2006 at 5:36 am
exactly right.
always always enforce referential integrity through the db. if your app fails because of DRI then its not a good app
mostly developers don't use foreign keys and constraints because they stop the developers from developing. pishhh - if they set up a good database first they wouldn't have to do half the work. even beter - get them to use stored procs and they won't have to worry about atomicity of transactions.
i've even seen developers dropping primary keys in applciation updates so that they can "improve functionality" by allowing duplicates
we've had to do this kind of cleanup work so many times in the last 3 years that it's become second nature. I'm afraid i can't really give you any specific advice without more details, but here are a few things we've done to help us out
create views that provide reports of data not matching your missing foreign keys - rerun these weekly and pass the data to someone who can kick up a fuss that you're losing money/risking a lawsuit/wasting your time
create triggers on tables to report incorrect "foreign key" entries as they are entered (you can then see if the initial cleanup you did requires constant updates and if you're safe to apply the FK for real)
afraid i've not seen any really usefull tools for this
MVDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply