How many times I have said, in this newsletter and at conferences, that “If you get the database design right, everything else is easy.” Yet, despite that, Iwas working on a project for a client in the American Southwest and I failed to follow my own advice. If nothing else, the following story may serve as an object lesson to you about the perils of poor database design.
We had started with the customer’s “straw man” application. It was actually a pretty good application in its own right and the data design was just about perfect. There was one table that bugged me, though I couldn’t put my finger on why. The table listed a set of relationships between two objects in the design, including the dates that the objects were assigned to each other and the dates that they were unassigned. However, I couldn’t see any particular problem that this design would create with the application.
As I worked on the project, I continued to chew on this table. I eventually came to realize what the problem was. Because of the design, there could be multiple records in the table for any particular object:
• The current assignment, where the unassigned date would be blank.
• The historical records, where the unassigned date would be set to some value.
Expressed this way, it was obvious that I had two separate entities shoved into a single table. There really should be one table of current assignments. In this table, there would be an assignment date (when the two objects were brought together). There should also be a historical table that looked just like the original table except that it would also have an “un-assignment” date. When a relationship between the two objects was broken, I should copy the current record from the current assignment table to the historical table and set the “un-assignment” date. I don’t know which normalization rule I was violating but I’m sure I was violating at least a couple. I really had no excuse for not following up with this table’s design. My client even called me up at one point and expressed some doubt about whether this table was fully normalized. I told him that I still didn’t see any problems that this design would create for us. So I plunged on, entombing this design (which, by now, I knew wasn’t right) in more and more code. I did notice that my logic was more complex than I had originally thought it would be. But what the heck: I was paid by the hour, and I could throw in a few more comments to explain what was going on.
We eventually had the application about 90 percent complete and entered beta testing. One of the first things my client did was load up the system with enough data to reflect the worst-case scenario. Response time on three major forms in the system went to three to four minutes (it may have been longer—I’m probably in denial). The problem was my unfortunate table: Finding the current record (the one with the unassigned date) amongst all the other records was slowing the whole application down.
I had no choice. I broke the table up into two and rewrote the three slow forms to use the new design. The results were remarkable (even to me). All three forms now had sub-second response time. The new code was, of course, considerably simpler. In good conscience, I couldn’t charge my client for the two or three extra days (fortunately, I was far enough ahead of schedule that I had two or three days to spare). My client had the good graces not to gloat.
So I’ll say it here again: “Get the database design right and everything else will be easy (and fast). Get the database design wrong, make sure that you’re paid by the hour.”
My experience probably accounts for some of this issue’s contents. Andrew Wrigley has more specific guidance than this editorial on how to ensure that you do have the right database design. Frank Kegley provides a process for moving from badly designed Excel tables to well-designed Access tables. Doug Steele’s “Access Answers” column this month is heavy on the SQL but discusses (in passing) how the obvious database design may not be the right one. Rebecca Riordan’s “Access Interfaces” column picks up on one of Doug’s points about data design and rolls it into a form. And, for the first time in a long while, I got to write an article for Smart Access.