Access 2007

Introduction

The first step in creating a database is to think about why you need it—what is its purpose? Once you know that, you can begin designing your own database. Ask yourself questions like: What kinds of information will it store? How will I have to break that information down so it can be stored in my database? How will I use the data once it's in there?

In this lesson, we'll walk you through the steps of database design using a bookstore scenario. You will determine the purpose of the database and plan the tables and fields you will need for a simple bookstore database.

Designing a Database

Watch the video! (4:22min)

The Ready-2-Read bookstore scenario

The best way to learn about designing a database is to jump right in to the process. Let's pretend we work for the Ready-2-Read bookstore, and we need to build a database for the store to use. Let's think about what we might need the database to do for us.

The following diagram shows that we have customers and sell books via orders, both in the store and online.

Ready-2-Read Bookstore

The store needs a way to keep track of these things.

Determine the purpose of the database

The first step of designing a database is to determine the purpose of the database. What do we need it to do?

At Ready-2-Read, our customers will place orders for books. At the very least, we will want our database to track the books we sell, the customers we sell them to, and the date of each of the orders.

Purpose of Ready-2-Read Database

The database will also be able to tell us where our customers live when we need to send them books or coupons. Additionally, we can use it to track our sales or to make a bestseller list to post in the store.

Plan database tables

Once you have established the purpose, you can begin brainstorming about how to break the information up into the tables your database will need to store the data. Recall that a table is a collection of records, and each record is broken up into the smallest pieces of needed information, called fields.

Since we are planning a database to track our customers, the books we have, and the orders our customers place, we will need a table for each one of these.

Tables for Ready-2-Read Database

TIP: Keep your database usable to others by giving your tables simple, easy-to-understand names.

Plan Table Fields

Once you know what tables you need, the next step is deciding what fields belong in each table. Remember that fields are the smallest chunk of information in any record.

Let's look at our Ready-2-Read bookstore example again. There will be several fields in our Customers table. We'll obviously want each customer's first name and last name. We'll also need an address if we need to send a customer his order. Email will be necessary if we need to contact the customer when there is an issue with his order.

Planned Fields in Customers Table

We will also need several fields in the Books table. Title and Author make sense for books. We'll need a Price for each book. Category will help us know what type of books sell the best so we can order more books that fit into that category.

Planned Fields in Books Table

The Orders table will have fewer fields. We'll need this table to track the Customer who is placing the order, the Book the customer is ordering, and the Date the order is placed.

Planned Fields in Orders Table

Don't worry if your plan doesn't include every possible field you may need. Access lets you add fields to tables any time you think you need another one!

Challenge!

Imagine you want to use a database at your new coffee shop. Plan your database:

• What would you want to track with your database?
• What database tables would you need?
• What fields will be in each database table?