Creating  
an Access Database:  
A Primer for Resource Center Managers   

 by   
Laura C. Larsson

Planning Your Access Database

Table of Content

Introduction  
Common Mistakes 
General Questions to Ask Yourself Before You Begin 
Collecting Information on the Database  
Functions of a Serials Database  
The Design Process   For More Information 
 

Introduction 

Before we begin the design process, we need to know what kind of database we are going to be creating in today's workshop and we need to have a clear idea of what we are going to use the serials database for. 

We are going to be designing an Access database to track the ordering and management of the periodicals we receive in our resource centers. The database we are going to be creating is a relatively simple database but it does have a enough interesting features to make the design process interesting. 

Planning a database is a lot harder and more time consuming than creating a PowerPoint presentation for an eight hour workshop. But planning is something which must be done carefully and with much consideration. Do not assume you can start creating tables and their relationships without having done some serious thinking ahead of time. It is much better to thoroughly plan the database and make your mistakes ahead of time than to have to redo the database later on because the design is poor.  It is not very difficult to change the structure of a table once you have entered data into it, but it is better to avoid making any changes once you have entered data into a table. 

It is inevitable that you will need to make some changes. Be sure to back up your database before you make changes to the table structures just to be on the safe side. 

By the way, we will use several terms when we speak of journals. We may use the word journal, periodical or serial. All three terms pretty much mean the same thing, although the format might be slightly different. Another term used in public libraries is magazine. Examples of journals include the New England Journal of Medicine, Science, Nature, Milbank Quarterly, Medical Care, and so on. 

Journals appear in a number of formats including hard copy, CD-ROM, and virtual (on the World Wide Web). 
 

Common Mistakes 

A common mistake people make is to create one large table with all the fields in it. Use Access to create many tables and the relationships between the tables. Another common mistake is to create a table in which the same information is repeated in each table. For example, you do not need to repeat the name of the person in each table. Use the relationships between the person and any other information to link information in two tables together. Remember not to repeat information and avoid redundant data. Don’t type in anything more than you have to. 
 

General Questions to Ask Yourself Before You Begin 

There are a number of questions you need to ask yourself before you start. 

Why do I think I want a database?  Does the information I have lend itself to fields and records? (Most information lends itself to some kind of structure).  What kind of data do I have? What is the best way to organize it? (These questions help you define your tables). 

If I need to enter data into a table, how can I simplify the process to make it as easy as possible for someone to do data entry? (This question helps you define the forms you may need). 

When I have the data entered into the database, what kinds of questions do I think I’ll want to ask about it later on? (This question will help you define the queries and reports you will want to make based on your data). 
 

Collecting Information on the Database

Be sure to identify and talk to potential users, even if you spend your days working in the area of the database you're going to create. You would be surprised as how often you forget important elements. Be certain to record the suggestions. Professionals who create databases for a living generally use Requirement Collection Forms to capture requests for data elements from the people they interview. 
 

Functions of a Serials Database 

A serials database has many functions. We will be discussing the functions of a serials database in this section of the handout.
 

The Design Process 

  1. Determine the purpose of our database
  2. Determine the tables we need for the database
  3. Determine which fields we will need 
  4. Determine how each of the tables is related
  5. Refine and redefine the design

Determine the Purpose of our Database 

Before we do anything else, we need to ask the following  questions: What do we need to know from our database and what will this information allow us to do? 
 

What do we need to know from our database? 

We'll need to know a lot. This database is going to track a lot of information and should be able to answer any serials-related questions we have. 
 

We would like to know 

  • which journals we own (and in which format)
  • how we acquired them (i.e., who did we purchase the individual journal from (the publisher, a jobber))
  • how much we paid for each journal
  • how often we should bind each journal if we bind our journals
  • how much space on our shelves each journals take up
  • which issues we've received and which are missing
  • when we should renew a journal
  • what the purchase order (order numer) is
  • what else do you want to know?

What will this information allow us to do? 

  • track costs over time, in order to make informed decisions as to which journals we should keep and which ones we should cancel
  • track individual journal price increases from year to year to make decisions on which journals to keep and which to cancel 
  • provide various reports to the financial person in your organization
  • create and mail claims for missing issues
  • plan for weeding or storage
  • track to be sure that we are receiving issues on our purchase orders
 
Assignment:

What other questions would you like to see added to What We Would Like to Know. Add them here

    • Make another list of What the Information Will Allow us to Do.


    •  

    Make a list of all the information you need. 

    Keep in mind what you've listed above. Remember to talk to others about the database to get ideas for what data elements need to go into the database. 

    Map it out on paper. 

    You can now use your thoughts about the database and a piece of paper to begin designing a database in Access 

     

    Determine how each of the tables is related

    Figure out on paper how each of the tables is related and then read the section on Building Relationships. 

    Refine and redefine the design 

    Believe me when I say that immediately after beginning to enter data into your database you will find errors and duplications. This is why it's always wise to test the database with 20 to 25 representative records to make sure that your relationships are accurate, your forms work correctly to capture all the journal information you need in your collection, that your queries also produce relevant information, and that your reports also produce nice looking and accurate data. 

    Plan on spending a lot of time designing and redesigning your database. That's just part of the learning experience. You will find that each time you design a database, it gets very slightly easier. At the best of times, designing a database is a complex process. At the worst, it is your worst nightmare. 
     

    For More Information 

    For more information on planning a database, look at the Introduction to Microsoft Access 97 and Getting Help from Access Help. 

    Next: Opening a New Database 

    Back: Planning a Serials Database
    Table of Contents | References and Resources | Glossary | 
    Page Updated: June 21, 1998
    URL:  http://weber.u.washington.edu/~larsson/conf/aiha98/primer/planning.html