Skip to main content.

Web Based Programming Tutorials

Homepage | Forum - Join the forum to discuss anything related to programming! | Programming Resources

Web Database Developer's Guide with Visual Basic 5

World Wide Web Database Developer's Guide with Visual Basic 5


-5-

Database and Site Design



Developing a Website requires designing the visual component of the site and possibly a data-base component as well. For your Website to be successful, you need to have an understanding of these components. To help you further understand these components, this chapter focuses on the following topics:

Relational Database Terminology

Knowing common relational database terminology is useful when designing a database yourself or conversing with your database administrator. The terminology discussed in the following section is common to most relational databases:


FIGURE 5.1.An example of a table.

FIGURE 5.2.An example of a row and a column.

FIGURE 5.3.Relationships among data items in a relational model.



FIGURE 5.4. An example of a view.

FIGURE 5.5.An example of a trigger.

FIGURE 5.6. An example of a stored procedure.

Relational Database Design Guidelines

A properly designed database can increase data integrity and simplify data maintenance. To help you better understand how to design a database, the following concepts are discussed in this section:

Why Normalize a Database?

The following section briefly discusses why you should normalize a database. If your Website is primarily used to display data and not to modify data, you might not encounter some of the data modification problems discussed in this section.

How to Normalize a Database Design

There are three standard normalization rules. After a design successfully passes a rule, it is said to be in # normal form (where the # represents 1st, 2nd, or 3rd). Rules are cumulative. For example, for a design to be in 3rd normal form, it must satisfy the requirements of the 3rd normal form as well as the requirements for 1st and 2nd normal forms.

Technically speaking, there are other types of normalization rules beyond 3rd normal form. However, for most database designs, the first three normal forms are sufficient. You seldom need to apply the other types of normalization. Therefore, this section concentrates only on the 1st, 2nd, and 3rd normal forms of database design.

Now that you know the rules regarding normalization, let's apply them to a sample application.

For this application example, suppose that you are tracking training classes taken by each employee. Figure 5.7 contains the data elements tracked by this application. Each employee may have taken 0 or N (zero or many) training classes, and each employee will have 0 or 1 (zero or one) manager.

FIGURE 5.7.Data elements for the sample design.

1st Normal Form Look at the training_id, training_description, and training_date attributes in Figure 5.8. See how they are repeated? This violates the concept of 1st normal form: No repeating groups.

FIGURE 5.8.Repeating groups.

You can move the training information into a separate table called employee_training and create a relationship between the employee table and the employee_training table. Now the table design meets the requirements of 1st normal form. (See Figure 5.9.)

FIGURE 5.9.Tables that meet the requirements of 1st normal form.

2nd Normal Form In Figure 5.10, notice how the training_description attribute depends only on the training_id attribute and not on the employee_id attribute in the employee_training table. This violates 2nd normal form: No nonkey attributes can depend on a portion of the primary key (the primary key for this table is employee_id + training_id). This rule is applied only to entities that have compound primary keys (a primary key consisting of more than one attribute).

FIGURE 5.10. A nonkey attribute depends on a portion of the primary key.

You can move the training_description attribute into a separate table called training. Relate the training table to the employee_training table through the training_id attribute. Now the design satisfies 2nd normal form. (See Figure 5.11.)

FIGURE 5.11.Tables that meet the requirements of 2nd normal form.

3rd Normal Form Look at the manager_name attribute for the employee table in Figure 5.12. The primary key for the employee table is the employee_id attribute. Does the manager_name attribute depend on the employee_id attribute? No! This violates 3rd normal form: No attributes can depend on other nonkey attributes.

FIGURE 5.12. An attribute depends on a nonkey attribute.

You can move the manager_name attribute into a separate table called manager. The manager table can be related to the employee table through the manager_id attribute. By making this change, the design meets the requirements of 3rd normal form. (See Figure 5.13.)

FIGURE 5.13.Tables that meet the requirements of 3rd normal form.

Now you have completed the normalization process (see Figure 5.14). This process helps isolate design flaws that can lead to an awkward and inefficient database design.

FIGURE 5.14. A normalized database design.

Denormalization

Denormalization means that you are purposely designing your database so that it is not in 3rd normal form. This is done to maximize performance or to simplify end-user reporting. Whenever you denormalize a database, you must be willing to forego the benefits gained from the 3rd normal form.


NOTE: I recommend that you start your initial database design in 3rd normal form. If you find that performance problems exist, selectively step back to 2nd or 1st normal form. Keep in mind that when you denormalize a database, you do so for a specific set of application requirements. Future requirements might not need or benefit from past denormalization decisions. In other words, only denormalize when you have to.

Performance

A database design in 3rd normal form may require more table joins to process a query than a design in 2nd or 1st normal form. These additional table joins can be expensive in terms of CPU and disk I/O.

Suppose that you need a report that lists the training classes taken by each employee. (See Figure 5.15.)

FIGURE 5.15. A sample report.


NOTE: The examples used in this section are overly simplistic; however, they do explain how multitable joins can complicate data processing. Also, if your Website is used to publish data and not to modify data, a denormalized design may provide better performance and simplify your queries.

To retrieve the data from your fully normalized database, you create the following query, which is a sample query for a fully normalized database:

SELECT a.employee_name, d.manager_name, c.training_description,b.training_date
FROM employee a, employee_training b, training c, manager d
WHERE a.emp_id = b.emp_id
 AND b.training_id = c. training_id
 AND a.manager_id = d.manager_id

As you can see, this simple report requires four tables to be joined. Assume that each table contains one million rows. Can you imagine the work involved to join four tables, each containing one million rows? You can be assured that performance will suffer.

To maximize performance, you sometimes have to step back to 2nd or 1st normal form. If you denormalized your data into a single table, you can use the following query, which is a sample query for a denormalized database:

SELECT employee_name, manager_name, training_description, training_date
FROM training_summary

Denormalization Techniques

Following is brief summary of the various techniques you can use to denormalize a database:

FIGURE 5.16.Horizontal parti-tioning.

FIGURE 5.17.Vertical partitioning.

Web-Specific Database Design Issues

Designing a Web-specific database is very similar to designing a database prior to the advent of the Internet. However, there are several issues that must be considered when designing Web-specific databases. The following is a brief list of issues relating to Web-specific databases:

Website Design and Development Considerations

When you design a Website, you are likely to encounter a myriad of problems, obstacles, and technical challenges. It is difficult to offer blanket solutions to these problems. Every site differs in its goals and objectives, the types and amounts of information it intends to serve, the number of users expected, and the composition of the development staff. To offer canned design solutions would be trying to impose a rigid structure on what is inherently a semi-structured activity. Fortunately, however, a variety of considerations and guidelines can aid you in the numerous decisions with which you will be faced.

In this chapter, we step back for a moment to present some issues related to site design. You will find that the material in this chapter does not offer many solutions but instead raises a number of questions--ones you should be asking yourself as you design a new Website or expand the capabilities of an existing site. Our intent is not to provide you with all the answers, but to invoke thought by raising some issues and providing you with the appropriate set of questions you need to answer in order to develop a successful Website.

What Is Design?

The term "design" may conjure up various meanings depending on whether you are an engineer, graphic artist, programmer, systems analyst, or database administrator. However, if you view design as a process, similarities emerge that span all disciplines.

Design entails planning and the conceiving of ideas that address certain needs. In many cases, especially with information systems design, it entails the confluence of many competing issues, the integration of efforts by personnel with multiple technical disciplines and backgrounds, and dealing with the ever-present problems of obtaining sufficient levels of resources to address the tasks at hand.

Looking at design from a system viewpoint requires an all-encompassing look at the system inputs, outputs, environment, processes, work flows, and transformation of inputs to outputs. It necessarily incorporates the contributions and effects of people ("the players") in the system, including end users, developers, managers, and technical staff. Additionally, system design looks at the existing problems (or opportunities), develops requirements and goals to address those problems and opportunities, proposes various alternative methods to implement solutions, and then examines the operational, technical, and economic feasibility of proposed alternative solutions. This entire process may be sequential, may overlap in stages to varying degrees, or may be iterative in nature. Every system design is different to varying degrees, but all incorporate aspects of the same processes.

The Players

Successful Website design and development requires a collective and cohesive effort as well as input from multiple disciplines, internal and external to the organization. Yet, too often, personnel with backgrounds that could contribute significantly to the system design are ignored or bypassed. Manufacturing companies often utilize integrated product development teams when developing a new product for market. These teams are comprised of members from all cognizant business functions and disciplines. The same concept can and should be employed when designing larger Websites. If you involve all persons with a stake in the system early on, the result is generally a better overall design.

This section takes a brief look at the roles played by various people involved in Website design. These roles vary to some degree depending on the size of the site under development and the amount of personnel resources assigned to undertake the development. In some cases, multiple roles may be undertaken by a single individual. Webmaster In earlier days, a Webmaster was seen to be the primary developer and maintainer of a Website. The Webmaster was someone with enough knowledge to establish and maintain a server, develop HTML pages, and perform some degree of system administration and handling of security issues. However, the Web is rapidly evolving from Websites that simply maintain static repositories of hypertext documents, to complex sites with extensive interactive services. These interactive services may range from serving documents, to incorporating database applications, to providing informational searches and numerous commercial activities. This has resulted in a division of labor, especially at larger sites.

Many sites now have established Web application teams with members who represent many technical disciplines. Increasingly, it is the role of the Webmaster to lead and manage the activities of the team members. Indeed, the Webmaster is becoming more of a system engineer in many respects and is the person responsible for making the final decisions on many site issues such as development schedules, design and content of documents, software and hardware to be employed, and the document style and programming standards. Of course, these decisions are not made in a vacuum, but rather with the input of the appropriate team members. End Users and Audience Your users are arguably the most important component of your Website. Presumably you would not be developing a site if you did not have an existing or potential user base to provide services and information to. In most cases, the users are the reason the Website exists, yet it is amazing how often end users are ignored during the design of systems because the design team feels that they already understand the users' needs.

It is very important to identify your audience and its informational needs. End-user involvement and feedback should be solicited during design (if possible) and after implementation to determine needs and requirements, as well as other issues such as time-sensitivity of information, available data formats, ease of use of applications at the site, and so on. Web Page Developers Web page developers are those persons responsible for the development of the more static portions of a Website (that is, HTML documents that are not generated on-the-fly). Although this may include some static forms, it typically entails documents translated from other sources and pages that establish links to other documents or applications.

In the not-too-distant past, coding HTML documents and Web pages required a fair amount of HTML knowledge and skill. With the advent of HTML and Web authoring tools, however, the technical skill level required of Web page developers has decreased dramatically, and nearly anyone with the ability to use a word processor can develop fully functional Web pages. Programmers Programmers are playing an increasingly important role in Website design and development. These folks are the ones responsible for extending the range of server-side services that can be provided by the Web server. This is done through the development of CGI programs and the use of gateway software, which allow the integration of other applications. These may include, among other things, the development of relational database applications that can be provided to users. Additionally, programmers are employing tools to improve the functionality of client-side applications. This includes extending the capabilities of Web browsers and applications through the use of Java, VBScript, and technologies such as Microsoft's ActiveX development environment. The ingenious use of these tools by Web programmers is likely to lead to the development of an entirely new generation of interactive Web applications in the very near future. System Administrator The system administrator is responsible for maintenance of the system hardware, troubleshooting network problems, backing up system and application data, and upgrading the system software. This person typically has in-depth knowledge of security, networking, and Internet issues as well as some working programming knowledge. Duties may overlap considerably with those of a conventional Webmaster, especially in the areas of Web server installation, configuration, upgrades, and maintenance. Database Administrator The database administrator (DBA) is the person responsible for the setup, maintenance, tuning, and backup of system databases. Ensuring the security and integrity of organizational data stores is the primary focus of this individual. Additionally, the DBA is generally consulted on issues regarding database design, performance, applications development, database security, and integration with Web site applications and their potential system impacts.

Site Content versus System Design

Content design deals with all the issues of presenting data and information to your user
base. Content design deals with issues such as the following:

Simply put, content design is concerned with what is delivered to users and how it is presented.

System design, on the other hand, is more concerned with the mechanics of delivering the information and the infrastructure required to make it all happen. It deals with issues such as the following:

In other words, system design is concerned with how information and services are provided.

Is there a relationship between what types of information and services you provide and the system hardware and software you select to provide it? You bet there is, and most of you know this intuitively. However, this relationship is often overlooked during design or upgrade cycles because these activities are not undertaken from a systematic point of view.

The content of pages and the types of services you provide can have a direct impact on the performance of your Web server hardware and software. Conversely, the capabilities of your Web server hardware and software can directly impact the types of services and information you can provide with reasonable levels of performance. As a result, these two facets of site design cannot be considered in isolation.

The remainder of this chapter presents considerations and raises issues that you should consider when designing your site. Although we present these broken down by content and system, we encourage you to continually question how these affect one another. Site Structure The structure of a Website depends on several factors. Depending on the types of documents the Web server will service, a site may be configured strictly as a document repository consisting of static HTML-coded documents. Under another scenario, it may be more appropriate to use a more advanced form of document control, such as a database or document-indexing product.

When choosing the type of site structure to use for your site, you also need to consider other areas such as document indexing, searching capabilities, administration, and document- updating capabilities. It would certainly not be feasible to service thousands of online static HTML documents without having some form of search capability that end users can easily utilize to get direct access to specific document subject criteria. Also, the updating of static HTML documents can quickly become a Web administrator's worst nightmare. In short, you need to select a site structure that can be supported by current administrative resources, includes support for potential future expansion or migration to another site design, and still provides easy document access to your document audience. System Design Considerations This section presents numerous considerations and questions that you should ask when creating the system design. Notice that many of the questions flow down from the initial end-user requirements.

Many of the questions and considerations are easily addressable for intranet Websites and applications because these represent a more structured design environment with fewer intangibles about the user base. After all, it is substantially easier to determine the user requirements and to quantify the size of the user base for intranet applications. It is also easier to involve end users in the design process.

Designing a system for external use is a more difficult task. There are numerous unknowns about the desires, needs, and expectations of potential users. It is extremely difficult to quantify the size of the user base, expected usage and repeat frequency, peak periods, and how heavily the site will be used in the future. It also is virtually impossible to determine the amount of lost business or lost repeat visitors that result from a system that performs poorly or addresses inadequately many of the issues associated with page design and content. Still, educated attempts to answer the right set of design-related questions are better than blindly designing the system.

Get a firm idea of the goals and objectives of the system. Who are the end users? What are their informational requirements? What other services are to be provided? Will this require a document-based Website or database applications, or both?

How many end users are there, and is the number quantifiable? What is the expected frequency of site access (both in terms of static HTML documents and database access)? What amounts and types of data will be served? What CGI applications are required to support this, and what is the effect this may have on CPU, memory, and disk requirements? Will a single Internet connection provide the bandwidth necessary to service users during peak times? Page Content and Layout Tips and Considerations Hypertext documents differ from traditional print documents in that the relationship between pages and documents is blurred. Therefore, visual identity and navigational aids should be primary considerations. The following is a brief listing of basic page design considerations that you should think about when determining how information and services will be presented and provided to your users:

Always remember that it is very easy for your message to get lost through misuse of the media. Poorly designed pages can serve to confuse your users and result in the under utilization or discontinued use of services you have to offer. The way a page is designed should enhance the use of the information presented, not obscure it.

Additional information on form design and data presentation is included in Chapter 8, "Using Classes and Objects in Visual Basic 5," and Chapter 9, "Visual Basic 5 Data Access Features," respectively.


RESOURCE: The Yale Center for Advanced Instructional Media (C/AIM) Web site contains a wealth of information on Web page design and data presentation. In particular, it contains the Web Manual of Style by Patrick J. Lynch. This manual is an excellent treatise that covers a range of topics including WWW interface design, Website structure, WWW page design, use of links and navigational aids, sample page templates, use of graphics (and effects on performance), and listings of additional resources for Web authoring, graphic interface design, and multimedia. The manual can be found at the following URL:
http://info.med.yale.edu/caim/manual 



Summary

Much thought must go into the process of designing and implementing a well-rounded Website. Unfortunately, many of the sites in existence today were created during the initial euphoria that resulted from every company and government agency wanting to create a Web presence as fast as possible. Many of these sites are now retrofitting their site configurations to handle expanded capabilities and to avoid exhausted resources and administrative nightmares. A well-planned Website can ease the downstream effects that both future expansion and new technologies will bring to the Web world.