don94403

Well, somebody's gotta get this new blogging system off the ground, so it might as well be me.

Many of you already know that I'm notorious for ranting about table structure, normalization, and related design issues, even when some innocent soul asks a perfectly simple question like, "How can I search for addresses that might be either in Address1 or Address2 fields?" Nothing in there about how the table is designed. Just a simple question, right?

I've been designing databases since about 1972 (well, that's stretching it a bit, the early days couldn't honestly be called "designing", but I did work with programmers who were coding crude algorithms on mainframes to cope with inventory and similar problems). But at least I've been around the track quite a few times. I also taught database classes (dBASE and later MS Access) at community colleges for about ten years.

The one common thread that runs through the vast majority of issues, from my observation, is the structure of the data in database tables. Beginners (and some others!) too often speak in terms of "I think it should be this way."

Folks, the whole concept of relational databases is to organize the data in tables, using well established rules, NOT by stuffing fields in tables according to what "seems right" to somebody.

A lot of people are not comfortable with abstract views of a project, but that is precisely what is required to assure a good design that will continue to serve its purpose as time goes by and requirements change.

I once took a week-long seminar from Chris Date, who collaborated with Dr. E. F. Codd and published the seminal reference volumes on the relational database model, which are now in their 8th edition, I believe, and used worldwide. OK, I'm bragging, but I did have that opportunity and I have been an evangelist for proper database design ever since.

So what does all this mean? In my opinion, it means that every database project should begin with identifying the scope of the data model and the entities that will be represented in the model, and then the attributes of those entities. Until this step has been completed, it is useless to even discuss reports or forms.

I'm not a fanatic on normalization. Even Chris Date would be quick to acknowledge that most practical databases do not conform to all the levels of normalization. The important point is that the analysis in terms of normalization is critical to understanding where you may want to relax the rules to meet certain operating demands. It is one thing to analyze your database schema and say, "I will consciously decide to denormalize these specific parts of the data, having considered all the factors." It is entirely different to say, "Ahh, I don't worry about that theoretical stuff, this arrangement looks right to me."

So that's my rant for today. I'll think of something else to stir up the dust, next time.