Learn Access Now! Chapter 12 Next Section in Chapter 13 Chapter 14
(This is section 1 of 3 in this chapter)
So far in this book, you have focused primarily on using simple, straight-forward data structures to maintain your information. Access is not restricted, however, in how you can organize your data. It also lets you set up relational databases. In this chapter, you will learn what a relational database is and how to create one in Access.
By the time you finish this chapter, you will understand the following key concepts:
You may have heard or read somewhere that Access is a relational database. In general, a relational database is a table-based database in which the information in one table relates (corresponds) to information in another table. For example, you may have a database which you use to keep track of your music collection. In one table, you have all your CDs. In another, you have all the music artists in your collection. The information in the one table relates to the information in the other since every CD has at least one musical artist.
Relationships within a database are often based on relationships between objects in everyday life. For instance, the relationship between a CD and a musical artist is one that exists in the real world. Likewise, a real-world relationship exists between inventory products and the companies that make those products. In a database, you may have one table that contains inventory products and another that contains vendors. When you tie the two tables together, you create a relational database.
The idea behind relational databases is to limit the amount of redundant information in a database. Consider the inventory example again. If you want to keep track of your inventory, you might have a table that includes the following items:
For most inventories, you need to maintain this information for each item. The problem occurs when you start ordering more than one item from a given vendor. Pretty soon, you have a lot of redundant information in your table. Redundant information simply consumes disk space. Consider how much space could be wasted by thirty occurrences of the same vendor address and phone number! Multiply that wasted space by several dozen vendors, and you can begin to see the magnitude of the problem.
Wasted space is only one problem. Another is that redundant interaction increases the chance of error. For instance, when employees enter new inventory items, it is possible that they will enter typos when they put in vendor names, addresses, and phone numbers. Down the road, when you need to refer back to the information, how do you determine which names, addresses, and phone numbers are correct?
Still another problem is that redundant data increases your update and processing time. For instance, if you need to change the phone number of a vendor, you must pull up each inventory record and retype the information. As you can guess, such changes can take quite a bit of time. If you use a command (such as the Replace command in the Datasheet), the command may not change some numbers if they were initially entered incorrectly.
All in all, the redundant data increases your personal management tasks. Luckily, using a relational database, you can use two related tables, one containing inventory items and one containing vendor information. For instance, consider the following two tables:
| Item ID | Vendor ID | |
| Description | Name | |
| Color | Address | |
| Weight | City | |
| Cost | State | |
| Price | Zip Code | |
| Quantity On Hand | Phone | |
| Reorder Level | ||
| Vendor ID |
Notice that the Vendor ID field exists in both tables. Access uses the Vendor ID field to tie the information in the inventory table to the information in the vendor table. By setting up your database in this way, you don't have any redundant information and thus you eliminate many of the problems inherent in the other layout:
Information in two tables can relate in any of three different ways. These ways are based on the number of possible relations between the records. These three types of relationships are:
A one-to-one relationship indicates that for each record in the first table, there is one corresponding record in the second table. For instance, you might have two tables, one with general customer information and another with detailed customer information. For each record in one table, there is a corresponding record in the other. For security reasons, you might want to set up a one-to-one relationship; the general information may be open to anyone within your company, but the detailed information may be available to only those with a particular security clearance.
A one-to-many relationship indicates that for each record in one table there are many records in the other table. For instance, in the example given in the previous section, for each record in the inventory table there is only one corresponding record in the vendor table. This is because each inventory item has only one supplier. However, for each record in the vendor table, there could be many records in the inventory table--because a particular vendor may supply more than one inventory item.
The many-to-many relationship is the most complex relationship. For any record in either table, there can be many corresponding records in the other table. The classic example of a many-to-many relationship is the relationship between a table that contains invoices and one that contains inventory. Each invoice record can refer to many different inventory items, and each inventory item can refer to many different invoices.
In Chapter 2, "Access in an Hour," you learned about primary keys. You learned that keys provide a method for Access to quickly organize and retrieve data. In a relational environment, however, keys can do much more than that. Keys, in effect, become the tie that binds two tables together. For instance, in the inventory example provided earlier, the key in the vendor table is Vendor ID. This key field is also used in the inventory table to tie it to the vendor table. In the vendor table, the Vendor ID is unique; in the inventory table it is not.
A key for any given table possesses three attributes which are important to remember:
A key can be an account number (as in a Vendor ID), social security number, license number, part number, or any other numeric value or combination of characters that are unique. That is the important thing--the key is unique. No other row in the table can have the value of the key. However, other tables may share the same set of key information, as was pointed out in the inventory example.
Text names (such as company names or titles) are not generally unique and cannot be used in math operations; therefore, they do not make good keys. You should instead put together your own unique value.
Note: Access provides a data type which is ideally suited to being a key value--an AutoNumber data type. This sequential numeric value is guaranteed to be unique, and the user cannot change it once Access assigns it to a record.
There are three types of keys which you can use in a table:
You have actually used a simple key throughout this book. A simple key contains the value of a single column, such as an account number. Access also supports complex keys, which use the values of more than one column. For instance, you might not have a single column that uniquely identifies a record. But if you combine the contents of two columns, you can achieve the purposes of a key.
As an example, assume you are developing a billing system for your company. There are different departments in your company, and each can generate their own invoices. When you look at what you can bill for, you find that it is highly possible that there will be an overlap between departments. In other words, two departments could sell and bill for the same item. Since you want to maintain separate inventories for each department, you can't simply combine them. Your solution is to use a complex key that is composed of the department ID and the part number, together.
A foreign key is one that does not belong to the current table, but instead provides a link to another table. A good example of a foreign key is the inventory and vendor tables described earlier. The Vendor ID is a simple key for the vendor table, but it is also contained in the inventory table. To the inventory table, it is a foreign key.
Learn Access Now! Chapter 12 Next Section in Chapter 13 Chapter 14