Click Here!
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


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.