Share this Page URL

Chapter 2. The Basics of Database Design > Normalization: Achieving a Well-Desi... - Pg. 56

The Basics of Database Design 56 Be sure to designate a primary key for each table. Remember that a primary key uniquely identifies each record in a table and that Access can't connect information stored in one table with related information in another table without a primary key. In many cases, you'll use a single field as the table's primary key, but you can use more than one field as the primary key, as we did in the previous section. Examine the tables you've designed and determine how they're related. Be sure to include foreign key fields that will form the relationships between tables so that you can combine related information. As you saw in Chapter 1, the AutoNumber data type is a convenient way to create a unique value for each record to use as a primary key. However, you don't always need or want to use an Auto- Number field as the primary key. A table might include a field that works as a primary key on its own --a U.S. social security number, an employee ID, or an order numbering system that includes a sequential number and a date (order no. 061603_01, for example). Normalization: Achieving a Well-Designed Database Before we go back to the HelloWorld database and design the table we'll use to track campaign expenses, we need to cover normalization, an important concept in the design of a relational data- base. Normalization is a set of criteria that helps ensure that you've minimized the amount of du- plicate data your database stores. Normalization often results in a database having a larger number of tables, but it makes retrieving records and updating the database much easier and also helps you manage the accuracy and consistency of your data. A strong design lays the foundation for optimal database and application performance. A poor database design can impair the performance of the entire system. We've already described some of the criteria of normalization. Here's a summary: