Design Example: The Crafts Database

3 4

The Crafts sample database on the companion CD contains information about books, book authors, videos, magazines, publishers, sources, and other information related to crafts. In a relational database, that means tables have been set up for each of these subjects, as shown in Figure 3-7.

Figure 3-7. Tables in the Crafts database contain information about crafts books, authors, and so on.

How do you know that a particular data category deserves its own table, and not just a field (or set of fields) in an existing table? The primary consideration should be whether the same information might be duplicated in many records. This is clearly the case with publisher data, as one publisher publishes many books. To prevent errors and ensure that the publisher name will always be correct in a book record, it's best to have a separate Publishers table and set up a lookup field in the Books table for selecting a publisher. This is also true of author and source information, but not true of ISBN number (each book, video, and kit has a unique ISBN number), language, or publication date information.

On the other hand, you shouldn't carry this process to ridiculous extremes-the name John might occur in many records in the FirstName field of the tblAuthors table, but that doesn't justify setting up a separate table of first names, and selecting first names from a drop-down list.

Information such as ZIP or postal codes and state or province abbreviations can go either way, depending on whether accuracy or ease of use is of primary importance. It's quicker and easier to type NY than to select it from a drop-down list of state abbreviations, but users might enter incorrect data. However, you can ensure accuracy by using a validation rule on the field.

After you've created a table for each database category, your next task is to set up relationships between the various tables.

See Chapter 4, "Creating a Database," for more information about setting up relationships.

Working with the Crafts Sample Database

Throughout this book, I'll use the sample relational database named Crafts (Crafts 2002.mdb on the companion CD) to explore and illustrate various aspects of database development. As a historical note, this database started life in dBASE, then was moved to Paradox, and then to Access 1.0 and up the Access versions since then. I developed the Crafts database to store information about books, videos, and kits related to various crafts, and its original output was in the form of ASCII text files, which were zipped and uploaded to the old Crafts forum on CompuServe.

This was well before the development of the Web, so users had to download and unzip the files to read or print them. Now the same Crafts data could be published directly on a Web page and viewed on line, fully formatted, with graphics.

The Crafts database lends itself well to discussions of normalization and relationships, because it contains many different types of data (authors, books, sources, specialties, publishers) that need to be linked in a variety of one-to-many and many-to-many relationships.

Design Problems and Solutions

If you're creating a database to store information about crafts books, you might start by looking at a book—say, Elizabeth Zimmermann's Knitting Without Tears— and creating a Books table with fields to store all the information you can extract from the book. In this starter table, the ISBN field was set as the primary key. Figure 3-8 illustrates such a table, with actual data from this book.

Figure 3-8. A starter Books table lists data from a single book.

This process seems reasonable, when you look at this record. But (as I know from experience with the Crafts database), this table design won't survive the addition of other book records, or the requirements of normalization. The following list describes some of the problems you might encounter while setting up a relational database, and offers possible solutions. Although the specific examples are from the Crafts database, the problems are typical of those you might encounter while creating any new database or normalizing an existing database.

• Problem: Books without ISBN numbers. The ISBN numbering system wasn't adopted until the late 1960s. Therefore, older books won't have an ISBN number, and because the ISBN field has been designated as the key field, you won't be able to add records for these books (because a key field can't be left blank).

  Solution: Use an AutoNumber BookID field instead, so each book will have a unique ID, regardless of whether it has an ISBN.

• Problem: Author name in one field. Placing the author's entire name in one field makes it difficult, if not impossible, to print a report listing books by author, last name first.

  Solution: Break the author's name into separate fields for First Name, Middle Name, and Last Name.

• Problem: Multiple authors. This table doesn't allow you to list multiple authors for a single book, other than by creating a new record with duplicate data, which would violate normalization, as well as being difficult to work with.

  Solution: Create an Authors table to hold author information, and set up a many-to-many relationship between the Books and Authors tables. The Author field can then be eliminated from the Books table.

• Problem: Publisher name inconsistency. Publishers might change their names as the years go by or their names might have a different form. For example, one book might list its publisher as Charles Scribner's Sons and another as Scribners. Even though these are the same publisher, they wouldn't appear in the same Publisher category in a report, which would be incorrect.

  Solution: Set up a separate Publishers table, and then make the Publisher field in the Books table a lookup field so that the publisher name can be selected from a drop-down list. In the Publishers table, enter the full, official name (Charles Scribner's Sons) in one field and the commonly used abbreviation (in this case, Scribners) in another field, for selection in the lookup field's drop-down list. This ensures that all books from this publisher will have the same entry in the Publisher field in the Books table.

• Problem: Cover type inconsistency. As with the publisher name, you might encounter problems with reports sorted by Cover because Paperback was typed in some fields and just Paper in others.

  Solution: There are two solutions for this problem. One is to make the field a lookup field with a value list containing just two values: Hardback and Paperback. The other is to make the field accept an Integer value, selected from an option group, and translate it into Hardback or Paperback for reports.

• Problem: Currency type inconsistency. The data type of the Price field is Currency. (Access is smart enough to create a Currency field when you enter an amount preceded by a dollar sign.) This isn't a problem if all your books are published and sold in the United States, but you will have a problem if you also want to enter book prices for other countries.

  Solution: Add a field that identifies the currency type, preferably a lookup field for consistency, and change the data type of the Price field to a Double. This lets you add the appropriate currency symbol (such as a British pound symbol) to the price when you print reports.

• Problem: What if it's not a book? As I worked on my Crafts database, I realized that I also needed to add videos, and later kits, to the table, as they were also informational materials related to crafts. Also, some books include CDs, and I needed a way to record this fact. The starter table provides no way to indicate whether an entry is a book, video, or kit or whether it includes a CD.

  Solution: Again, this problem has two possible solutions. The first solution is to add four Yes/No fields, one each for Book, Video, Kit, and CD, to indicate whether a record in the table represents a book, video, or kit (or perhaps some combination of these, such as a book and kit set) and whether it includes a CD. The second solution is to separate Books, Videos, and Kits into three tables. (CDs stay as a Yes/No field, because they exist only as companion CDs for books.) This solution would make it difficult to deal with combination sets, so for the Crafts database, I chose the first solution. The check boxes are bound to separate Yes/No fields, rather than being part of an option group, to allow selection of more than one (for example, for a book/video combination).

• Problem: Need to add additional information. The original Books table is fine for entering data that is listed in (or on) the book, but sometimes you need to add information about the book that isn't listed in the book—for example, a source from which the book can be purchased, or the book's crafts specialty, or whether the book contains copyright-free information.

  Solution: Add new fields or create additional tables as needed. Specialties and Sources (like Authors) both require a many-to-many relationship to Books, as a book can have multiple specialties or sources, and a specialty or source can have many books.

Figure 3-9 shows, in two views, the finalized tblBooksAndVideos table for the Crafts database. (The tbl prefix indicates a table—see Chapter 4, "Creating a Database," for a discussion of using tags to identify database objects.) Notice that the ISBN numbers have an inconsistent format. As is often the case when entering real-world data, the sources weren't consistent in entering the dashes in ISBNs, and the dashes can't be inserted automatically because (unlike Social Security numbers) ISBNs don't have a consistent format.

Figure 3-9. The finalized tblBooksAndVideos table omits information stored in linked tables.

Notice too that the Author field from our original Books table has been removed-it isn't needed because the relationship between Authors and Books is handled by the linking table tblBookAuthors, which establishes the many-to-many relationship between Authors and Books. Figure 3-10 shows a portion of the tblAuthors table. The LastNameFirst field in tblAuthors is used to store the author's name in last name first format (the field is automatically filled in when data is entered into the individual name fields on the form for the tblAuthors table) or to store a name that isn't a person's name.

Figure 3-10. The first few fields from the tblAuthors table show the author name divided into three fields.

The remainder of the fields in tblAuthors are used to store data about the authors, including address, phone numbers, online IDs, and other such information if it's available. Figure 3-11 shows the tblBookAuthors table, which links tblAuthors to tblBooksAndVideos in a many-to-many relationship, using the two tables' primary keys.

Figure 3-11. tblBookAuthors links tblAuthors and tblBooksAndVideos in a many-to-many relationship.

Troubleshooting - The database appears to be frozen, but it isn't listed as Not responding in the Task window

This puzzling situation can arise when a modal dialog box (such as the Save Changes To The Following Objects dialog box) is hidden behind another window. Press Alt+Tab to locate the modal dialog box, and then close it. This will unfreeze the database.

Figure 3-12 shows the Relationships window, with links set up for the needed one-to-many and many-to-many relationships between database tables. (This figure shows only a representative set of the table relationships in this database, to conserve space.) The 1s and infinity symbols (∞) at the ends of the join lines represent the "one" and "many" sides of one-to-many relationships.

Figure 3-12. The Relationships window for the Crafts database shows one-to-many and many-to-many relationships.