Your Proposed Relational Database Table Design is Inappropriate

During many years of participation at UtterAccess.com, I've had a chance to review many, many "First-Timer databases". After a promising beginning, these new comers run into a blocker like a query that doesn't return the right records, or a form that won't accept new records, or a report that doesn't work the way they want. They run into a puzzling or challenging problem which seems like it ought to be simple. Instead they get stuck and have to turn to online forums like ours for help. We actually love that, because it gives us a chance to help them clean things up and get off to a better start. But all too often, I have to tell new members, "You don't have a query problem. You have an inappropriate table design." Or "You don't have a report problem or a form problem.Your tables are not appropriately designed. Put the forms on hold while you fix your tables."

I admit "inappropriate table design" is a euphemism for the more blunt assessments I'm tempted to state. I favor it over "you have created non-normalized tables", or worse, "Your table design is wrong." Here's why.

Often, the limited information we get as part of the question reveals the underlying tables are not designed according to the Rules of Normalization, but that's not the question originally asked by the newcomer. Instead of offering SQL or VBA to work around the problem, we have to start out questioning the very basis of the database application itself--The tables.

Ouch. No one likes to hear that they broke the rules, especially when they didn't even know those rules exist. Moreover, Access readily, and even gleefully, aids and abets the creation of those inappropriate, non-normalized tables. Sometimes I can hear the newcomer asking their computer screen, "Who is this George Hepworth guy" they wonder, "to second-guess Microsoft?  They let me do it this way, so it can't be wrong, can it?"

Well, yes, there are many ways a table design can be wrong, and all too often it is wrong the first time. It's my responsibility, I think, to let the new comer know when their first step leads down the path to the swamp, not the garden.

Anyway, recently I've been wondering if maybe my approach is on the right level.

Am I too harsh in telling new members their query doesn't work because it's based on inappropriate, i.e. poorly designed, tables that need to be changed?

Or is calling it "an inappropriate table design" too subtle ? Should I be stating outright that the table design is wrong and doesn't make sense? Should the new comer have a dose of shock therapy the first day? Or maybe I should ignore the problems at first and try to offer a query that works with the flawed tables  in hopes we can fix the real problem later? Maybe this is really just a minor thing that doesn't matter all that much.

Time after time I have come to the same conclusion.

In the long run, ignoring flaws in a table design means the problems are only going to get bigger, more intractable and even become risky. Members at UtterAccess and other forums come up with all sorts of metaphors, usually trite and well-worn, to explain that concern.

• It's like putting a band-aid on a broken leg. 
• It's like asking your doctor to give you aspirin for a broken leg. 
• It's like putting up walls on bare dirt without a solid foundation. 

 And on and on. They all boil down to the same thing: work-arounds don't work in the long run. In the short-run, sure, you can force Access to work with a poorly designed table architecture. The result is fragile, inflexible and inefficient. I like to point out that there's nothing heroic about creating massively complex queries or wads of code to compensate for an inadequate table design. Sort of like putting in flying buttresses on a garage wall to make up for leaving out the foundation.

As one of my favorite people used to say, "Let's face it. We have a problem.  I know what it is, and it's not a query problem. Your table design is inappropriate."

Let's fix that.