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




The Problem Of Repeating Fields
Many database designs run into the problem of repeating fields. To illustrate exactly what this means, consider the Invoices table that you are creating to hold information about invoices. A basic table design might look something like the following:

Field Name
Description

CustID
Customer identification number

Date
Date of invoice

InvNo
Invoice number (primary key field)

CustPO
Customer’s purchase order number

StockNo
Stock number for the item ordered

Quantity
Quantity ordered

However, this permits only one type of item to be recorded per invoice record. Thus, some more fields need to be added to the table:


CustID

Date

InvNo

CustPO

StockNo1

Quantity1

StockNo2

Quantity2

StockNo3

Quantity3

Now you have what are called repeating fields—the StockNo and Quantity fields repeat, to permit entry of more than one item. In certain situations, where you know that the number of items—or whatever it might be that is being repeated—will be strictly limited to a small number, this type of table structure may be satisfactory. Generally, however, it should be avoided. No matter how many times you repeat the field or fields, the chance always exists that you won’t repeat it enough times to hold some records. And for most records, the majority of the repeated fields go unfilled, thus wasting disk space. Remember that all fields in a database table—even empty ones—take up space.
The solution is to split the data into two tables. One table holds the information that is unique to each invoice. The other table holds the individual line items. This relationship is illustrated in Figure 21.4.
The link between these two tables is provided by the InvNo (Invoice Number) field. InvNo is the primary key for the Invoices table and a foreign key for the Items table.
You may have noticed that the Items table doesn’t have a primary key. InvNo, StockNo, and Quantity will not be unique for each record in the table. In terms of the information that this table needs to hold, a primary key field isn’t necessary. In terms of database design, however, providing every table with a primary key is a good idea. If no meaningful data is available to serve as a primary key, you can add an additional field to the table structure and have the database program generate unique sequential numbers to serve as the primary key.

Figure 21.4  The relationship between the Invoices table and the Items table that solves the problem of repeating fields.

Your table definitions are complete—at least for now. Figure 21.5 shows the table structure, the primary keys in each table, and the links between them.

Carefully examine this database design. No problems are apparent. No data is duplicated. Each table has a primary key field, and each table is linked to at least one other table. Take a look at the type of links you have:
Link
Type

CUSTOMERS:INVOICES
1:M

WINES:ITEMS
1:M

INVOICES:ITEMS
1:M

No 1:1 links exist that might suggest a need to redesign the table. Nor do any M:M (many to many) links exist that require special treatment. You can safely conclude that you have a good database table design. If you’re lucky, this design will remain unchanged throughout the remainder of the project.

Creating The Database
Now that you have your table design, you can use Visual Data Manager to create the database and its tables. You also need to place some data in the tables; otherwise, you have nothing to use when testing forms and other parts of the program. Ideally, this should be real data provided by your client. If that is not available, you can create “dummy” data and delete it from the database later. memory needs jogging. Create a database named GRAPEVINE (using Microsoft Access version 7), with the following four tables, labeled here for identification as Tables 21.1 through Table 21.4.

It’s time to use Visual Data Manager (VisData) to create the database and its tables. The basics of using VisData are covered in Chapter 19, so please refer to that section if your

Figure 21.5  The table structure for the GRAPEVINE database (an asterisk marks primary key fields; lines indicate links).

You may have some questions about the data type that I selected for some of these fields. You may even believe that a different data type would be more appropriate in some cases. Let me explain:


•  ZIP in the CUSTOMERS table—Since ZIP code is a number, why not use a numeric data type? Some ZIP codes begin with zero; if the data type were numeric, you would have to write special code to display or print the leading zero. By using a text field, you avoid this problem.
•  Phone in the CUSTOMERS table—Why not use a length–10 text field, since a phone number contains only 10 digits (including area code)? The separators could be added later when the number is displayed. This argument is valid, but this involves only two extra characters, or bytes, per phone number for the separators (the dashes in a phone number, such as 919–555–1212). By storing the phone number fully formatted, you save programming hassles at the cost of taking up a trivial amount of extra space.


Table 21.1 The CUSTOMERS table.



Field Name
DataType, Length

CustID
Long Integer (turn on AutoIncrField option)

Company
text, 24

Address
text, 30

City
text, 15

State
text, 2

ZIP
text, 5

Contact
text, 24

Phone
text, 12






Table 21.2 The WINES table.



Field Name
Data Type, Length

StockNo
text, 10

Description
text, 40

Year
text, 4

Color
text, 5

Type
text, 15

Country
text, 15

Rating
Integer

QOH
Integer

OurCost
Currency

WholesaleCost
Currency

RetailPrice
Currency






Table 21.3 The INVOICES table.



Field Name
Data Type, Length

InvNo
Long Integer (turn on AutoIncrField option)

CustID
text, 8

CustPO
text, 12

Date
Date/Time





Table 21.4 The ITEMS table.



Field Name
Data Type, Length

ItemNo
Long Integer (turn on AutoIncrField option)

Quantity
Integer

InvNo
Long

StockNo
text, 10





•  InvNo in the INVOICES table—Why not make this a text field, so that the invoice identifiers can include letters as well as numbers? You have no reason to do this, beyond meeting client desires, such as wanting to continue with the existing system that uses an alphanumeric code for invoices. Using a numeric variable type makes generating sequential, unique values for this field easy. Because your client has no special needs in this area, go with the numbers.
•  ItemNo in the ITEMS table—The reasoning explained in the previous paragraph applies here, as well.

After the table definitions are complete, you can close the Visual Data Manager. You’re not finished with the Visual Data Manager, by any means. You still need to enter the initial data in the tables. Additionally, you will discover that VisData provides many other tools for defining various characteristics of the tables. For example, is a field allowed to be blank, or must some data be entered in it? Subjects such as this are covered in later chapters.




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.