Whenever I get into a discussion about database standards I invariably bring up one of my favorite quotes on the topic: “The best thing about standards is that there are so many to choose from.”
It shouldn’t be true, but it is. I mean, a standard is supposed to be a rule or principle that is used as a basis for judgment - so how can there be multiple standards? Well, standards are written by people, and as we all know, everyone has their own opinion about how things should be done.
There may be dozens of standards to choose from, and if this is the case, what chance do you have as a DBA to create reasonable usage and management standards for your organization? Nevertheless corporate standards and procedures for using database systems are important for ensuring smooth operational applications and systems.
Database standards and procedures should be developed by the DBA and stored together in a central place. The database standards and procedures should be a component of corporate-wide IT standards and procedures. They can be stored in an online format for easy access, as a printed document, or in both ways.
But what topics should be covered in a set of shop database standards and procedures? I don’t want to get into an in-depth discussion of how to develop database standards, but you should include things like naming conventions, SQL coding guidelines, application migration and turnover procedures, backup/recovery standards, contingency planning, security and authorization procedures, and a matrix of roles and responsibilities, at a minimum.
So there you go, you’ve created your standards and procedures, put them up on the corporate intranet, and educated your users on them. All is done and you can take a rest, right? Wrong.
One of the biggest problems with standards is that they become stale. You have to constantly review and revise your standards as the industry changes, as the DBMS is migrated to new releases, and as your organization adopts new coding and development practices. This can be time-consuming. But perhaps the biggest reason that standards fail is that they are no longer relevant. Written 5 years ago – or even longer – and life and technology have left them behind. Don’t let this happen to your standards!
If your standards are not living and breathing entities that change with the times, then you’ll inevitably end up with bad standards! Although standards can help to ensure reliable and efficient databases, a rule that made sense 5 or more years ago probably is no longer reasonable. Every standard at your site should be reviewed at least annually to determine whether it still makes sense.
So what would be an example of a bad standard? Well, a good example is placing an artificial limit on indexing. Do not create an arbitrary limit on the number of indexes that can be created per table. Relational optimizers rely on indexes to help establish efficient queries. Without indexes, data must be scanned – and that can be a very inefficient means by which to retrieve your data. Standards of this nature are usually written something like this: “Each table can have at most 5 indexes created for it” – or – “Do not create more than 3 indexes for any single table in the database.” These are bad 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?
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 to impede the efficiency of data modification.” Now that is a good standard! But most standards are not written that way because they are not very easy to enforce.
How about another bad database standard? One of the simplest mistakes made by SQL programmers is including too many columns in the SQL SELECT-list. The only columns that should be included in the SELECT-list are those that are absolutely needed to meet the business requirements of the query.
Sometimes SQL standards specify a rule like “Avoid SELECT *”. That actually is not a bad standard for programming. Using SELECT * in your application programs is not a good idea because a column can be added to the table at a future point-in-time thereby causing your program to fail if it is not modified.
But just avoiding SELECT * does not go far enough because it does not capture the true requirement, which is this: Reference only exactly what is needed for the business requirement and nothing more. With this in mind, a bad standard may read something like this: Every column referenced in a WHERE clause of your SQL statement(s) should also be included in the SELECT-list of that statement. I’ve seen such a standard out there in the wild – and it makes absolutely zero sense. Why? Well, consider the following statement:
SELECT FIRSTNAME, LASTNAME, EMPNO
FROM EMPLOYEE
WHERE EMPNO = ’700′;
At first glance you might ask “What is so wrong with that statement?” Well, there is no reason for EMPNO to be in the SELECT-list. We know its value will always be 700 because of the WHERE clause. By including it, we are asking for the database to return data we already know, and that will impact performance.
The Bottom Line on Standards
The bottom line is that you should take care when writing your shop database standards, be flexible in terms of policing them, and review your standards and remove the ones that don’t make any sense. And plan on doing that every year … sort of like a spring cleaning for your standards… you’ll be glad you did.