Friday, June 24, 2016

Just Say "No-Rmalization"

It never ceases to amaze me.

The other day, we got a question over at UtterAccess about adding a Total column to an existing query.

In chasing that one down the rabbit hole, we finally learned that there were not one, but two, different tables involved, one for each Fiscal Year on which reporting was required. That's a basic table design problem right there, but it got worse. The earlier table, named FY16 had three fields, Department, Month and "Amount". (I never did learn what that Amount represented, but I suspect a Budget, since I've seen and built FY Budget tables before.) The later table had 14 fields, Deparment, "Amount", "January", "February", etc.

Altogether now, a collective groan so we can move on.

Okay, feel better?

So the original question was how to add that "Totals" column to a query representing the monthly amounts by department. We also didn't get an explanation of how they intended to combine the two FY tables, but, hey, that's the next step.

I pointed out, of course, that this represents two different design flaws, and that getting the required "totals" would be duck soup, or the equivalent in chickens, if the underlying table flaws were fixed.

I also refrained from preaching the gospel of normalization. I've come to the conclusion that too many people don't want to hear it. It DOES sound preachy to say "You've violated two of the Rules of Normalization. Repent!"

No, I try to point out a "more flexible, expandable way" to design and build tables and hope they take it onboard.

Sooner or later, they get it or they give up. Either way, the world is happier place, IMNSHO.