Planning Tables

Although you can always make changes to your database when necessary, a little planning before you begin can save time later on. When you plan a database, consider how you will use the data. What kind of data are you collecting? What kind of data are you entering? How are data values related to one another? Can your data be organized into separate, smaller groups? What kinds of safeguards can you create to ensure that errors do not creep into your data? As you consider these questions, you should apply the answers as you structure your database.

Plan Tables

Tables are one of the fundamental building blocks of a database. Database planning begins with deciding how many and what kinds of tables your database will contain. Consider organizing your database information into several tables ”each one containing fields related to a specific topic ”rather than one large table containing fields for a large variety of topics. For example, you could create a Customers table that contains only customer information and an Orders table that contains only order information. By focusing each table on a single task, you greatly simplify the structure of those tables and make them easier to modify later on.

Choose Data Types

When you create a table, you must decide what fields to include and the appropriate format for those fields. Access allows you to assign a data type to a field, a format that defines the kind of data the field can accept. Access provides a wide variety of data types, ranging from text and number formats to object-based formats for images, sound, and video clips. Choosing the correct data type helps you manage your data and reduces the possibility of data-entry errors.

Specify a Primary Key

You should also identify which field or fields are the table's primary keys. Primary keys are those fields whose values uniquely identify each record in the table. A social security number field in a personnel table could be used as a primary key, since each employee has a unique social security number. A table with time-ordered data might have two primary keys ”a date field and a time field (hours and minutes), which together uniquely identify an exact moment in time. Although primary keys are not required, using them is one way of removing the possibility of duplicate records existing within your tables.