The 7 Types of Database Design Reviews

A little more than two years ago I wrote about The Importance of Database Design Reviews here on this blog. The general idea of that post is that reviewing the scope and content of a database implementation before it moves to production is an important aspect of database systems and application development. By conducting database design reviews you can validate your assumptions and ensure that requirements are being met. And furthermore, you can identify and remediate problems before the system/app becomes operational.

Today’s blog post will examine the seven different types of design reviews and outline their importance. But before we delve into describing these design reviews, let’s first define the necessary participants:

The personnel who should engage in the design review process follow:

  • Application Development personnel assigned to this development effort [AD]
  • Representatives from other applications that are affected by this new application that is being reviewed(due to the need to interface with the new application, shared data requirements, scheduling needs, etc) [AA]
  • Data Administration representative [DA]
  • Database Administration representative [DBA]
  • End users representatives [EU]
  • End User Management [EUM]
  • Information Center representative [IC]
  • IT Management for the new application & all affected applications [ITM]
  • On-Line Support representative (transaction unit, e.g. web/CICS) [OLS]
  • Operational Support Management  [OS]
  • Technical Support and Systems Programming representatives [TS]

It is not necessary for each of these participants to partake in each and every facet of all 7 design reviews.  The scope of each design review should be determined prior to the scheduling of the review such that only the appropriate participants need be invited.

Let’s look at each of the seven design review phases:

The Conceptual Design Review

The first phase should be the Conceptual Design Review (CDR).  The purpose of this review is to validate the application concept.  This involves a presentation of the statement of purpose as well as a general overview of the desired functionality that will be provided by the application.  A CDR should be conducted as early as possible to determine the overall feasibility of a project.  Failure to conduct a CDR can result in:

  • projects which provide duplicate or inadequate functionality.
  • projects which are cancelled due to lack of funds, staffing, planning, user participation, and/or management interest
  • over-budget projects

Participants should include:  AA, AD, DA, DBA, EU, EUM, ITM.

The Logical Design Review

The second phase is the Logical Design Review (LDR).  This should be conducted when the first cut of the logical data model has been completed.  A thorough review of all data elements, descriptions, and relationships should occur during the LDR.  The LDR should scrutinize the following areas:

  • Has the logical model been thoroughly examined to ensure that all of the required business functionality can be achieved based solely upon the model?
  • Is the model in (at least) third normal form?
  • Have all of the data elements (entities and attributes) required for this application been identified?
  • Have the data elements which have been identified been documented accurately?
  • Have all of the relationships been defined properly?

Failure to hold an LDR can result in a poorly designed database, a failure to identify all required pieces of data, and a lack of documentation causing an application to be developed which is difficult to maintain.  If further data modeling occurs after the logical design review is held, further LDRs can be scheduled as the project progresses. 

Participants should include:  AA, AD, DA, DBA, EU, EUM, IC.

The Physical Design Review

The Physical Design Review is the third design review phase.  It is this component which most DB2 developers associate with the design review process.  This is where the database is reviewed in detail to ensure that all of the proper design choices were made.  In addition, the DA and DBA should ensure that a proper translation from logical to physical was made including conformance to standards, mapping to appropriate database objects, and performance tweaking for physical DBMS issues. Additionally,  all denormalization decisions are to be documented for posterity. 

The overall operating environment for the application should be described and verified at this stage.  The choice of transaction processing (CICS, IMS/DC, Tuxedo, web, etc.) and a complete description of the on-line environment should be provided.  This should include the number of transactions and the anticipated workload.  Correspondingly, a complete description of any batch processes should also be provided. 

At this stage, all of the SQL that will be used for this application may not be available.  General descriptions of the processes required, however, should be available.  From the process descriptions, a first-cut denormalization effort (if required at all) should be either attempted or verified.

As the PDR phase requires a lot of in-depth attention, it can be broken into discrete pieces if so desired.  The PDR, or pieces thereof, can also be done more than once prior to implementation if significant changes occur to the physical design of the database or application.

Participants should include:  AA, AD, DA, DBA, IC, OLS, OS, TS.

The Organization Design Review

Smaller in scope, but no less critical is the Organization Design Review (ODR).  This review addresses the enterprise-wide concerns of the organization with respect to the application being reviewed.  Common review points include:

  • How does this system interact with other systems in the organization?
  • Has the logical data model for this application been integrated with the enterprise data model (if one exists)?
  • To what extent can this application share the data of other applications?  To what extent can other applications share this application’s data?
  • How will this application integrate with the current production environment in terms of DB2 resources required?  batch window?  on-line response time?  availability?
  • Will the implementation of this application cause the data processing needs of the shop to expand?  For example, will more memory, CPU power, or storage be necessary?

Participants should include:  AA, AD, DA, DBA, EU, EUM, IC, ITM, OLS, OS, TS.

The SQL Design Review

Phase 5, the SQL Design Review (SDR), must occur for each SQL statement prior to production turnover.  The SDR should consist of the following analysis:

  • An EXPLAIN should be run for each SQL statement using production statistics.  The PLAN_TABLEs should then be analyzed to determine if the most efficient access paths have been chosen.  If a plan analysis tool is available, the output from it should be analyzed, as well.
  • Every program should be reviewed to validate that inefficient host language (COBOL, Java, C, etc.) constructs were not used.  In addition, efficient SQL implemented inefficiently within loops should be analyzed for its appropriateness.
  • All dynamic SQL should be reviewed whether it is embedded in an application program or to be run through a report or query tool (e.g. Business Objects, QMF, etc.).

Suggestions for performance improvements should be made and tested prior to implementation to determine their affect.  If better performance is achieved, the SQL should be modified. The DBA should be on the lookout for opportunities to improve performance by creating new indexes (or modifying existing indexes) during this design review phase.

Participants should include:  AD, DBA, EU, IC.

The Pre-Implementation Design Review

The sixth phase is the Pre-Implementation Design Review (PreIDR).  This simply consists of an overall review of the system components prior to implementation.  Loose ends which exist from the previous five phases should be cleaned up and a final, quick review of each application component should be performed. 

Participants should include:  AA, AD, DA, DBA, EU, EUM, IC, ITM, OLS, OS, TS.

The Post-Implementation Design Review

The final phase is the Post-Implementation Design Review (PostIDR). This is necessary in order to determine if the application is meeting its objectives.  These objectives include both performance objectives and functionality objectives.  If any objective is not being met, a plan for addressing the deficiency must be proposed and acted upon.  Multiple PostIDR phases can occur. 

Participants should include:  AA, AD, DA, DBA, EU, EUM, IC, ITM, OLS, OS, TS.

Synopsis

By establishing and following a systematic approach to database application design reviews, the opportunity for implementing optimal  applications is expanded.  Database development can be very complex, and as such, successful systems can be created only by managing and documenting the implementation process.  The design review process is an efficient way to encourage a rigorous and systematic pre and post-implementation review of your database application applications.

About these ads

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 database design, indexing, normalization, performance, SQL. Bookmark the permalink.

7 Responses to The 7 Types of Database Design Reviews

  1. Pingback: DB2 Hub | The 7 Types of Database Design Reviews

  2. Pingback: The 7 Types of Database Design Reviews | Mainfr...

  3. Christian Louboutin Femme says:

    Excellent blog post, a great deal of beneficial details.

  4. AZ Jim says:

    Craig … Two things I would like to add,  First, the PDR should address the means by which the database will comply with the corporate security model.  I cannot count the number of PDRs I have completed and people are constantly surprised by who and what get read permissions, who and what get read and write permissions, and if dealing with Unix or Windows, how distributed security access will take place on the database.  It can be a big stress-reducer for DBAs to let people know up front what will be required.  Sometimes there are high level users who expect direct access to transactional data (i.e., without the use of an application).  Second, the transaction workload should be spelled out.  What is the maximum number of transactions that will be retained, what is the method of archival/purge (and quality requirements for recall if there is archival), what is the peak number of transactions that need to be processed in a certain given amount of time (second, minute, hour, or day).  For both of these two items, and this is important, make sure that the key stakeholders sign off on the document.  If they don’t, then either it doesn’t get implemented or it gets implemented with a documented organizational risk.  This latter action will prevent the witch-hunting six months later when you designed the database to hold 300 million transactions and the key stakeholders are now telling you it should have been 700 million.  I know.  This has happened to me in a previous job and I had the signed PDR to show what they signed off on. 

  5. Great points, AZ Jim… thanks for sharing your perspective.

  6. bella_whitfield says:

    First off I want to say awesome blog! I had a quick question which I’d like to ask
    if you don’t mind. I was curious to find out how you center yourself and clear your head prior to
    writing. I have had a tough time clearing my mind in getting my thoughts out.
    I do take pleasure in writing however it just
    seems like the first 10 to 15 minutes tend to be wasted just trying
    to figure out how to begin. Any suggestions or tips? Cheers!

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