Tables & Queries:
Design Tables

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

Good table design requires some logic and thinking ahead. Are you wasting any disk space? Will the same value, like a company's name, be spelled the same in all records? If the company's name changes, can you update it in just one place? Will your design allow for fast searches? What if there are thousands of records?

Goals for Table Design

  • Save disk space

  • Save time and effort

  • Avoid data errors and inconsistencies

  • Keep database functions fast

How to accomplish these goals? You will need to follow a few rules and then think ahead about the future needs of your database.

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing Tables Arrow: subtopic open
    Icon: StepDesign Tables
    Icon: StepFormat Fields
    Icon: StepIndexes
    Icon: StepValidation
    Icon: StepRedesign Table
    Icon: StepDocument a Table
    Designing QueriesTo subtopics
    ExercisesTo subtopics

Project 4: Forms & Reports


Common Sense Thinking

  • Set field size to allow for the largest possible values, not just the current values.

  • Use Lookup fields to keep data consistent.

  • If a field is empty for many records, reconsider whether you need this field at all. Could it be combined with another field in some way? Does it belong in a different table?

  • The order of the fields in the Design or Datasheet views can change without affecting the record.

TipForms for data entry: Plan to use forms to enter and edit your data. Normalized tables (see the discussion after the Step-by-Step section) in datasheet view are not user-friendly. They tend to have a lot of columns with just ID numbers.  Who can remember what the right number should be?!

TipTable Analyzer:  Recent versions of Access include a Table Analyzer to help you get your tables in good shape. But, you have to know what is going on in your tables and in your database overall or following the suggestions of the Table Analyzer can make a mess of things.

Icon: Step-by-Step 

Step-by-Step: Design Tables

 Icon: Step-by-Step

What you will learn:

to think through the steps to design and redesign of a table
to use Spell Check
to change field size
to rename a table
to copy and paste data between tables
to use a foreign key
to create a relationship between tables
to view subdatasheets

Start with:  Class disk, Access open but no database open.

The story so far: 
World Travel Inc. wants to include in their database some information about various projects and which staff members are involved with which projects.

You will walk through the design process, and make some bad decisions along the way (not that YOU would do that in real life!).

Think First

What fields do you need to keep track of company projects and which staff members are assigned to each one? An ID code for the project, a project name, staff members' names, name of the Project Director. Does not sound too complicated, does it?

For one or two projects, most anything will do! To keep track of a large number of projects or several years of projects, however, means you need to be careful to not waste disk space and that your design will work for many different projects.

Create Table: Projects

You have to decide how many people might be working on a project. It's easy to find out how many there are right now. What about future projects?? Hmmm. We may run into a problem really quickly! For now, let's assume that there are at most 4 staff members plus a project director.

  1. Icon: Save to Class diskCreate a new blank database and save it as projects.mdb to your Class disk in the folder databases project3.
    (Open Access. File | New... From the New File task pane, select New blank database)

  2. In the Database Window, select Table and click on the Design button. The table Design View appears.

  3. Complete the table with the following fields:
        ProjectID as an AutoNumber field. Make it the primary key by clicking the Primary Key button Button: Primary key (2003).
    Text fields:
        ProjectName, ProjectDirector, ProjectStaff1, ProjectStaff2, ProjectStaff3, ProjectStaff4


  4. Icon: Save Save the table as Projects.
    Table Design View: Projects

Edit Field Size

All of the text fields have the default size of 50 characters. That is too many for the name of the project

  1. Click on the field ProjectName to select it.

  2. In the General tab at the bottom of the window, change the Field Size to 30.

Add Records

  1. Icon: Save Save the table as Projects.

  2. Icon: Datasheet Switch to Datasheet View and add records as shown below:
    Table Datasheet View: Projects, with three records

  3. Dialog: Spell CheckSpell Check: From the menu select  Tools | Spell Check  and make any needed corrections.
    The illustration has 1 error.

Thinking Again

Inspect the table so far. Do you see any problems or potential problems with this design?

Thought 1: Some fields are blank. That is wasted space.

Thought 2: Future projects might have more than 4 staff members assigned. You could just add another field, but it would be blank for many records.

Thought 3: The table only shows the job for the director. You cannot see if other staffers have particular job titles for the project.

Thought 4: There is no information about the project itself - like due date or goals or a description of what it is all about.

Changes Needed:

Split the table into a table of Project information and a table of ProjectStaff information. Create a relationship between the tables, which will allow you to see subdatasheets in the Project table. (A truly cool feature!)

Rename Table; Create New Table

To reduce wasted space and to provide more information and to allow for varying numbers of staffers, you will now create two tables. You can save yourself some typing by copying and pasting some of the data you already entered.

  1. Close the Projects table.

  2. In the Database Window, change the name of the Projects table to Projects-old.

  3. Dialog: New TableClick the New button Button: New Table at the top of the Database Window. The New Table dialog appears.

  4. Select Design View and click OK. The Table Design View appears.

  5. Add fields for:
    ProjectID as AutoNumber and primary key
    ProjectName, with field size 30
    ProjectDescription as a memo field

  6. Icon: Save Save the table as Projects and keep it open.

Copy and Paste Data Between Tables

You can copy and paste the names of the projects, if you do your selecting correctly.

  1. Open the table Projects-old to Datasheet View.

  2. Select the column ProjectName by clicking its heading.

  3. Icon: Keyboard Shortcut On the keyboard, hold the CTRL key down and press the C button. The selected data is copied to the Windows Clipboard.

  4. Switch to the new table, Projects, in Datasheet View.

  5. Click the column heading ProjectName to select the column.

  6. Icon: Keyboard Shortcut On the keyboard, hold the CTRL key down and press the V button. The data you just copied is now pasted into the table.
    Message: You are about to paste 3 record(s). A message appears warning you that you are about to paste 3 records.

  7. Click OK. Your new table now has three records with names, but no descriptions.

    Old and new Projects tables with data copied and pasted.

  8. Add the following to the ProjectDescription field:
     For record 1:  Special trips for volume or long-time customers to celebrate our anniversary.
     For record 2:  A database to give to our good customers in which they can record information about their trips. Goal date: June 15.
    For record 3:  Training seminar on computers with emphasis on agency software. For our own staff. Date: Dec. 15
    TipCopy & Paste: You can copy each description above and paste into its matching record.

  9. Icon: Save Save the table and close it.

Create ProjectStaff Table with Foreign Key

Now you are ready to create a new table, ProjectStaff, for information about the staff. This table needs to show each staff member and what his/her job is on the project. (The ProjectStaff table does not need to have all of the contact information; that will be in another table.) The table also needs to include the primary key from the Project table. This field will be a foreign key in the new table. You need such a shared field to set up a relationship between two tables--in this case, to connect the project information with the staff members working on the project.

Having a separate table for staff information means you do not have to guess how many staff members will be working on a project.

  1. Create a new table.

  2. Add fields for:
    ProjectStaffID, as AutoNumber and primary key
    Text fields:
       StaffName, as Text, 50 characters
       Job, as Text 20 characters
       ProjectID, Number , in the Description section: Foreign key, from Projects table

    Table Design: ProjectsStaff

  3. Icon: Save Save the table as ProjectStaff.

Enter Data

The data from the original table is not laid out in a way that makes it easy to copy and paste it into the new ProjectStaff table. For these few records, it will be just as easy to type it all in. The old table did not have all of the job information anyway.

  1. Icon: Datasheet Switch to Datasheet View of the ProjectStaff table.

  2. Enter the data as shown:
    Datasheet View: Projects-old and ProjectStaff
    (Your columns will not be quite as wide as in the illustration.)

  3. Close the table.


Next you need to create a relationship between the tables. The primary key of one table should be joined to its duplicate field in the other table. It is called a foreign key for that table.

  1. From the menu select  Tools  and then  Relationships...
    A blank Relationships window opens.

  2. If necessary, click the Show Table button Button: Show Table. The Show Table dialog appears.

  3. Click the table Projects to select it.

  4. Click the Add button.

  5. Repeat for the table ProjectStaff.
    Both tables appear in the Relationships window.

  6. Close the dialog.

  7. In the Relationships window, drag the field ProjectID from the list of fields in Projects and drop it onto the field ProjectID in the list of fields in ProjectStaff.
    The dialog Edit Relationships appears.

  8. Dialog: Edit Relationships - Projects and ProjectStaffClick on the button Create. A join line appears that connects the two fields.

    Relationships Window: 2 tables

  9. Message: Do you want to save changes to the layout of 'Relationships'?Click  on the Close button Button: Close at the top right of the the Relationships window.
    A message appears asking if you want to save changes to the layout of Relationships.

  10. Click on Yes.

View Datasheet and Subdatasheet

Now that you have defined a relationship between the tables, you can use subdatasheets to see who is assigned to which project. Such a useful feature!

  1. Datasheet View: Projects, after relationship created with ProjectStaffOpen the Projects table in Datasheet View.
    Each record has a + icon at the far left.
    Icon: Trouble  Problem: No + icons at the left
    You left the table open while creating the relationship.
            Solution: Close the table and open it again.

  2. Click on the + for the first record, Anniversary Sales.
    Datasheet View: Projects, subdatasheet for record 1A subdatasheet appears, showing you the records in the ProjectStaff table that are related to this Project.
    You can quickly view and even edit the related records for any project, directly from the table view. This works well when there are just a few fields in the subdatasheet. Not so well if there are too many to see at once in the window.
  3. From the menu select  Format | Subdatasheets | Expand All.
    All subdatasheets are shown.
  4. From the menu select  Format | Subdatasheets | Collapse All.
    All subdatasheets are collapsed again.
  5. Close the table.

Thinking Some More

Using subdatasheets is quite handy but it does not take too many fields to make the subdatasheet too wide to see all of them at once. You need a better way to view and edit. That's what forms are for! Have some patience. You will learn about forms in the next project.

In the ProjectStaff table, it will be easy to make errors when entering a staff member's name. For example, William Gardner and Will Gardner and William P. Gardner are all the same person. Which way will you enter his name next week?? To make sure that names are the same throughout the database, you need a table for staff information that is linked to the ProjectStaff table. More and more tables!! That's for the next lesson.

Rules for Good Table Design

A database that follows the rules below is normalized to a particular level. There are more levels of normalization, but these first two are enough to deal with for these lessons.

First Normal Form (1NF) - Eliminates repeated groups inside a row(a record)

  1. The fields in a table should be logically related to each other.

  2. Fields are not duplicated inside the table.

  3. Each field holds a minimum of data.

  4. Each field appears in only one table.
    (except for the primary key fields which appear as a foreign key in another table.)

  5. Each record has a unique identifier, the primary key.

Is your database normalized to First Normal Form?

The original table Projects was not! It violated the first three rules!

  • Rule 1: The fields were not really aspects of the "project".

  • Rule 2: Staff members were in multiple fields.

  • Rule 3: Full names were used instead of separate fields for each part of the name.

The current database with 2 tables is closer but is not quite normalized. The field StaffName breaks the rule about holding only "a minimum of data". The parts of a name should be in separate fields. In the next lesson you will make changes that take care of this problem.

Second Normal Form (2NF)- Eliminates repeated values in a column.

  • Satisfy the rules for First Normal Form.

  • Values in a field are not duplicated between records.

  • Link the tables by defining relationships using foreign keys

Is your database normalized to second Normal Form?

No, because it does not meet the First Normal Form rules!

Even if it did, the ProjectStaff table repeats values in the Staff Name and Job fields. To meet the 2NF rules, you need a separate table that lists the jobs and one for staff info. The primary keys from these two tables would appear in the ProjectStaff table.

Many databases do not completely follow these rules. Sometimes a database will actually work faster without being completely normalized.

Why bother to normalize? To meet the goals!

  • Saves disk space by reducing duplications

  • Saves time by reducing data input duplications

  • Avoids data input errors by using linked tables and lookup fields

  • Speeds up database functions

It is best to design the tables right the first time. Normalize as you go! After you have a lot of records, it can be tricky to fix, as we saw at the beginning of this lesson when you had to separate the data into two tables. So awkward, and there were only a few records with a few fields.