Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6!:Database Programming: Tools And Design
function GetCookie (name) { var arg = name + "="; var alen = arg.length; var clen = document.cookie.length; var i = 0; while (i < clen) { var j = i + alen; if (document.cookie.substring(i, j) == arg) { var end = document.cookie.indexOf (";", j); if (end == -1) end = document.cookie.length; return unescape(document.cookie.substring(j, end)); } i = document.cookie.indexOf(" ", i) + 1; if (i == 0) break; } return null; } var m1=''; var gifstr=GetCookie("UsrType"); if((gifstr!=0 ) && (gifstr!=null)) { m2=gifstr; } document.write(m1+m2+m3);            Keyword Title Author ISBN Publisher Imprint Brief Full  Advanced      Search  Search Tips Please Select ----------- Components Content Mgt Certification Databases Enterprise Mgt Fun/Games Groupware Hardware IBM Redbooks Intranet Dev Middleware Multimedia Networks OS Prod Apps Programming Security UI Web Services Webmaster Y2K ----------- New Titles ----------- Free Archive 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 function isIE4() { return( navigator.appName.indexOf("Microsoft") != -1 && (navigator.appVersion.charAt(0)=='4') ); } function bookMarkit() { var url="http://www.itknowledge.com/PSUser/EWBookMarks.html?url="+window.location+"&isbn=0"; parent.location.href=url; //var win = window.open(url,"myitk"); //if(!isIE4()) // win.focus(); } Search this book:  














Previous
Table of Contents
Next




Fundamental Design Considerations
Database design remains as much an art as a science. While some rules and guidelines apply, no well–defined design method regularly produces properly designed databases. The knowledge and skill of the programmer come into play in ways that can almost be called intuition. Ask an experienced database programmer why he or she designed a table in a certain way, and you may hear an answer like, “I don’t know—it just seemed the obvious way to do it.”

The demonstration database covered here is a fairly simple project, and the reasons behind design decisions will most likely be clear to you. With more complex projects—and some databases involve dozens of tables—the table design may be much less obvious. And although alternative designs might work, they will not work as well as the optimal design.
The Design Process
Laying out a fixed sequence of steps to design a database is impossible. It’s a fluid process, with lots of interaction between the various stages. You often have to go back and change something that was decided earlier, such as a table structure or a key field, because of unforeseen problems that arise. Of course, minimizing those setbacks is important—every time you have to change something, you waste time and effort. By paying attention to the basic principles discussed in this chapter, as well as by gaining progressively more experience, you can minimize mistakes—but don’t count on ever being able to eliminate mistakes entirely.

Here, then, are the most important steps to follow in designing a Visual Basic database application. Remember that this sequence is not fixed, although following a similar order generally is the best approach. In spite of any other variations, the first step should always be to gather information.
Gathering Information
In many respects, gathering information is the most important step in database design. No matter how skilled you are as a programmer, you can’t create an application for a certain task unless you know exactly what that task is. Generally, the database will have a formal specification, whether provided by the client or noted as a result of conversations with the client. This specification will include information such as lists of the items (entities) that need to be tracked, the individual pieces of information about each item (attributes) that must be recorded, and the types of forms and reports that must be generated. While this information is essential, don’t stop there. Ask for all the paper data forms that are currently in use. Find out which data–related tasks are performed frequently and which ones are rarely, if ever, necessary. When replacing a legacy system, talk to the operators to discover what they like and don’t like about the current software. The more information at your command, the easier your job will be.

Designing The Tables
After you have all the necessary information, designing the database tables is usually the next step. Because the tables are the foundation of the database, this order makes perfect sense. Table design is a paper–and–pencil process, because visual representations of tables and the relationships between them can be a big help in the design process. Perhaps the most fundamental rule is to create tables that are based on important entities in the assortment of data that the database will contain—people, orders, invoices, and so on. Be sure that the data type of each field is appropriate for the data that it will hold, and that the length of text fields is matched to the information that will be placed in them. When the data lends itself to being divided into tables in two or more ways, make a decision based on how the data will be used and on the availability of keys and links.

Plan The Links
Sketch out the links between tables. To be useful, every table in a relational database must be linked to at least one other table. In most cases, links should be of the one–to–many (1:M) variety. A table structure with 1:1 links usually benefits from redesign, while many:many (M:M) links require special treatment.

Decide On Primary Keys, Foreign Keys, And Indexes
Every table should have a primary key, even if it is a surrogate key that you generate. Every table on the “many” side of a 1:M link also needs a foreign key, usually the same data item as the primary key in the table to which it is linked. Choose the fields that will be used for indexes. Each table should be indexed based on its primary key field, and also based on any fields that will be used regularly to search or sort the table records.

Sketch Out The Forms
After the tables have been designed, you can start sketching the Visual Basic forms that the program will use. Form design is based on the table structure, and also depends heavily on how the users need to view and edit the information. You should test your preliminary form designs, if possible, by incorporating them into a dummy program and allowing the people who will be using the database to try them. Form designs are easier to change now than at a later stage of program development. After completing this stage of design, you should have a preliminary idea of how many forms the program will have, and which controls each form needs.

Plan The Flow Of Execution
After you have a clear idea of the program’s forms, you should think about program execution. What will the user see when the program starts? How will the user select program functions—menus, toolbars, or command buttons? How do the forms relate to each other? For example, can the user move from a data entry form to a query form directly, or must the user first close the data entry form and return to the opening screen? The users’ needs should also influence this stage of design. Will a single operator be responsible for taking orders and entering inventory, frequently switching from one task to the other? Or will these two tasks be performed by different operators, with no need to quickly switch between them?

Gotchas
Rarely has a programmer completed a database design project without looking back and wishing that he or she had done something differently. Personally, I’ve never discovered any sure guarantees to avoid this. However, you can lessen the number and severity of these hindsight problems by keeping in mind a relatively small number of database design boo–boos. Here’s a list of database design dos and don’ts:

Keep Your Data Atomic
No, that doesn’t mean your database should be radioactive! Data that is atomic can’t be broken down any further into smaller chunks of data—at least not meaningful chunks. This relates to the field structure of your tables. An Address table with a single Name field that holds each person’s full name doesn’t have atomic data, because it can be further subdivided into first name and last name. Problems would arise later with this table when you need to perform manipulations based on only part of the field data, such as sorting the records by last name. Writing code to extract the last name from each Name field is unnecessarily cumbersome. The best approach is to separate your data into the smallest meaningful pieces—at the beginning: combining data is always easier than extracting or separating it.
Of course, this rule must be applied in combination with some common sense. If you need to break peoples’ names into First Name and Last Name fields, shouldn’t you have a separate field for Middle Initial? The answer is no, because the isolated middle initial is not meaningful in any sense—to my knowledge, no one has ever wanted to sort a database by middle initials. The standard practice is to include the middle initial in the First Name field.
Use A Primary Key Field
Although this has been mentioned before, it bears repeating: Every table should have a primary key, a field whose data uniquely identifies each record. For many tables, this isn’t a problem—one of the required data fields provides a primary key. Other tables, usually the “many” tables in a one–to–many relationship, do not have a field whose data uniquely identifies each record. You then have two methods for creating a primary key.

Most databases have the capability to use compound keys, whereby the data in two or more fields combine to provide a unique identifier. For example, in a database of musical recordings, combining the Performer field with the Title field could provide a unique value for each record. This method has two advantages: no extraneous field additions to the table are necessary to provide a primary key, and the primary key consists of meaningful data. A possible disadvantage is that the resulting keys may be rather long, such as “The Chicago Symphony Orchestra—Beethoven’s Sixth Symphony.” Any processing that uses such a lengthy key will be correspondingly slow.
The second approach is to create a new field whose sole purpose is to hold an arbitrary key generated by the application, such as a sequential number or a letter–number combination. This is sometimes called a surrogate key. The disadvantage of this approach is that you have to add meaningless data to the table—data that takes up space but has no purpose other than to serve as the primary key. On the other hand, such keys are likely to be concise, which speeds processing. One compromise is to generate a unique value for the primary key field that combines part of the record’s “real” data with a generated number. Using the musical recordings database as an example, you could create a unique key field that consists of the first five letters of the Title field, followed by a sequential number—such as Beeth001.



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.