Planning a Database

When you do create a new database, you want to make sure that the database is designed not only to meet your data entry needs, but also to meet your needs for viewing and reporting the data that is held in the various tables that make up the database. Taking a little time to plan your database before you create it can save you from headaches down the road. The sections that follow provide some tips on planning a database.

Determining Your Tables

Technically, you need only one table to make a database. However, because Access is a relational database program, it's meant to handle many tables and create relationships among them. For example, in a database that keeps track of customer orders, you might have the following tables:

• Customers

• Orders

• Products

• Salespeople

• Shipping Methods

Using many tables that hold subsets of the database information can help you avoid making redundant data entries. For example, suppose you want to keep contact information on your customers along with a record of each transaction they make. If you kept it all in one table, you would have to repeat the customer's full name , address, and phone number each time you entered a new transaction. It would also be a nightmare if the customer's address changed; you would have to change the address in every transaction record for that customer.

A better way is to assign each customer an ID number. Include that ID number in a table that contains names and addresses, and then use the same ID number as a link to a separate transactions table. Basically, then, each table in your database should have a particular themefor example, Employee Contact Information or Customer Transactions. Don't try to have more than one theme per table.

A table design requirement is to be sure that every table you create uses the first field (the first column of the table) as a way to uniquely identify each record in the table. This field can then serve as the table's primary key. For example, customers can be assigned a customer number, or sales transactions can be assigned a transaction number. The primary key is the only way that you can then link the table to another table in the database.

It's a good idea to do some work on paper and jot down a list of tables that will be contained in the database and the fields that they will contain. Restructuring tables because of poor planning isn't impossible , but it isn't much fun, either. Tables are discussed in more detail in Lessons 3, 4, and 5.

Determining Your Forms

As already mentioned, forms are used for data entry. They allow you to enter data one record at a time (see Figure 1.4). Complex forms can also be constructed that actually allow you to enter data into more than one table at a time (this is because fields can be pulled from several tables in the same database into one form).

Figure 1.4. A form allows you to enter data one record at a time.

Planning the forms that you use for data entry is not as crucial as planning the tables that make up the database. Forms should be designed to make data entry easier. They are great in that they allow you to concentrate on the entry or editing of data one record at a time. You might want to have a form for each table in the database, or you might want to create composite forms that allow you to enter data into the form that is actually deposited into more than one table.

The great thing about forms is that they don't have to contain all the fields that are in a particular table. For example, if you have someone else enter the data that you keep in an employee database, but you don't want that data entry person to see the employee salaries, you can design a form that does not contain the salary field. Forms are discussed in more detail in Lessons 10, 11, and 12.

Determining Your Queries

Queries enable you to manipulate the data in your database tables. For example, a query can contain criteria that allow you to delete old customer records, or it can provide you with a list of employees who have worked at the company for more than 10 years .

Deciding the queries that you will use before all the data is entered can be difficult. However, if you are running a storea cheese shop, for exampleand know that it is important for you to keep close tabs on your cheese inventory, you will probably want to build some queries to track sales and inventory.

Queries are an excellent way for you to determine the status of your particular endeavor. For example, you could create a query to give you total sales for a particular month. Queries are, in effect, questions. Use queries to get the answers that you need from your database information. For more about Access queries, see Lessons 15 and 16.

Determining Your Reports

A report is used to publish the data in the database. It places the data on the page (or pages) in a highly readable format. Reports are meant to be printed (unlike tables and forms, which are usually used onscreen). For example, if you were running a club, you might want a report of all people who haven't paid their membership dues or who owe more than $1,000 on their account.

A report is usually for the benefit of other people who aren't sitting with you at your computer. For example, you might print a report to hand out to your board of directors to encourage them to keep you on as CEO. A report can pull data from many tables at once, perform calculations on the data (such as summing or averaging), and present you with neatly formatted results. Figure 1.5 shows a database report.

Figure 1.5. Reports allow you to organize and summarize database information.

You can create new reports at any time; you don't have to plan them before you create your database. However, if you know you will want a certain report, you might design your tables in the format that will be most effective for that report's use. For more information on creating Access reports, see Lessons 17 and 18.

Designing good databases is an acquired skill. The more databases that you work with, the better each will be. Now that you've gotten your feet wet with database planning, take a look at how to start Access.