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




Avoid Bad Keys
No precise definition of a “bad” primary key exists, but you do have some definite guidelines to follow. Avoid using a big field—one that contains a sizable chunk of data—because the processing overhead required to maintain and use such a key slows down the application. This is particularly true when the primary key is used to index the table, which is a common practice. Don’t use a field in which data is likely to change, such as telephone numbers. When a primary key’s data changes, all associated indexes must be updated. Even more problematic, the data in all linked foreign keys must be updated, as well.

The data in the primary key must be unique for each record. Even if the data ostensibly should be unique—such as Social Security Number—nothing guarantees that a typing error during data entry won’t result in a duplicate number. Most database applications give you the option of designating a field as a primary key, and then it checks each new entry against existing entries to ensure that no duplicates exist. This doesn’t solve the problem of typographical errors, of course, but at least it guarantees that the primary key field remains unique.
Use Descriptive Table And Field Names
This one might seem like a no–brainer, but you would be surprised at how many people ignore it. Assign names to your tables and fields that describe the data they contain. Some tradeoffs do exist between clarity and brevity, but the names should be such that anyone who has some familiarity with the database data will be able to tell from the field and table names exactly what is being stored where. For a field that will hold Part Number, for example, PN is too cryptic, while Part Number is probably unnecessarily long. PartNo would be a good compromise.

Do Not Use Duplicate Field Names
With one exception, you should never use the same field name in different tables in the same database. Doing so is just asking for confusion. For example, a database might contain a Suppliers table and a Customers table, each of which has a field for a telephone number. You shouldn’t call that field TelNo in both tables, even though doing so might seem perfectly logical. Instead, create two field names, such as CustTelNo and SuppTelNo. Not only does this avoid the possible confusion of duplicate field names, but it also follows the previous suggestion to use descriptive field names.

The one exception to this rule is the case of foreign key fields, which can have the same field name as the field they link to in the related table.
Be Aware Of International Issues
Many databases need to accommodate international information that is expressed or formatted differently than it is in the United States, such as addresses, currencies, and phone numbers. Even if a database is intended for use only with domestic data, including the required flexibility for international use may be a good idea. Windows itself provides some features to help you deal with different number and time formats and other related issues. Look in Windows Help under Regional Settings.

The Job
In this and the next few chapters, you are going to develop a complete relational database system for an imaginary client. You’ll start from scratch with the client’s requests and specifications, and then work though all the design and development stages until you have a complete, tested, and functioning product. Approaching the task in this way should be much more informative (and interesting) than if I simply presented the finished program to you and tried to explain how it works.

Before describing the project, I want to mention a dilemma that I face as a computer book author. When I create a demonstration program, I have to find a good balance between two extremes. On one hand, I could create a complete program that has all the details, capabilities, and safety features that are needed for use in a real–world setting, with no further additions required. On the other hand, I could create a program that contains only the bare essentials needed to illustrate the principles and techniques being discussed—with no concern for how this knowledge is applied in a real, functioning program.
In the first approach, the programming techniques that I am trying to communicate may be obscured by details that, while necessary to the final program, are irrelevant to the topic under discussion. The second approach is flawed by the fact that the reader learns the techniques in isolation and never sees how they are integrated into a complete program. My approach is to try to walk a middle line between these two extremes. In the database project that you are going to develop, you will receive enough real–world detail to understand how things are handled, but not so much detail that the main topic—relational database design—is obscured. As you examine the project, you may see several places where the program is lacking a feature that would be necessary for a real, commercial application. Don’t fret—I’m aware of them, too.
The Client
Your imaginary client is the GrapeVine Distributing Company, a firm that distributes wine to local restaurants, liquor stores, and hotels. An old, family–run firm, it has been using an antiquated paper–based system to keep track of customers, inventories, and invoices. While old wine may be good, GrapeVine realizes that old business methods are not. It wants to computerize its system, and you are the lucky programmer selected for the job. The basic specifications of the project have already been sketched out by the client:


•  The business needs to keep a customer list, including company name, address, and phone number, as well as the name of the wine buyer. This list will be used to generate shipping labels and invoices, and for promotional mailings and similar tasks.
•  The business also needs to keep track of inventory. For each wine, the firm needs to record a description, its type, vintage year, the quantity on hand, and the wine’s quality rating, as assigned by a popular wine magazine. Furthermore, the business needs to record the wine’s cost (the price it pays), its wholesale price (what the business charges its customers), and the suggested retail price.
•  Finally, the business needs a method to enter and keep track of orders. Some orders come by mail, others over the phone. In some cases, the customers know exactly what they want; other times, they ask the firm to suggest something: “I want a French or Czechoslovakian red that I can sell for under $15.00.”

You now have sufficient information to start designing the database. The first step is to design the tables.

Designing The Tables
The preferred database design is one in which tables represent actual objects, at least as closely as possible. With this in mind, you can quickly see that the client’s database naturally divides itself into three tables: one for customers, one for wines, and one for invoices. Each is a physical object that exists in the real world, and each is a unit of information that is of interest to the client. Starting with the customers table (which is cleverly called “Customers”), you can plan for the following fields:

Field Name
Description

CompanyName
Name of customer firm

Address
Customer’s street address

City
Customer’s city

State
Customer’s state

ZIP
Customer’s ZIP code

Contact
Name of contact person

Phone
Contact’s phone number

At first glance, this table looks adequate, but two potential problems already exist. First, by placing the contact person’s entire name in one field, isn’t this table violating the principle of atomicity that was covered earlier? Strictly speaking...yes, it is. Should the contact person’s name be split into FirstName and LastName fields? In this case, that isn’t necessary. The function of this table is such that the contact name is not a central piece of information—the business never sorts the table records by contact name—so placing the entire name in one field doesn’t cause any problems.

The second problem with this table design is—you got it—no primary key field. You can’t be sure that any of these fields, even CompanyName, will always be unique for each record. Even if you felt sure that CompanyName would always be unique, an existing company can sometimes change its name. As explained earlier, you shouldn’t select any field as a primary key if its data is subject to change. Therefore, you need to add a surrogate key field, named CustID (for Customer Identification) in this example, to hold a unique identifier for each customer. This unique identifier will be generated later.
The following are the preliminary specifications for the table that will hold information about the wines:
Field Name
Description

Stock
NoStock number

Description
Description from wine’s label

Country
Country of origin

Year
Vintage year

Rating
Wine’s rating

OurCost
What we pay per unit

WholesaleCost
What we charge per unit

RetailPrice
Suggested retail price per unit

QOH
Quantity on hand

This table, too, may seem adequate at first glance, but then you begin thinking about some conversations that you overheard down at the GrapeVine offices. Things like “Hey Charlie, the Downtown Hotel wants a case of fancy white Burgundy for the mayor’s reception. Got any ideas?” or “The fraternity guys want a case of the cheapest red we’ve got—what shall we send them?” It seems that some characteristics of wines don’t fit into this preliminary table design. A quick phone call to the folks at GrapeVine confirms your suspicions. Every wine has a color—red, white, or rose—as well as a type that specifies its region of origin and/or the grape varieties used in making it. This can be important information in making a sale, so it should be included in the database. Therefore, you need to add two more fields—Color and Type—to the Wines table.

The last table you need to design is the Invoices table. Each Invoice record has a unique invoice number, the order date, the customer’s purchase–order number, and the customer identification number. In addition, you need to make room for the items that are ordered—for each item, the stock number and the quantity. But wait—how many items should you allow on each invoice? Some invoices may include only a couple of items, while others will have dozens. How can you handle this? You have just run into....



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.