DataZulu

If supporting foreseeable business changes requires adding tables or columns to your database, the design is deficient.

Over the years I have learned many principles regarding database design. Some I discovered on my own, usually through my own mistakes and pain. Some passed to me through a mentor or seminar. But the very first of all these is still in my Top Ten list of database design ideas:

If supporting foreseeable business changes requires changing the structure of your database, you have a problem with the design.

The database design should not have to change when obvious expected things happen. Things like adding a new customer, or updating a product's price, or . . . well, August.

This nugget came to me from a coworker in my first position where I dealt with databases although I don't think he realized it at the time. I had recently migrated an Excel-centric department reporting application over to Microsoft Access 2.0. I gained a bit of notoriety in the division as having some expertise with Microsoft Office in general and others frequently asked for tips or help troubleshooting.

So a co-worker asks me one day, "Eric, is there a limit to the number of tables you can have in a UNION query?"

"Um..."

I didn't know! At first I was somewhat flustered that I didn't know. But then I also couldn't think of a scenario where it would matter too much. So I responded:

"Why do you ask?"

He drew my attention to his monitor where he pointed at the tables in his database, a work-in-progress. They were:

As dense as I can be, I could even see where this was going.

So I introduced him to my friend, the "date" type and showed how, with a simple yet magical column called "EffectiveMonth," one big table can actually store all these results and you can just flag each one by what month the results were for.

"Oh." He paused, bewildered. "Wow, that's neat!"

Indeed it is.

I have since come to believe this principle is one of the clearest marks of good database design. Or good software design in general. Think about likely changes and build a structure that won't make the obvious changes a nightmare to handle.

A "senior" developer I worked with more recently in my career was fond of saying, "You can't anticipate future requirements." I argue instead: You must anticipate future requirements if you call yourself a professional. Otherwise, you are just a typist who knows some syntax.

(As an aside, check out what has to be the worst example of this I have seen to date.)

Database Design abstraction, normalization, axioms