Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101


Home > Jan's CompLit 101 > Working with Databases > Tables & Queries > Tables
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Databases

   Tables & Queries: Tables

Previously you learned a lot about creating tables yourself or with the Table Wizard, but there is still more to learn!

Table Design

You want to design your tables so that:

  • It is easy to enter, update, and delete information.
  • Data is consistent throughout.
  • Sizes for fields waste no disk space.
  • The database responds quickly to searches.

How does one do that??

General Principles for Table Design:

  • Every piece of data needs one and only one home.
    The only exceptions are primary key ID fields that appear as a foreign key in other tables to join them.
  • A table's fields must be logically related to each other.
  • The order of the fields is not important to what they mean.
  • Choose entries from a Lookup list whenever possible.
  • Use field validation to make sure that the data is in the right form.
    (We have not talked about this yet.)
  • Field size fits the possible size of the data, not just what you have already.
  • Create indexes for fields that you will be searching on often.
    (We have not talked about this yet either.)

Normalized Tables

If you read articles or books about databases, you will run into the terms normalization and normal form. There are several levels of normalization and many different ways to describe each level. These 'rules' are guides for making a database that works well and can be modified and enlarged without breaking. It's another way of looking at what makes for good table design.

Errors caused when tables are not normalized:

  • Update and data entry errors: If a name is recorded in several different tables, entering a new record or correcting the spelling in one table won't enter or correct the name in other tables.
    Correct design would have only one table for the names and the other tables would link to it.
  • Insertion error: If supplier info is recorded directly in a table of products, you cannot add a record for a new supplier until you actually have a product from that supplier. There is an additional problem if you decide to get the same product from an two or more suppliers.
    Correct design would have separate, linked tables for suppliers and products.
  • Deletion error: If faculty info is recorded only in a table about courses taught, deleting the course removes the teacher from the list of faculty. Big whoops! Also, the faculty info would be duplicated in each course record, which is a waste of disk space.
    Correct design would have separate, linked tables for faculty info and courses taught.

From these errors, you can see that it is important to keep together only the fields that truly belong together.

Summary for the first three normal forms for tables:

The table has a primary key; every other field depends on the whole key and nothing but the key.

Many databases do not completely follow the normalization rules. Sometimes a database will actually work faster without being completely normalized. It is certainly a lot of trouble when you have a small database. But will your database stay small?

Once you have a lot of records, it can be tricky to normalize the tables. It's best to analyze your plans for the tables in your database carefully and normalize the table designs before adding a lot of records!

Normalized Table Datasheet = Not user friendly

A table that has been normalized often has a lot of fields that are foreign keys. What you see in Datasheet View is a bunch of ID numbers. Not very user friendly! But datasheets are not supposed to be user friendly. That's for forms and reports. Avoid the temptation to put your data into tables in a way that is easy for you to read right from the table. That's probably not normalized and will cause trouble later.

Example: Scores table has several foreign keys (Access 2010)The illustration shows the Datasheet View of the Scores table from the Language Arts Classes database in the resources files.

The table records student scores on the various assignments and tests. The primary key is ScoreID. There are three foreign keys: C/SID, A/TID and StudentID. It would be hard to use this datasheet to find out what Molly McCoy scored on the Unit 24 Test in Spelling. (It's ScoreID 278. She scored a 95!) I filtered a form to figure that out.

Disclaimer: The Language Arts Classes example database is based on an actual database that I created while teaching 7th grade Language Arts for 65 days. Scores are real, but student names and info are not.

It is not a perfect database. I did not fully normalize the tables, including the Scores table. This created some issues later. I had to create some queries and some manual methods to work around those issues. Awkward for sure. But, the tables and relationship structure worked well enough to suit me at the time.

Perfection is not always worth the time and the aggravation to fix when you realize a bit late what you should have done! If I had taught those same classes the next semester, I would have revised the table structure.