Learn Access Now!      Chapter 20      Next Section in Chapter 21

Chapter 21: Putting It All Together

(This is section 1 of 7 in this chapter)

Congratulations. You now know everything you need to know to put Access to work for you. The previous chapters in this book have taught you everything from starting Access to working with OLE to using VBA. In this chapter, all that information will be brought together, in one place, as you review the design and implementation of a full-fledged database system. This chapter is different from previous chapters in that it is not necessarily designed to teach you anything, other than what you may learn by reviewing someone else's work.

Note: The database described in this section is include on the companion CD-ROM as CD Collection.mdb. The sound snippets included in the database are also stored in the Sounds folder on the CD-ROM.

Database Design

The database developed for this chapter provides a way to catalog and manage an audio CD collection. When I first looked at developing this database, I determined that I needed to maintain the following information:

In addition, I wanted the capability to keep a ten-second sound clip for each song in the collection. With this as my starting point, my next task was to determine how I could minimize repetitious data in the database. If the database kept a record for each song in a CD collection, two fields (Group and CD Name) immediately presented themselves as potentially repetitious. To get rid of the repetition, I made these fields the basis for other tables in the database. Thus, I decided upon three tables:

My next step was to examine each table and determine the information stored in each.

The CD Table

I intended the CD table to hold information related to an entire CD: the name, group, date of release, and so on. This information would constitute the main table in the database. I decided to name this table CD Collection. The table ended up having the layout shown in Table 21-1.

Field Type Length
Disc ID AutoNumber
Group ID Number Long
Title Text 40
Recording Label Text 35
Year Released Number Integer
Number of Tracks Number Byte
Date Purchased Date/Time
Purchase Price Currency
Note Memo

Table 21-1 Layout for the CD Collection table.

The Disc ID field, which is a AutoNumber, determines a unique identification number for each CD in the collection and serves as the primary key for the table. In addition, the Individual Tracks table uses the Disc ID field to track records to each individual CD record. If you desire, you could label each physical CD in your collection with the Disc ID number, which makes cataloging your CDs easier.

I picked the other fields in the table in a brainstorming session. None of these fields are mandatory; I included many of them simply because I felt this was information which would be "nice to maintain."

Note: I don't claim that my database design is the best possible design, or the only design that will work. Determining what information to keep in a database is a very subjective matter, and you may decide to keep entirely different information in your database.

The Group ID field ties into the Groups table, described in a moment. I used this field to indicate what group was responsible for which CD.

The Song Table

The Song table holds information about individual tracks on each of the CDs. This information includes the song title, group, length, and a sample sound clip. I named this table Individual Tracks, and it has the layout shown in Table 21-2.

Field Type Length
Disc ID Number Long
Track Number Number Byte
Song Title Text 50
Group ID Number Long
Length Date/Time
Notes Memo
Sample Sound Clip OLE Object

Table 21-2 Layout for the Individual Tracks table.

The Disc ID field ties the Individual Tracks table to the CD Collection table. I used this field to indicate the CD that an individual song belongs to. Likewise, the Group ID field ties into the field of the same name in the Groups table (described in the next section). You will use this field when you want to search songs by group.

The Groups Table

The Groups table keeps track of information related to a musical group or individual singer. Of the three tables, the Groups table contains the least information. It does, however, contain the group name, when the group was formed, and where the group is from. The layout for this table is shown in Table 21-3.

Field Type Length
Group ID AutoNumber
Group Name Text 50
Date Formed Date/Time
Where From Text 40
Notes Memo

Table 21-3 Layout for the Groups table.

The Group ID field is a AutoNumber that uniquely identifies every group in the table. This field ties into both of the other tables in the database.

Establishing Relationships

With each of these tables in place, you are ready to establish relationships between the tables. You establish table relationships as described in Chapter 12, "Understanding Data Relationships." In this case, the linking fields and their purposes are:

After you establish these relationships, the Relationships window should look similar to what is shown in Figure 21-1.

Figure 21-1 The Relationships window.

Learn Access Now!      Chapter 20      Next Section in Chapter 21