I've been picking at this and wouldn't mind some feedback. I thought it might be a good idea to compose a tutorial on the basics of table design. This particular topic seems to be one where people either have a hard time understanding it or haven't even been introduced to the topic. I've also noticed a lot of (now lost) posts that try to explain concepts like good table design and normal forms. So here is what I have so far of my table design tutorial. The intention is to cover design steps that should occur prior to sitting down at a computer.
I know the focus of these forums is to get help after you already have a firm grasp of Access, but I have tutored enough of my own classmates to know that sometimes the basics are really confusing when your teacher sucks. So I think it's worthwhile to throw a bone to students and beginners in such a situation. I thought the final form could be as a thread where people could post questions and updates; anyone who contributes would be credited as an author.
With that in mind, does this look like it will be useful? Does anyone have feedback on how I've explained things, important points and/or topics I should include, etc.? Keep in mind this is far from the final form; I have plenty more to write on normalization and the writing in general needs to be tightened up and made more consistent. But if nobody here thinks this is a good project then I'd like to know so I don't waste any more time.
Tutorial: How To Create Your Access Tables
1. Understanding Your Needs
2. The Thought Process: Relational Databases and You
3. Normalization
a. 1st Normal Form
b. 2nd Normal Form
c. 3rd Normal Form
4. Table Definition
a. Primary Key
1. Understanding Your Needs
The first step in creating a database is to know what you need your database to do. You should have a clear idea of what problem you are trying to solve or what role the database will have in your activity. You should also have a reasonable idea of what Access is capable of doing (*If you are unfamiliar with the basics of Access’ capabilities, you should start with basic tutorials or obtain a beginner’s guide of some sort). Once you have these factors in mind, you are ready to grab a pad of paper and a pencil.
2. The Thought Process: Relational Databases and You
To effectively design a table in Access, you have to know how the tables work or you’ll get nowhere. We’ll start with a definition of “relational database”:
“A relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables.”
Helpful, right? Simply put, a relational database is a bunch of tables with information in them. Sometimes the same information is stored in more than one table, and those tables are related to each other because of that common information.
An example – Let’s say you create a database that stores a name, home address, and car(s) owned. You make two tables, one for the address and one for the cars. They might look something like this:
Table 1
Name Address City State Zip
Eric 1234 Elm St. Chicago IL 60601
Stan 5678 Oak St. Chicago IL 60602
Table 2
Name CarModel Mileage
Eric Testarosa 20,000
Eric S-575 10,000
Stan S-575 5,000
Since “Eric” appears in both tables, the database will match “Testarosa” and “S-575” with “1234 Elm, etc.” based on the common element, “Eric” and vice versa.
Additionally, you may have a table that stores information about cars. It might look like this:
Table 3
CarModel Make
Testarosa Ferrari
S-575 Lotus
Accord Honda
In this example, Table 2 is used to join the information in Table 3 with Table 1. You now know that there is a Ferrari with 20,000 miles on it parked at 1234 Elm St. in Chicago because you know Eric lives at 1234 Elm St. (Table 1) and he has a Testarosa with 20,000 miles on it (Table 2), which is made by Ferrari (Table 3). The connections between three tables are known as “relationships”.
3. Normalization
You may be asking yourself at this time, “Why can’t you just put Ferrari and Lotus in the same table with the Eric?” At first glance it seems like a good idea that makes things a whole lot simpler and, when you’re dealing with very small sets of data and very simple databases, it is. (If this is the case, though, then an Excel spreadsheet might be a better option for you.) However, when you start working with large amounts of information, complex data, or queries, many problems arise if you do not follow a certain standard for efficient and stable table design. This standard is called “normalization” or “normal form”. There are many online resources available that discuss normalization in great detail so I will not do that. I will instead provide the essential basics.
3.a. 1st Normal Form
A database is said to be in 1st Normal Form when no records have repeating data groups. This means two things: each field should contain one unique value (ie., a ‘CarModel’ field should not contain the data “Ferrari, Lotus”); and each record should not have more than one field to store the same kind of data (ie., the records in Table 1 should not have fields such as ‘CarModel1’, ‘CarModel2’, etc.).
There are two reasons to eliminate repeating data groups. First, if you wish to search your database for a certain data, it is much easier to do so when the data is in a field by itself. Addresses are a prime example of this. If your address field says, “1234 Elm Street, Chicago, IL, 60601, USA), a simple query for everyone in Chicago will not find this record.
The second reason for eliminating repeating data groups is efficient use of space. Continuing with the address example, say we want to store both a home and business address for each person. If we put both addresses in the same record, we might have the following fields: Number, Street, St/Ave/Blvd/etc, City, State, ZIP, Country and BusinessNumber, BusinessStreet, BusinessSt/Ave/Blvd/etc, BusinessCity, BusinessState, BusinessZIP, BusinessCountry. If every entry in your database utilizes all those fields then you’re fine, but if some entries only have one address your wasted space will add up very quickly. (** See section 4xxxx)
To solve repeating data groups, make a new table with the repeating fields and an appropriate field from the first table (usually its primary key).
3.b. 2nd Normal Form
A database is said to be in 2nd Normal Form if all the fields in a record are fully dependant on the primary key. This generally applies only to tables with a composite primary key. (**See section 4.a. for more on primary keys.)
In the above example, Table 2 has a composite key; both the “Name” and “Car” fields combine to make a unique record. The “Mileage” field belongs in this table because only Eric’s Lotus has 10,000 miles on it. On the other hand, if we put the “Make” field from Table 3 into Table 2 it would violate 2nd Normal Form because all Testarosas are made by Ferrari; it doesn’t matter who owns it. Therefore, “Make” is dependent on “CarModel” but not on “Name”, so “Make” should not be in a table where “Name” is part of the key.
I know the focus of these forums is to get help after you already have a firm grasp of Access, but I have tutored enough of my own classmates to know that sometimes the basics are really confusing when your teacher sucks. So I think it's worthwhile to throw a bone to students and beginners in such a situation. I thought the final form could be as a thread where people could post questions and updates; anyone who contributes would be credited as an author.
With that in mind, does this look like it will be useful? Does anyone have feedback on how I've explained things, important points and/or topics I should include, etc.? Keep in mind this is far from the final form; I have plenty more to write on normalization and the writing in general needs to be tightened up and made more consistent. But if nobody here thinks this is a good project then I'd like to know so I don't waste any more time.
Last edited: