I have a handful of analogies that explain my database design
philosophy. Some came from colleagues and mentors, some are my own. Here are
three of my favorites.
Three Functions of a Dry Cleaners — Three Functions of a Database
Application
A properly designed, three tier Access database application bears
a remarkable resemblance to a dry cleaners.
First you need a pleasant,
efficiently laid out, user friendly interface. In a dry cleaners, that is the reception
area and front counter. Customers are welcomed in by chrome, glass and potted
plants. They interact with the counter person, dropping off new batches of
dirty items and picking up clean ones. They usually get a receipt for their
transaction, and use it again later to identify their dry cleaning for the
cleaners to get it back.
That's pretty much how an
Access interface works too. It facilitates data entry and reporting, i.e. the interactions
with the data in the back end.
In the back of the dry cleaners
you find the equipment and storage tubs, baskets, and bins and the noisy, dirty cleaning
machines you never want your customers to see.
And that's exactly how the
tables and queries in an Access database work as well. You never want users to
have to see them, but nothing works without them.
And between them, you find a
transitional area where each customers items are grouped and sorted and moved
from one container to another according to the rules established to manage it
all.
In a well-designed Access
database application, that's the job of the logic layer—the VBA and macros.
Yes, I know. It's not an ideal
scaffold on which to hang the complexities of a properly designed database
application. It is, nonetheless, a reasonably colorful picture of an Access
database properly split into a Front End and Back End--with the preview of
the logic layer that makes it all work.
So, if you will, an Access database application has a lot in
common with a dry cleaners.
Paddling vs Floating in a Canoe
I got the canoe analogy from a fellow MS Access MVP. It’s a good
way to explain why it's so important to do things "the Access Way".
One of the most common problems we run across with Access is the misguided
application of Excel spreadsheet experience to relational databases like
Access.
Access is remarkably flexible and forgiving. It’s possible, for
better or worse, to make it perform amazingly complex feats using “spreadsheet
style” tables and sticky wads of Macros or VBA. Things like Repeating Groups of
fields in a table, or even multiple tables containing segmented data (e.g. “tblSales2017”,
“tblSales2018”, “tblSales2019”) are not only possible, but even, with enough effort
and ingenuity, quite workable.
As the saying goes, just because you can do something, that doesn't mean
you should do it. And that leads to the analogy of a trip in a canoe.
If you go upstream in a canoe, against the current, you’ll
spend all of our time paddling.
If you go downstream in a canoe, with the
current, you only need the paddle to steer.
Access, of course, is the canoe in
this analogy and the development tools—tables, queries, VBA and reports—are the
paddles. If you want to work less, you’ll
learn and follow the best design principles. Normalized relational tables,
forms with subforms, and so on. Use them to steer, not to paddle against the current.
Pigs in a Cadillac
One of my favorite stories
concerns pigs, Cadillacs and the surprising rarity of common sense. And not
just in the design of Access database applications. This story goes back to the
very start of my career with Access.
The original version of this
story involved the wisdom of buying a Cadillac to transport pigs.
I was a member of a team tasked
with evaluating software applications for a large financial enterprise. Two main contenders emerged in the search. One
was a modest Windows based package that came with a mid-five figure license
fee. The other one had, as my friend Armen likes to say, an additional zero on
the right end of the price tag. One of the analysts responsible for the
evaluation feared we were going to choose the Cadillac version, so she offered
this little story to encourage the common sense choice.
Here's the story.
You have raised a herd of pigs
which you need to get to market. It's time to acquire a vehicle to haul them
there. A visit to the local auto dealer, though, presents a bit of a puzzle. On
the dealer's lot you find two vehicles big enough to do the job. One is a used
pickup truck with a stake bed suited almost perfectly to hauling farm animals.
The other is a brand-new full size Cadillac Escalade with plenty of room for a
handful of pigs—after a few modifications of course. The price tags of those
two vehicle options also differ by a zero, as you probably already guessed.
So, the question for you: Do
you want to have the prestige that goes with being able to haul pigs in the
back of a brand-new Cadillac? Or should you humble yourself and buy a used
pickup truck because it's better suited to the job (and cheaper to boot)?
Well, in that particular
situation, the enterprise took ownership of a very nice Cadillac, and had it
retrofitted with an appropriate pig holding enclosure, which came at a substantial additional premium over the original license fee 😁.
Unfortunately, in that case, common
sense did not prevail.
Over the years, I've had more
than one occasion to apply the moral of that story to other situations. Despite
the temptation to haul my own pigs in a Cadillac, I have made a concerted
effort to stick to the common sense choice as much as possible. It’s saved me a
lot of embarrassment and effort, not to mention money.
Lately, answering questions on
UtterAccess, I've been thinking about Pigs in a Cadillac a lot. It’s
seductively easy to look for a clever way to write wads of code to do something
that would be dead-simple, but boring, if you do it “the Access Way”. And that
leads me to my final thought. There’s nothing heroic about writing wads of code
to compensate for a poorly designed table schema or an elaborate interface.
