 | All Northwind and Oakmont tables use codes for primary key values, as do almost all production databases. The critical requirement is that the primary key value is unique to each record in the table. Following are some tips, many with online resources, to aid in establishing primary key codes: |
Many types of tables such as those for storing information on sales orders, invoices, purchase orders, and checks are based on documents that have consecutive serial numbers, which are obvious choices for unique primary key values. In fact, most database designs begin with collecting and analyzing the paper forms used by an organization. If the table itself or programming code generates the consecutive number, make sure that every serial number is present in the table, even if an order is canceled or voided. Auditors are very suspicious of invoice and purchase order registers that skip serial numbers. Tip AutoNumber primary key values work well for serially-numbered documents if you don't allow records to be deleted. Adding a true-false (Boolean) field named Deleted and setting the value to true is one approach. This technique complicates queries against the tables, so you should consider moving deleted records to another table. Doing this lets you write a query to reconstruct all records for audit purposes.
Packaged retail products sold in the United States have a globally unique 10-digit or longer Uniform Product Code (UPC). The UPC identifies both the supplier and the product's SKU. The Uniform Code Council, Inc. (http://www.uc-council.org/) assigns supplier and product ID values, which are combined into linear bar codes for automated identification and data capture (AIDC). The European Article Number (EAN) is coordinated with the UPC to prevent duplication. The UPC/EAN code is a much better choice than Microsoft's serially assigned number for the ProductID field. Books have a 10-digit International Standard Book Number (ISBN) code that's unique throughout the world and, in North America, a UPC. ISBNs include a publisher prefix and book number, assigned to U.S. publishers by the U.S. ISBN Agency (http://www.bowker.com/standards/home/isbn/us/isbnus.html). ISBN Group Agencies assign code for other countries. Canada has separate agencies for English- and French-language books. Either a UPC or ISBN field is suitable for the primary key of a North American books database, but ISBN is preferred if the code is for books only. The North American Industry Classification System (NAICS, pronounced "nakes") is replacing the U.S. Standard Industrial Classification (SIC) for categorizing organizations by their type of business. A six-digit primary key code for 18,000 classifications replaces the four-digit SIC code. Five of the six digits represent codes for classifications common to the United States, Canada, and Mexico. You can view a text file or purchase a CD-ROM of the NAICS codes and their SIC counterparts at http://www.naics.com/. The U.S. Postal Service offers Address Information Systems (AIS) files for verifying addresses and corresponding ZIP/ZIP+4 codes. For more information on these files, go to http://www.usps.com and click the Address Quality link. Social Security Numbers (SSNs) for U.S. residents are a possible choice for a primary key of an Employees table, but their disclosure compromises employee's privacy. Large numbers of counterfeit Social Security cards having identical numbers circulate in the United States, making SSN even less attractive as a primary key field. The Oakmont database uses fictitious nine-digit SSNs for EmployeeID and StudentID fields. Most organizations assign each employee a sequential serial number.
Specifying a primary key for tables such as CustPers isn't easy. If you use the five-character code based on first- and last names for the primary key, you encounter the problem with potential duplication of CustomerID codes discussed earlier. In this case, however, common last names Jones, Smith, and Anderson for example quickly result in duplicate values. Creating a composite primary key from CustomerID and ContactID doesn't work; doing this increases the number of new contacts you can add for a company before inevitable duplicates occur. One approach is similar to that for CustomerID values: Add two or three zeros to the ContactID values, which provides for future name duplications and doesn't require a composite primary key. As you detect duplicate key values, add 1 to the numeric suffix of the key. In most cases, it's easier to use an AutoNumber key for all ID values.  | Figure 4.9 shows the final design of the modified Northwind database with the added contact details tables. The tables of this database are included on the accompanying CD-ROM as Nwind04.mdb in the \Seua2003\Chaptr04 folder. |
Figure 4.9. The final design of the expanded Northwind database with customer and supplier contact details tables added.  The modified Northwind database doesn't qualify as a full-fledged customer relationship management (CRM) system, but the design is sufficiently flexible to serve as the model for a sales and purchasing database for a small-sized wholesale or retail concern. |
