[AccessD] On DB Bloat, Bad DB Design, and various

Just a note on this bloat subject. I acquired a new client recently,
whose DB was 220 MB. They burned me a CD containing same last weekend,
so I could work on it while they were closed. Dumbass previous developer
hadn't even split it into FE-BE! Everyone was loading the same instance
of the whole thing across the net!

They had a problem which I fixed. I estimated a day but once I got into
it, 3 days elapsed. I split the difference and billed for 2 days.

Upon inspection of said database, I discovered some incredible and
bizarre anomalies/stupidities. Several tables of critical importance had
no PKs and no FK indexes! Unbelievable!

Anyway... I split the datbase, then made some mods to the BE which in
theory ought to have almost doubled it (i.e. I made copies of every
transaction table, but not the static lookup tables). Then I ran
compact/repair, and the db came down to (gasp) 40 MB. This includes the
doubled tables and the new PK and FK indexes that I added. From 200+ MB
to 40 MB -- and this with doubled transaction tables. Once we get the
s**t sorted out, I expect that it will come down to 25 MB.

I have never programmed in an automatic compact/repair before -- I
suppose because I tend to be available and do it as a matter of course.
But this app has caused me to rethink that.

More seriously, this app has caused me to rethink the virtues of
certification. No BE! No PKs! No FK indexes! No wonder the bloddy app
was slow with only 20 users on a net!

The up side is, it's really easy to look good in a situation like this.
The performance gains were spectacular. I got high-fives from all
directions :)

This is a strange business :)

Arthur