How to Design a Relational Database in Access 2010

By Alison Barrows, Margaret Levine Young, Joseph C. Stockman

Part of Access 2010 All-In-One For Dummies Cheat Sheet

When you use a database program like Access 2010, you can’t just start entering data. Instead, you need to create a relational database design, dividing your information into one or more tables, each made up of fields (which are like the columns in a spreadsheet). Tables are connected using relational joins, where a field in one table matches (relates to) a field in another. Here is the general idea.

  1. Identify your data.

    Make a list of the possible fields (pieces of information), including text, numeric, date, true/false, and other types of data.

  2. Eliminate redundant fields.

    Don’t store the same information in more than one place. If you can calculate one field from another, store only one. (For example, store birth date or age, but not both.)

  3. Organize the fields into tables.

    Group your fields according to what they describe, so each group becomes a table. For example, an order entry database for a store might have one table for customers, one for products, and one for orders.

  4. Add tables for codes and abbreviations.

    Plan to include a table of state names and two-letter codes, and a table every other code or abbreviation you plan to use in the database. You’ll use these tables to create dropdown lists of values you can choose from when entering records.

  5. Choose a primary key for each table.

    The primary key is the field that uniquely identifies each record in the table. You can tell Access to assign a unique ID number to each record by using an AutoNumber field.

  6. Link the tables.

    See which tables contain fields that match fields in other tables. In an order entry database, the Orders table must contain a field to identify the customer who has placed the order — a field to match the primary key field in the Customers table. Most relationships are one-to-many, where one record in one table can match more than one (or no) records in another table.