Designing a Relational Database in Access 2013
Part of the Access 2013 All-In-One For Dummies Cheat Sheet
When you use a database program like Access 2013, 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 by relational joins, in which a field in one table matches (relates to) a field in another. Here’s the general idea:
Identify your data.
Make a list of the possible fields (pieces of information), including text, numeric, date, true/false, and other types of data.
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. (Store birthdate or age, but not both, for example.)
Organize the fields into tables.
Group your fields according to what they describe so that each group becomes a table. An order-entry database for a store might have one table for customers, one for products, and one for orders.
Add tables for codes and abbreviations.
Plan to include a table of state names and two-letter codes, and a table of every other code or abbreviation you plan to use in the database. You’ll use these tables to create drop-down menus of values you can choose when entering records.
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.
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 that identifies the customer who placed the order — a field to match the primary key field in the Customers table. Most relationships are one-to-many, in which one record in one table can match more than one (or no) records in another table.