Page 8 of 13PREVNEXT

Design the tables for a new database

Every database table needs a primary key

A critical field for all tables

The next step in your plan is to add a primary key field to each of your tables. A primary key is a field, or a combination of fields, with a value that makes each record – each row in a table – unique. For example, the phone company keeps track of all those John Smiths by identifying them with a unique primary key value.

In addition to identifying each record in your database, you also use primary keys in the relationships among your tables. In fact, primary keys are so important, we have a rule for them: Every table in your database must have a primary key. Without them, you can’t create relationships and extract meaningful information from your data, you stand a good chance of duplicating records, and your data will be much harder to use

Access provides several ways to create primary keys. Since you’re just starting out, the simplest way is to plan an “ID” field, such as "AssetID" or “SupplierID”, for each of your tables, and then set that field to the Autonumber data type. Access will then increment the value in that field by one whenever you add a new record. Also, if you’re planning to publish your database to SharePoint, you need to use Autonumber fields as the primary keys for all your tables.

The next course in this series shows you how to let Access create primary keys for you, and how to create them yourself.

Page 8 of 13PREVNEXT