home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6!
(Publisher: The Coriolis Group)
Author(s): Peter G. Aitken
ISBN: 1576102815
Publication Date: 08/01/98

Bookmark It

Search this book:
 
Previous Table of Contents Next


Sorting Records

In a relational database, the records in each table do not exist in any meaningful order. In most cases, the physical order of records in the disk file is the order in which the records are entered—which rarely, if ever, has any meaning. Of course, the structure of the disk files is something that you should never have to be concerned about. Any decent relational database application should completely isolate the user from any worries about disk files, file structure, and the like.

Does this mean that you can’t sort the records in your tables? Of course not—sorting is an important aspect of using databases. Rather than physically sorting the records, however, a database application uses logical sorting, based on indexes.

Used in this context, index doesn’t mean the same thing as the index in the back of a book, although the meaning is similar, because a database index also indicates where to find things. Each index is based on one or more fields in the table. The index itself is sorted based on the data in that field, and each entry in the index contains a pointer that specifies the physical location of the corresponding record in the table. This format is illustrated in Figure 21.1.

When you want records to be sorted based on a particular field, simply tell the database program to use the index that is based on that field. During display, printing, and so on, the table records appear in the order specified by the index. You can quickly change to another sort order—ordering by name rather than ZIP code, for example—by using another index.


Figure 21.1  Each index entry contains a pointer to the location of its corresponding record.

In addition to sorting the records in a table, an index greatly speeds up searches, or queries, based on the data in the indexed field. If you are searching for data in a nonindexed field, the database application must look through all the records, one at a time, for a match. If the field has been indexed, however, a binary search algorithm can be used, which greatly increases the speed of the process. Here’s how a binary search works:

1.  It locates and reviews whichever record falls in the middle of the index.
2.  Is this record a match? If so, it is finished. If not, it continues with Step 3.
3.  Is the item that you are searching for “less than” the data in the current record? If yes, the matching record (if any) must be in the first half of the index. If not, the matching record must be in the second half of the index.
4.  It discards the half of the index in which the match is not located, and returns to Step 1, repeating the process until it finds the record.

While you are designing a database, the following question usually arises: By which field or fields should a table be indexed? Although you increase your options by having more indexes, you also slow down performance, because all the indexes must be updated whenever new records are entered. A table should be indexed based on its primary key field, which some database applications do automatically. A table should also be indexed based on any foreign indexes that it may contain. Other indexes depend on the details of the application, and are usually restricted to fields that are used as the basis for queries.

Joins

Extracting information from database tables is one of the most common tasks that database users need to perform. When this operation involves two or more tables, it is called a multitable select—or, more commonly, a join. Three possible types of join exist to join two tables:

  One–to–one join—Occurs when only one record in a dependent table relates to a record in a primary table. One–to–one relationships are rare in relational databases. When two tables stand in a one–to–one relationship, combining their fields into a single table usually is a better solution. The fact that a one–to–one relationship exists indicates that no data would be duplicated if the tables were combined.
  One–to–many join—Occurs when multiple records in the dependent table relate to a single record in the primary table. This join is the most common and most useful type of join that is used in relational database programs. In the electronics store example, a many–to–one relationship exists between the Stock Items table and the Manufacturers table—multiple stock items exist for each manufacturer.
  Many–to–many join—Occurs when multiple records in the dependent database relate to multiple records in the primary table. For example, if each stock item may be obtained from several manufacturers, and each manufacturer makes several stock items, you have a many–to–many join. Obviously, this kind of join can’t be based on a primary key field in either table. Strictly speaking, a many–to–many join is not a true join, because it requires an intervening table (the relation table) to hold the values of the foreign keys, and relational database theory only defines joins between two tables. The relation table stands in a one–to–many relationship with each of the other tables.

Database Front End Applications

When you work with Visual Basic to create a database application, you’re actually generating a database front end, which is an application that can be used to view and manipulate data in a database. The back end is the set of related database tables—and in some cases, the database engine that serves as the interface between the front end and the tables. The terms client and server are sometimes used instead of front end and back end.

Why bother with this separation between the front and back ends of a database? Why not just combine all database functionality into a single application? The answers to these questions is clear if you consider how most organizations use a database. An organization’s various databases cover a wide range of information—personnel records, salary information, sales data, inventory information, and so on. The various users within the organization, however, need to access selected subsets of this information in specific ways. The personnel department, for example, requires access to the personnel data and needs to look at information such as Social Security contributions and medical insurance payments. The shipping department has no use for personnel information (and may even be denied access to it), but shipping does need to view sales and inventory data.

If the front and back ends of the database were combined in a single application, a single application would have to cover the needs of all potential users. It would be unavoidably large, complex, and cumbersome—not to mention difficult to customize for new or changed needs of a specific group of users. Additionally, and perhaps even more importantly, maintaining database integrity and validity would become a nightmare.

The front end/back end approach avoids most of these problems. Each group of users can be given its own specialized software that is designed to do just what the group needs it to do, and nothing more. With a single program—the back end’s database engine—coordinating all access to the actual database files, matters of integrity and validity can be dealt with more easily, and controlling access to different parts of the database becomes feasible.

Database front ends fall into two categories:

  Decision–support applications—Enable users to view and query information in the database, but do not permit them to add or modify information.
  Transaction–processing applications—Include the capability to add, delete, and edit data in the database.

Decision–support applications, which vary widely in scope, are the most common type of database front end. This type of program can be extremely specialized and limited—for example, an application that displays only client and sales information can be designed for sales personnel. These programs can also be extremely flexible, as in complex management–information systems (MIS) that provide summary data on all information in a company’s database. Decision–support applications generally have read–only access to the database files—that is, they can read the data but can’t change it.

Two approaches, often combined, can be taken toward the design of decision–support applications. Users can be given one or more fixed, nonmodifiable ways of examining the data, or they can be given the capability to design custom queries and reports. The former method has the advantage of requiring less training of the final users, but productivity suffers if the program’s predefined capabilities aren’t closely matched to the users’ needs.

Transaction–processing applications provide the capability to add new data to a database and to edit or delete existing data. Because a transaction–processing application can change the actual table data, some additional programming considerations come into play. The application has the responsibility of preserving the integrity, or accuracy, of the data in the tables.

Many databases are client/server databases. The terms client and server are being used somewhat differently here than they were earlier in the chapter, although it is still related to the concept of front end and back end. A client/server database refers to a multiuser database, to which multiple front ends are connected from different computers scattered around an organization. The client computers are connected via a network to the server computer on which the database back end is located. Multiuser client/server databases present some special problems, which are dealt with in Chapter 24.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.