Tables & Queries:
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
How to accomplish these goals? You will need to follow a few rules and then think ahead about the future needs of your database.
Project 3: Tables & Queries
Common Sense Thinking
Forms 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?!
Table 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.
Start with: , Access open but no database open.
The story so far:
You will walk through the design process, and make some bad decisions along the way (not that YOU would do that in real life!).
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.
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
Inspect the table so far. Do you see any problems or potential problems with this design?
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.
Copy and Paste Data Between Tables
You can copy and paste the names of the projects, if you do your selecting correctly.
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.
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.
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.
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!
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)
Second Normal Form (2NF)- Eliminates repeated values in a column.
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!
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.
~~ 1 Cor. 10:31 ...whatever you do, do it all for the glory of God. ~~