Once a DBMS has been chosen and installed, before it can be used effectively standards and procedures must be developed for database usage. Studies have shown that companies with high levels of standardization can reduce the cost of supporting end users by 35 percent or more as compared to companies with low levels of standardization.

Standards are common practices that ensure the consistency and effectiveness of the database environment, such as database naming conventions. Procedures are scripts that direct the processes required for handling specific events, such as a disaster recovery plan. Failure to implement database usage standards and procedures will result in a database environment that is confusing and difficult to manage.

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 as a printed document, in an online format for easy access, or in both ways. Several vendors offer "canned" standards and procedures that can be purchased for specific DBMS products.

Database Naming Conventions

One of the first standards to be implemented should be a set of guidelines for the naming of database objects. Without standard database object naming conventions, it will be difficult to identify database objects correctly and to perform the proper administration tasks.

Database object naming standards should be developed in conjunction with all other IT naming standards in your organization. Wherever possible, the database naming conventions should peacefully coexist with other IT standards, but not at the expense of impairing the database environment. For example, many organizations have shop standard conventions for naming files. The DBMS maps database objects to operating system files or data sets. To coordinate the database object to the operating system file may require a specific format for database file names that does not conform to the shop standards therefore, it may be necessary to make exceptions to existing shop file naming standards for database files.

Be sure to create and publish naming standards for all database objects that can be created within each DBMS used by your organization.

In general, do not impose unnecessary restrictions on the names of objects accessed by end users. Relational databases are supposed to be user-friendly. A strict database naming convention, if not developed logically, can be antithetical to a useful and effective database environment. Some organizations impose arbitrary length limitations on database tables, for example an 8 byte limit even though the DBMS can support up to 128 byte table names. There is no practical reason to impose a limitation that prohibits the length of database table names.

Standard Abbreviations

Deploy standard conventions that will make database object names as English-like as possible. However, you will inevitably encounter situations that require abbreviations. Abbreviations should be used only when the full English text is too long to be supported as a database object name or when the full text renders the object name unwieldy or difficult to remember. Create a list of standard abbreviations that must be used whenever an abbreviation is required and forbid non-standard abbreviations from being used. For example, if ORG is the standard abbreviation for ORGANIZATION do not allow other formulations (such as ORGZ) to be used. Using standard abbreviations will minimize mistyping and make it easier for users to remember database object names. Adhering to this practice will make it easier to understand the database objects within your environment.

Other Database Standards and Procedures

Though database object naming standards are important, you will need to develop and maintain other types of database usage standards. Be sure to develop a comprehensive set of standards and procedures for each DBMS used by your organization. Each of the following areas should be covered in your DBMS standards and procedures guide.

Roles and Responsibilities

The successful operation of a DBMS requires the coordinated management efforts of many skilled technicians and business experts. A matrix of database management and administration functions should be developed that documents each support task and who within the organization provides the support. The matrix can be created at a departmental level, a job description level, or even by individual name. A sample roles and responsibilities matrix is shown in Table 1. An “X” in the matrix indicates involvement in the process, whereas a “P” indicates primary responsibility.

Table 1. Database Support Roles and Responsibilities.

Task

DBA

DA

SA

Management

Operations

Applications

End Users

DBMS Budget

X

 

X

P

 

X

X

DBMS Installation

P

 

X

 

X

X

X

DBMS Upgrade

P

 

X

X

X

X

X

Database Usage Policy

P

X

 

X

 

 

 

Capacity Planning

X

 

P

X

X

X

 

Data Modeling & Analysis

X

P

 

 

 

 

X

Metadata Policy

X

P

 

 

 

 

 

Governance and compliance

X

X

X

X

 

 

P

Database Design

P

X

 

 

 

X

 

Database Creation

P

 

 

 

 

 

 

System Performance

X

 

P

 

 

 

 

Database Performance

P

 

X

 

 

X

 

Application Performance

X

 

X

 

 

P

 

Backup & Recovery

P

 

X

 

X

X

 

Disaster Recovery

P

 

X

 

X

 

 

Database Security

P

 

X

 

X

 

 

Stored Procedures

X

 

 

 

 

P

 

Triggers

P

 

 

 

 

X

 

User-defined Functions

X

 

 

 

 

P

 

Application Design

X

 

 

 

 

P

 

Application Turnover

X

 

 

 

X

P

X

App Design Reviews

X

X

 

X

X

P

X

 

Of course, you can create whatever tasks you deem necessary in your roles and responsibilities matrix. You may need additional tasks or fewer than in this sample. For example, you may wish to differentiate between stored procedure development, testing and management, by creating a different task category for each and breaking down the support requirements differently for each.

Whatever the final composition of your roles and responsibilities matrix, be sure to maintain it by keeping it accurate and up-to-date with new DBMS features and tasks. An up-to-date matrix such as this one makes it easier to define roles within the organization and to effectively apportion database-related workload.

DBA Administration Standards

A basic set of DBA standards should be established to ensure the ongoing success of the DBA function. The standards will serve as a guide to the DBA services offered and specific approaches taken to support the database environment. For example, standards can be developed that outline how requests are made to create a new database or make changes to existing databases, specify which types of database objects and DBMS features are favored and under which circumstances to deviate, establish backup and recovery procedures (including disaster recovery plans) and communicate the methods used to transform a logical data model into a physical database implementation. An additional set of DBA standards that cover database performance monitoring and tuning may be useful to document procedures for overcoming performance problems.

Although the DBA standards will be most useful for the DBA staff, the application development staff will need them to learn how best to work with the DBA staff. Furthermore, any performance tuning tricks that are documented in the DBA standards should be shared with programmers, too. The more the application programmers understand the nuances of the DBMS and the role of the DBA, the better the working relationship between DBA and development will be – and that should result in a more efficient database environment.

Standards should also be developed for Data Administration, System Administration, Database Development, and Operational Support functions.

Database Security Standards

The DBA unit often applies and administers DBMS security. However, at some shops, the corporate data security unit handles DBMS security. You should provide a resource outlining the necessary standards and procedures for administering database security. It should contain the following information:

  • Details on what authority to grant for specific types of situations. For example, if a program is being migrated to production status, what DBMS authorization must be granted before the program will operate successfully in production.
  • Specific documentation as to any special procedures or documentation required for governance and compliance related requests.
  • An authoritative list of who can approve what types of database authorization requests.
  • Information on any interfaces being used to connect DBMS security with operating system security products.
  • Policies on the use of the WITH GRANT OPTION clause of the SQL GRANT statement and how cascading REVOKEs are to be handled.
  • Procedures for notifying the requester that database security has been granted.
  • Procedures for removing security from retiring, relocating, and terminated employees.

Application Migration and Turnover Procedures

The minimum number of environments for supporting database applications is two: test and production. Some organizations, however, create multiple environments. For example, different environments can be created to support different phases of the development life cycle, including:

Unit testing – for developing and testing individual programs.

Integration testing – for testing how individual program interoperate.

User acceptance testing – for end user testing prior to production status.

Quality assurance – for shaking out program bugs.

Education – for training end users how to work the application system.

When multiple environments exist, procedures are required for migrating database objects and moving programs from environment to environment. Specific guidelines are needed to accomplish migration in a manner conducive to the usage of each environment. For example, what data volume is required for each environment and how is data integrity to be assured when testing activity occurs? Should data be migrated, or just the database structures? How should existing data in the target environment be treated – should it be kept or overlaid with new data? Comprehensive migration procedures should be developed to address these types of questions.

The migration and turnover procedures should document the information required before any database object or program can be migrated from one environment to the next. At a minimum, information will be required about the requester, why and when the objects should be migrated, and the appropriate authorization to approve the migration. As a part of the migration procedure, the implementer of the request should document the methods used to migrate and record the verification process to ensure the success of the migration.

Design Review Guidelines

All database applications should be subjected to a design review at various stages of their development. Design reviews are important to ensure proper application design, construction, and performance. Design reviews can take many forms, but there are seven basic design review phases that should be conducted for database applications:

  • Conceptual Design Review - to validate the concept of the data and proposed application;
  • Logical Design Review - a thorough review of all data elements, descriptions, and relationships, as well as comparison to and possibly remediation of the corporate data model;
  • Physical Design Review - the database is reviewed in detail to ensure that all of the proper database parameter settings and other physical design choices were made, that a proper translation from logical model to physical database was made and that all denormalization decisions were formally documented;
  • Organizational Design Review - to examine the impact of the new application/database upon the organization from a business and technological perspective;
  • SQL and Application Code Review - a rigorous statement-by-statement review of every SQL statement in the application with an eye toward accuracy and performance;
  • Pre-Implementation Design Review - an overall appraisal of the system components prior to implementation;
  • Post-Implementation Design Review - formally review the application and database once it has run in production for awhile to determine if the application is meeting its objectives.

Summary                                                                                                

Without appropriate standards it can be challenging to appropriately implement and use a DBMS within your organization. Standards help to guide development and minimize problems. Be sure that you have developed reasonable shop standards for your DBMS environments.