IT Management

For most DBAs, the question, "Do application requirements affect the database design?" has an obvious answer: Of course!

Regrettably, important things are forgotten or ignored during data modeling and database design. This article discusses common post-implementation issues that can be avoided by giving them proper consideration during the design process.

Database Design Practices

Database design flows from business rules, application requirements, and your data modeling standards such as normalization and referential integrity enforcement.

What about a set of tables implemented to augment a current batch system? These will probably be fully normalized per current database design standards. Is this a new database in an enterprise data warehouse? Most likely, the design will include fact, dimension, and key tables with star join access paths. Will this new data support a mission-critical application with tens of thousands of users? Perhaps there will be some denormalization of the design for performance reasons.

DBAs tend to design databases based primarily on functionality and performance. Will the database support the business rules of the application? Will it perform based on the Service-Level Agreement (SLA)?

This prioritization is backward. There are just as many, if not more, important issues that should be addressed during database design. Factors such as recoverability and data availability are either missed or given lower priority.

After implementation, when recovery and availability issues arise, DBAs and application support staff find themselves in the unenviable position of considering a database re-design—usually accompanied by an extended application outage.

By prioritizing these issues in order of their importance during the database design phase, we can greatly reduce the need for re-design after implementation.

Following are some common post-implementation issues broken into sections corresponding to the laws of database administration (see "The Laws of Database Administration" by Lockwood Lyon, which appeared in the October/November 2006 issue of z/Journal, at Database design practices that respect the first law (data must be recoverable) come first, followed by the remaining laws in priority order.

4 Pages