Introduction

The first step in creating a database is to think about why you need it —what is its purpose ? Once you know the answer, you can begin designing your own database. Ask yourself questions like What kinds of information will it store ? How will I have to break down this information 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

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.

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 .

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 we've established its purpose, we can begin brainstorming about how to break up the information into the tables the database will need to store 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 .

Because we're 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.

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 if there is an issue with the order.

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 this category.

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.

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

Challenge!

Let's imagine that you want to use a database at your new coffee shop. Plan your database by answering the following questions:

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