![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
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!
The Problem Of Repeating FieldsMany 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:
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:
Now you have what are called repeating fieldsthe StockNo and Quantity fields repeat, to permit entry of more than one item. In certain situations, where you know that the number of itemsor whatever it might be that is being repeatedwill 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 wont 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 tableeven empty onestake 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 doesnt 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 isnt 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.
Your table definitions are completeat 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:
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 youre lucky, this design will remain unchanged throughout the remainder of the project. Creating The DatabaseNow 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. Its 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
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:
After the table definitions are complete, you can close the Visual Data Manager. Youre 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.
|
![]() |
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. |