One of the two sample databases that comes with Visual Basic is called BIBLIO. The BIBLIO database has a simple structure, almost trivial, but it demonstrates many of the topics we have covered 80 far. You can open the BIBLIO database With the Visual Data Manager and explore its structure.
The BIBLIO database contains book’ titles, authors, and publishers, and it’s made up of four. tables, as shown in Figure 17.11. Instead of showing the names of the fields of each table, this figure shows some of the data they contain, and it shows only the fields needed to demonstrates the relationship among the tables, The field names. are displayed, as column headings.
The first rule in database design is to avoid data duplication. Storing author names along with the titles would violate this rule because an author may have written more than one book. In this case, you would have to repeat the same author name in more than one row of the Titles table. Author names are stored in a separate table, the authors table. The same is true for publishers. Since each publisher
appears in many books, you shouldn’t store information about publishers in the Titles table. Why repeat the publisher’s address with each title?
So far, we have established the reasons for the presence of three tables in the database. But the BIBLIO database has a fourth table. Each book can have more than one author, and an author’s name may appear with more than a single title. Think about this for a moment. Storing multiple author names, even author IDs, for each title would require a field for each author. This field would be analogous to the PubID field, but because a book might have multiple authors, you would have to provide a number of fields-for storing Author IDs (AuthorID1, AuthorID2, and so on). It is clear that authors can’t be handled like publishers
The Title Author table sits between the Titles and Authors tables and connects them with a pair of fields, which are the Title’s ISBN and the Author’s ID. When you want.to see a specific title’s author, you will do the following:
- Locate the desired title in the Titles table.
- Read the Title’s ISBN and use it as a key to locate the matching row(s) in the Title Author table.
- For each’of the matching rows in the Title Author table, read the author’s ID and use it as a key to locate the author’s name in the Authors table .
If you are not familiar with database programming, this procedure may sound complicated.but it isn’t (perhaps you should just get used to it). Later in this chapter, you’ll see two ways to search for specific records with keys. The technique just described won’t work efficiently unless there’s a quick way to locate a record based on the value of a specific field. And this tan be accomplished with the proper indexing of the tables.
As you can see, the indices are an essential part of database design. The Title Author table, for example, must be indexed on the ISBN field. If you want to be able to search in the opposite.direction (given an author’s name; locate the books on which this name appears), you ‘should also index the Title Author table on the AulD. field. Obviously, the Authors table must be indexed on the AuID field, since the information in the Title Author table is the author/s ID, not their name. The Publishers table must be indexed on the PubID field so that you can go quickly from a title to its publisher.
