Bad Database Standards, Part 1

With this blog entry I start a new series discussing bad database standards. Almost every DBA group keeps a database standards manual – but most do not keep it up-to-date. I’ll try to tackle some of the more popular standards that should be done away with.

Just about every company with a DBMS has that binder full of corporate and/or IT standards. You know, it is that one over there in the corner with the cobwebs on it? The one that you only use when you need an excuse to avoid work… OK, well, maybe its not quite that bad. Your standards documents could be on the company intranet or some other online mechanism (but chances are there will be virtual cobwebs on your online standards manuals, too).

How do standards become standards? Well, it is like some wise sage said several years ago: “The nice thing about standards is that there are so many to choose from!”

Basically, what happens is that some well-meaning authority comes up with a “rule” or “guideline” that makes sense at some point – and then decides to enshrine it for eternity in the standards manual. Now don’t get me wrong, company standards can be a very good thing. It is the eternity part that I take exception with. Standards need to be a living, breathing “thing” that change with the times.

You see, standards can be worthwhile as a measuring stick to work from, hopefully ensuring that reliable and efficient databases and applications are built in a standard manner. But a rule that made sense 10 or 20 years ago probably is no longer reasonable. Every standard in your book should be reviewed at least annually to determine whether it is still reasonable to enforce.

One such rule is today’s topic, and if a form of it still exists in your standards manual drop everything you were going to do today and expunge it immediately from your standards book.

1. Limits on Indexing

There should be no arbitrary limit on the number of indexes that you can create for any database table. Indexes are undoubtedly one of the most important factors in creating efficient queries. Relational optimizers rely on indexes to build fast access paths to data. Without indexes data must be scanned – and that can be a long, inefficient means by which to retrieve your data. When a rule such as this exists, it usually is stated in the standards manual using verbage something like this:

  1. “Each table can have at most 5 indexes created for it” – or –
  2. “Do not create more than 3 indexes for any single table in the database.”

These are bad standards. Very bad standards. Horrible standards.

If you already have 3 indexes, or 5 indexes, or even 32 indexes, and another index will improve performance why would you arbitrarily want to avoid creating that index?

Anyway, a good indexing standard, if you choose to have one, should read something like this: “Create indexes as necessary to support your database queries. Limitations on creating new indexes should only be entertained when they begin significantly to impede the efficiency of data modification.”

Now that is a good standard!

But most standards do not read that way because they are not easy to impose without arbitrary numbers and restrictions embedded within them.

I intend to take on other nasty standards that should be eliminated in subsequent blog entries here. Please feel free to e-mail me your favorites (or perhaps I should say least favorites) standards that should be eliminated.

Or leave a comment with your least favorite standards below.

Advertisements

About craig@craigsmullins.com

I'm a strategist, researcher, and consultant with nearly three decades of experience in all facets of database systems development.
This entry was posted in DBA, standards and tagged . Bookmark the permalink.

2 Responses to Bad Database Standards, Part 1

  1. MarcTun says:

    significantly? when is that? The point of many standards is to get ahead of the game not to react after the things gone bad.

    • Well, if you can’t live with a word like “significantly” then you shouldn’t have a standard about number of indexes! You simply can NOT say only 5 indexes… or only 12 indexes per table. A lot depends on things like amount of data, type of access, how frequently the data changes (insert, update, delete), and you cannot boil that down into a pithy little boilerplate standard.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s