Transact-SQL Programming with CDROM

Chapter 2: Database Analysis and Design

Design is concerned with finding an efficient solution to an identified problem. During the design phase, you'll finalize decisions about the technology platform, the database product, and the chosen development tools. Since these decisions impose limits on the proposed system, the designer is faced with the challenge of finding an innovative and efficient solution. This solution must work against a backdrop of real constraints that have been described in the analysis phase deliverables. In many respects, design is about juggling a series of trade-offs into an optimal configuration. As with analysis, there are certain tasks we can perform to minimize the possibility of failure. Unfortunately, there is always an element of risk in any project, but if analysis has been thorough, and if competent designers are performing this phase of the life cycle, the risk is minimal.

These are the basic design tasks:

• Applying a design method
• Getting a sense of the amount of data and the user load the system will need to manage
• Understanding the technology that will be used
• Redesigning an existing system or developing a new design
• Applying a Design Method

It is equally important to select an appropriate method for design. Many organizations have a preference about what sort of design method is used, although in some cases, you may be lucky enough to be able to define a method of your choosing. Design methods are similar to coding standards; you need to have them but you need to temper their implementation with common sense. For a fuller discussion of coding standards, refer to Chapter 5, Format and Style.

Current design methods cover some of the following topics:

• Distributed processing versus centralized processing
• Mainframe versus client/server, intranet, or Internet-based application
• Object-oriented databases versus relational databases
• Structured systems design versus object-oriented design
• Unix versus Windows NT

Each of these methods is designed to encourage heated debates since each has its own merits. However, sometimes one approach is more appropriate than another for a particular environment. As the designer, your responsibility is to weigh the relative merits of each approach and decide which one is more appropriate for the particular system being designed. Each organization has a different set of operating conditions. Therefore, a design strategy that proves successful in one organization may not necessarily be successful in another. Always bear in mind the unique conditions of the organization when you evaluate different methods.

As a designer, you need to be aware of the major principles that underlie each of these individual approaches and determine which of the approaches (or combination) is more appropriate to your particular scenario. Your decision will invariably be swayed in a particular direction as a result of the information you gather from the other design tasks.

Getting a Sense of the Amount of Data

Data is the foundation of every system. For a system to have a purpose, it must take in inputs of some form or another and produce a series of outputs. The data in a system may be stored in a database or, heaven forbid, in a flat file. The amount of data the system is responsible for and the processing to be performed on this data determine the appropriate database to use. Even with a flexible budget, you can still have overkill, which may serve to inhibit performance for your particular environment.

Understanding your data volume is critical when it comes to designing a database and configuring it for optimal performance. Knowing this information before you begin the design process enables you to consider a more appropriate implementation strategy once you get to a physical design.

It is also critical to be aware of the volume of users who are going to be using this system. Very few systems are designed for single users. A database design may need to be modified if there are large numbers of users who consistently access the same data, since this may cause problems like blocking and user contention. Chapter 11, Transactions and Locking, discusses these issues in detail.

Understanding the Technology

Many organizations have preferred vendors who supply their hardware and software, and these vendors cannot be changed. During the design phase, you need to identify what technology infrastructure is already in place. Upper management usually chooses these vendors, which means that you will have to implement a permutation of a familiar theme that has been previously implemented.

Technology changes rapidly, and one of the biggest issues that usually has to be addressed here is making a purchasing decision for a solution that may not actually exist for a long period of time. This happens when the development phase may take years to complete. One of the approaches that has been adopted in many organizations is the use of a prototype solution. Typically, a scaled-down version of the final solution is implemented using only key components of the eventual system, In following this type of approach, it is possible to do a proof of concept that can (if successful) engender the support required to get the budget approval needed for this particular project.

It is highly unlikely that you will need to have everything from a technology infrastructure standpoint before you can begin the development phase. Often, it is possible to grow your technology requirements in tandem with your implementation schedule; a multiphased implementation schedule is not uncommon in systems development.

Analysis and design are critical steps in any systems life cycle. Numerous studies have proven that the cost of change increases exponentially as you step through each stage in the traditional life cycle. A change you identify and implement in the analysis phase could save you hundreds or even thousands of dollars over finding it out in the coding stage.

All too often, analysis and design are left by the wayside and regarded as costly and unnecessary. But in reality, a well-designed system is not an unreachable goal. There are many systems in every organization that are living examples of this.

Redesigning an Existing System or Developing a New Design

Designing a new system is a lot easier than retrofitting an existing system into a new design. Often, a system will already exist, and the new system that is being developed will be required to utilize components of the existing system (for better or for worse). As a system designer, during the design process you need to identify what has to remain and what can go. Based on these decisions, you can determine how you integrate these components without compromising your design.

Before you begin your design process, take an inventory of what components are in the current system and what components are going to need to stay. Prioritize these components as critical, probable, and not sure. After you have completed this inventory, you will have two basic paths you can follow based on this inventory:

• Design your new system as if the checklist didn't exist; then, once you have a perfect design, focus on how you incorporate these components into your new design.
• Design your new system, taking those components into consideration and designing your system around them....