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

Arthur Fuller artful at rogers.com
Fri May 21 22:32:01 CDT 2004


This is a thread worth pursuing, IMO. Given db1 with its (bad)
structure, how does one create the queries and execute them to create
db2 with its (good) structure? I've been fighting this problem for the
last few months, and it is decidedly non-trivial. There has to be a
correct way to do it. I'm certain of that. But I haven't found it yet.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: Friday, May 21, 2004 9:05 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various


"Colbyize the user" - Out the door, 20,000 feet, without a parachute.

For those who were wondering.

It actually came from the practice of certain South American "leaders",
when dealing with the Shining Path guerrillas (or suspected guerrillas
in too many cases).

Back to the subject, the unfortunate reality is that the typical
application designed by the typical user often simply can't be
transitioned economically.


I "transitioned" one of these things at one of my current clients - a
call center for the Disability Insurance industry.  The main table was
over 120 fields that represented about 5 different major entities
(Insurer, Policy Holder, Policy, Claimant, Claim, Physician1, Physician2
etc.) plus dozens of "lookup" tables (city, state, Policy Type etc).
They started with a "flat file" data dump from their client (the
insurance company) which they just started adding new fields onto the
end of.

Needless to say, it took months to analyze the entities and write the
queries to extract the data (normalize the thing).  Then it took more
months to build the forms to allow data entry, reports to report the
data etc.  Had I been called in at the start it would have been a far
less imposing task.

They hired me 1/2 time for almost a year to get the thing ported and
running, with more than 1/2 of that time just extracting data.  They
USED that data every day and couldn't run the business without it.

I had to build a system of queries that I could sequence in exactly the
correct order to normalize everything.  I had to test it and test it
again to get it all exactly right.  Then I had to build up the forms
they would be using after the port.  I had to run the normalization and
have a handful of testers do double entry (in the new and the old) to
ensure that it worked, then I had to train the users in the new system
(it simply didn't and COULDN'T look or work like the old).  Finally I
had to "throw the switch" one night porting the data, and make sure I
was available on site for the next several weeks to handle in real time
the inevitable issues that arose, to keep the system running and their
client (and the claimants calling the database users) happy.  On top of
all THAT I had to keep the old db running until I could throw the
switch.

Perhaps this isn't a "typical" power user database gone wild, but it
could very well be.  And the results are VERY expensive.  The company
just doesn't function without the database and the cost of
"transitioning" is astronomical compared to a gradual building up of a
system the right way from the start.

On a humorous note... the previous "developer" at the company was in
waaaaaay over her head trying to accomplish this stuff.  She just
"disappeared" one day, never to be heard from again.  My client was very
nervous that I might do the same thing.

John W. Colby
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Hale, Jim
Sent: Friday, May 21, 2004 5:47 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various


Thanks, Susan- you hit the nail on the head and have prompted me to put
down some thoughts that have been bothering me for awhile. Hide the
women and children, here goes :-)

<rant mode on>
I have to start by saying upfront my career evolution has been from the
finance/accounting user-to power user-to developer lineage and NOT the
IT side of the world. Frankly, I developed programming skills in self
defense to have control over my own destiny because the IT side of the
business couldn't/wouldn't keep up (even when it reported to me). My
understanding of Access has been need-based and evolved slowly over
time, i.e. I discovered Access and relational databases when Excel
"database" tables could no longer satisfy my needs. For several years
Access wizards and macros were my "state of the art" and I was happy as
a clam. My little databases received "raves" and made me the office guru
to the point I decided to take a college course in Access. It was at
that point I realized I really didn't understand relational databases
and that in fact my "wonderful" databases were all wrong except for the
fact they produced useful results. While I have since gone on to develop
my skills much further (yes, Virginia, I eventually discovered VBA!) the
point is I believe my evolution is more the rule than the exception. I
am willing to wager that in terms of sheer numbers the vast majority of
useful, results producing Access databases have been created by the
user/power user cadre rather than developers. I know of a major
insurance company whose IT group recently did a nose count of personal
Access apps that were floating around the company. The numbers
absolutely shocked them. Here at my company we have several people who
know a lot about the business but only a little about Access.
Nevertheless they are using the tool to produce very useful results.
This is the true "silent majority" of Access users who are attempting to
solve problems on a day to day basis. This should not surprise anyone
because after all the product was designed as a personal app. It is a
tribute to the strength of the product and the creativity of developers
such as those on this list that Access has evolved far beyond a simple
personal tool. The fact remains, however, personal databases account for
the vast majority of its use.

With this as background I am very disturbed by Microsoft's apparent
intent to remove Access from the mainstream of evolving apps.  I also
was disappointed in Getz's column a few months ago summarizing a wish
list for the next Access version. It seems to me Access must stay true
to its roots and those roots are as a personal app. What does this mean
at the practical level? The self taught user runs into trouble not with
the small apps that, however constructed, they can still get their arms
around and validate the results. Its when these apps morph into mission
critical monsters that have grown in size and complexity to the point
where the non IT professional can no longer ensure valid results that
things usually hit the fan. Typically these apps spin off into space
before developers are called in for pooper scooper patrol. So what is
the solution? It would be tremendous if Access could be given additional
tools/wizards/internal training screens/magic to ease the transition in
the database life cycle from user app to developer maintainable code.
While it might be fashionable to always say we should "colbyize" the
users, on their side of the fence they have even harsher terms of
endearment for the IT crowd. It is  certainly not realistic to say users
should keep hands off and leave all the development to the pros. I
believe the more understanding users gain about relational databases by
hands on efforts the better off we all are. Having users in effect
"prototype" apps by taking a shot at building it also can save time
compared with a blank sheet of paper where the developer is forced to
play 20 questions trying to divine what users "really" want. If tools
could somehow be developed to smooth the user-to-developer transition we
would all be winners IMNSHO.  So I throw it out to the group, what sort
of improvements could be made to Access to lower the user learning curve
and smooth the handoff of projects from user to developer?

<rant mode off>
That's my story and I'm sticking to it
Jim Hale

-----Original Message-----
From: Susan Harkins [mailto:ssharkins at bellsouth.net]
Sent: Friday, May 21, 2004 8:55 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] On DB Bloat, Bad DB Design, and various


Arthur -- do you know who wrote the original app? Was it someone
in-house that had to put together something because s/he was told to?
Access is as much a user database as a development tool -- that's what
makes it so alluring to such a wide audience. If the boss tells you the
department needs such and such, and you're not a database developer,
know onlyh a little about Access, you might come up with crap from a
developmental perspective
-- but if the crap works... Of course, eventually, they probably are
going to have to call in someone that really understands the issues, but
for awhile -- it works. That's not a bad thing -- and I don't know that
that's even the situation in your case Arthur -- but I think it happens
a lot.

And a lot of so called developers produce crap -- especially the
geniuses in other areas that think Access is a toy and that anyone can
"do it." Those folks irritate me because invariably their stuff is
inefficient and laborious -- but it "looks" difficult and that's what
people expect to see, so they must know what they're doing, right? :)

My personal favorite is developers that claim it can't be done without
code. Yeah... Right...

But, the crap issue -- it's why I don't do it -- I'd produce more crap
than good stuff in today's environment. I can sling out little stuff
with the best of you, but once you get into the multi-user issues, I'd
rather visit a dentist.

Susan H.

I don't think certs are the answer either Arthur--it is too easy to get
a certification, and they push you through to fast. You don't even have
to produce anything original to get a cert--just do their stupid
exercises in the back of the chapters. And, I have seen certified
people, both programmers and network admins, do stupid stuff.


--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list