Keys
| Each table in your database represents one subject only. But as you've seen, to accomplish your mission objectives, you need data from two, three, or even more of these tables. For example, the invoice you looked at earlier requires values from the Customers, Orders, and Order Details tables, and perhaps other tables as well. Having segregated your data by subject, how do you now tie together these tables so you can combine their values and accomplish your mission objectives? Keys are a vital part of the answer. Keys are essential for ensuring that each record in a table is uniquely identified. You'll soon see that achieving that objective is crucial if you hope to bring together data from several tables while maintaining data integrity. Keys as Representative FieldsAll social organizations at times need to unite with their peers to achieve common group objectives. High school clubs meet to allocate the use of school facilities equitably. Aluminum makers organize to lobby legislators and influence industrial policy. Toastmaster clubs work together to arrange public-speaking competitions. To coordinate these efforts, organizations must choose one or more of their members as representatives to external audiences. Because that member essentially is the organization to the outside world, the best organizations adopt careful procedures for finding the right person. First, they determine the qualities needed in such an ambassador. Second, they identify individuals in their organizations who have these qualities. Finally, they choose the best candidate for the job. Qualities of KeysThis process can be compared to the selection of keys in a database. Each table needs to be represented to other tables and throughout the database. Therefore, you need to select one of the table's membersone of its fieldsas its envoy, so to speak, to other tables. Specific qualities make a field a good nominee to represent the table in the database as a whole:
Many fields in a table will lack one or more of these qualities and will be immediately disqualified. A few, one, or no fields will have all these qualities. The keys that do meet the standards are known as candidate keys. The key you ultimately select to represent the table is known as the primary key. The keys you don't select are known as alternate keys. (There are also foreign keys, but let's hold off on that discussion for now.) Searching for Keys in the Classic TV DatabaseTable 2.11 shows the current list of fields for the tables in the database. Let's review the fields to see which might make good candidate keys in each of the tables. ProgramsThe Year Started, Year Ended, and Location fields are all poor candidate keys for the Programs table. Any one of those fields could easily contain duplicates. It's also possible that you will have null values in those fields. The Name field is more distinctive, and it won't contain null values. But a program name is still not a good candidate key. Dragnet, The Lone Ranger, and The Twilight Zone have all in recent years been revived as new series with the same name. Thus, none of those names would be unique. The Character Names and Character Occupations fields would also be poor candidates because they could have several values in the same field (a problem we will deal with shortly). Synopsis and Program Notes are Memo fields that can contain an almost unlimited amount of data and, as such, are unsuited to be primary keys. GenresAt first glance, the genre name could conceivably make a good candidate key in the Genres table. Because it's descriptive, you can make sure that it is unique. You can always create a new genre name, so it need never be null. But precisely because the genre names are descriptive (urban comedy, police drama), it's entirely possible that you might want to modify their names (yuppie comedy, crime drama) as you develop the database. Thus, it fails the third requirement that the primary key be modified only in rare or unusual circumstances. The Genre Description field will contain one or more sentences to describe the genre. It will have the Memo data type and is thus an unsuitable candidate for a primary key. NetworksIt might seem that the Official Name field would be a good candidate key in the Networks table. But in the highly fluctuating world of media, the corporate entity changes often. A Columbia Broadcasting System that has been purchased and absorbed by, say, Rupert Murdoch's Fox might be a sufficiently different entity from the one that produced The Beverly Hillbillies in the 1960s; you might want to distinguish between them. It is also unlikely that a huge network such as ABC or CBS would abandon a popular abbreviation with which it is so closely identified. But could the same be said of smaller networks that you might want to include? Thus, Popular Name is probably not the best candidate key. The Founder field could have the same value in two fields; the Network Notes field is a Memo field and, thus, is disqualified. ActorsAs you saw in Chapter 1, "Getting Started," people's names are poor candidates for primary keys, even when the first name and last name fields are taken together. Other fields in the Actors table do not qualify as good candidate keys. Gender can obviously have duplicates, and Biography is a Memo field. Artificial Primary KeysWe certainly didn't seem to have much success finding candidate keys. We found just one field (Popular Name in the Networks table) that was a possible candidate key, and even then we weren't fully satisfied using it. If a database were a business, we'd make some compromises and choose less-than-perfect candidates as our representatives. Fortunately, in databases, you have an excellent alternative. You can create a field (called an artificial key) and use that as your primary key. In the Customers table see Table 2.12), CustomerID serves as a primary key. As you saw in Chapter 1, a field with an AutoNumber data typea field that, by default, starts at 1 and simply adds 1 to each succeeding recordis highly serviceable as a primary key. Let's consider its qualities:
In Table 2.13, ID fields using an AutoNumber data type have been added to each table.
Artificial Versus Natural KeysThe superiority of artificial keys over natural keys (fields that already exist in a table because they define specific traits) is by no means universally accepted. As with Macintosh versus PC users, both artificial and natural keys have their supporters. Of course, some take a middle stance and believe that natural keys work well in some instances and artificial keys work well in others. On the downside, natural keys can be long and complex, you might need to edit them, and they might turn out to be less unique than originally thought. But artificial keys (and in Access, that usually means keys with the AutoNumber data type) have their drawbacks as well. They can mystify users who are unfamiliar with them, especially when records are deleted and there are gaps in the sequence of autonumbers (as described in Chapter 1). You will usually find it better and easier to use an artificial AutoNumber field as your primary key. When you get over the initial hump of learning how they work, they offer the simplicity, uniqueness, and consistency that are desirable in primary keys. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
EAN: 2147483647
Pages: 169