Re: Declaring Unenforced Constraints

Laconic2 wrote:

>
> The unenforced constraint answers both of these objections:
>
> It won't slow things down until you test it. And sloppy data can be put
> into your database any time you like.
>
> Later on, you can find the sloppy data, and decided whether to fix the
> sloppy data, or fix the constraints, or fix both.
>
> And, you can even find out HOW MUCH it costs to enforce the constraint
> all
> the time, by altering the enforcement of the constraint.
>
> I think this would provide a road to redemption for those managers who
> care
> more about performance than about correct data. And that's the hidden
> agenda.

Perhaps this story will shed a small amount of light on that possibility. It is from a former life I lead at an ISV that produced a 2-tier package, a db app designed and written entirely by programmers, not one DB guy in the shop. [1]

The package was under active development, and upgrades were frequent.

To set the scene, imagine this. Upgrades were performed by code. Each individual unit of work would have an associated script that did things like update structures, perhaps populate system tables, and more often than anyone would admit, directly write to user tables to fix data the app itself had munged.

To put it simply, the upgrades were hopelessly broken. The theory was that if you ran the scripts in the order in which they had become part of the release, everything would work. It never did. I don't mean never-as-in-rarely I mean never-as-in-zero. The procedure was to take a customer offline -- during business hours -- and run the scripts through in order, grovelling through the errors one by one until the scripts finally executed. This process was handled differently depending upon who did it, effectively ensuring that no two databases had the same meta-data.

OK, so enough bashing. I preached some of my sermons on a data-driven database builds and was given the go-ahead, as far as server-side elements, to write a build engine. [2]

Now here was the funny thing. The old system had a 100% fail rate that led to manual intervention, and a lot of sweeping-under-the-rug. This led to a lot of databases missing constraints. Now imagine a system that is simple and robust and never fails to add a constraint. Support exploded. Suddenly every weakness that would manifest on a constraint bubbled up, and often from many of the customers at the same time. It was the classic case of feeding rich food to a malnourished person: they vomited it back up.

There were other problems. Almost all customers had code patched in by hand late at night in an emergency, which often depended upon the structures of that particular client, which we knew were often not standard. So weird things would break when the new upgrade system would add columns that had been missed 6 months earlier in the prior upgrade.

So, to get back to the original thread. The solution was to add what we called "OK TO FAIL" to all constraints. What this meant was that the build would not abort on a constraint create failure, it would write it as a warning and keep going. This allowed us to get to 100% by degrees, instead of in a big lurch.

They eventually got fixed, mostly. At this shop the CEO would ocassionally show up with somebody and say, "this is my friend Johann Schmitt, perhaps you have a spot for him?" What do you know! We did! Eventually one of these FOC's (friend of CEO) actually had some SQL skills, and we put her on to running SELECTs to find offending data and working with the client to manually purge out the violations. Then we'd run an upgrade pass, which would notice the constraints were not there and would try to put them back in, and would finally succeed.

In the end, the bigger clients all had their legacy constraints in place and new improvements came in with the constraints in place from day 1.

[1] For what it's worth, that was the last shop I walked into calling

    myself a programmer. I walked out calling myself a database     architect. When I left the count of staff with knowledge of     both tiers went back to zero.

[2] The engine ended up being mostly a vehicle that loaded

    code to the server, such as stored procedures and triggers written     by programmers. This made it a code management tool instead of a     data-driven differences engine. It also led me to the conclusion     that any code was bad code, and led me to pursue a way to completely     specify a system in scalar data, eliminating the programmers.

-- 
Kenneth Downs
try { java.lang.String.tcpip.usenet.posting.response.sigblock.setSig( 
        sig.toUpperCase().trim(); }
catch (VerbosityException e) { ErrorWrite("Puh-leez! waaaah, I just wanted
to redimension my array, is that so wrong?"); }