One article update and a new Tip of the Day, see below:

• Like that update title? Check out today’s Tip of the Day concerning maxing out your 401k match bonus.
• If you’re having trouble getting your tables right in Microsoft Access, maybe it’s because you’re not following a good design process. See the latest article for Microsoft Access about the Basic Table Design Process.

How you design your tables can make or break your database. It doesn’t matter what you have working on top of it (i.e. forms, code modules, etc.), if your underlying table structure is not sound you’re in for a lot of headaches. With that being said, designing tables for your database doesn’t have to be torturous, as along as you follow a few basic guidelines.

First, make sure you fully understand the purpose of the database. If you don’t, you’re not going to be able to create all of the right tables and relationships to make it work correctly. Get the requirements from your client (which could just be your supervisor), and make sure that you ask questions about anything you are not sure of. One thing is for certain in any kind of project lifecycle: The farther down the road a problem gets, the more costly it is to fix it.

Next, before you create even a single table you need a Database Blueprint. This is basically the structure for your database. It should include all of the tables that you want to create as well as their associated fields. Also, the Primary Key(s) for each table should be specified. Once this is in place, creating the tables themselves is quite simple.

How do you go about creating this blueprint? First, you need to decide what tool you are going to create the blueprint in. Unless your database is going to be pretty large, you can probably use Microsoft Excel. If you think your database might be relatively large (say 20+ tables), there are a number of commercial software products out there that are specifically designed for database design. These can get expensive, but they are worth the money in the long run.

Let’s say you decide to use Microsoft Excel. I would suggest creating a template to display the table layouts, then creating a separate Excel document based on your template for each planned table. Some of the basic fields in your template should be:

• Table Name
• Field Name
• Field Data Type
• Field Size
• Field Description

Also, you should highlight the Primary Key(s) in some way so that they are easily identifiable. The Field Description is especially important to include, because if somebody who is not familiar with the database needs to change it later on, these descriptions will help them to understand how the database works.

Now that we have our template ready, it’s time to get down to work. Layout all of the information that you think you are going to need to store in the database. For example, if I’m creating a database to store customer information, I’m going to need their names, addresses, payment information etc. After you list all the information you need, group this information into logical sets. You can use the same field more than once if you have to.

Here’s an example containing the following fields:

• Customer Name
• Customer Address
• Customer Payment Type
• Order Number
• Order Description
• Order Due Date

It looks like I’ll want to create two tables out of this: One to hold the first three fields, and one to hold the last three. We’ll call them “Customer Information” and “Order Information”. The initial tables are set, but how will they relate to each other? This is the next step: Determining the table relationships. In the above example, it looks like I’ll want to be able to link an order to a specific customer. To do this, I’ll add the “Customer Name” field to the “Order Information” table. This way, when we are processing an order, we can use the “Customer Name” to do a look up on the “Customer Information” field to get their address and payment information.

*Quick Tip: If you can make your table lookups use number compares instead of text compares, do it. Text compares are generally slow than number compares. In the above example, you could add a unique ID number per customer then use this ID number on the “Order Information” table instead of the “Customer Name”.

Also to note, I used descriptive field names in this example. This is something that you must be aware of every time that you are designing any kind of database. Your field and table names should always be descriptive of what they represent. For example, if you name your tables “Table 1, Table 2,…” you are just going to get confused later on as to what table holds which information. This is especially true in large databases. Not only will you get lost in it, but anybody else who tries to maintain it will be unable to do so as well.

Now decide what should be the Primary Key(s) of each table. You can have more than one, and this is common in complex databases. For our examples, let’s make “Customer Name” the Primary Key of both tables.
Your Primary Key needs to always be a unique identifier for a single row on the table. This is an important decision to make, as it could have major consequences to your design later on if you have to change it.

In summary, we have accomplished the following:

1. Have a full understanding of our database requirements.
2. Decided to use Microsoft Excel to create our Database Blueprint in.
3. Created our template to use when designing our tables.
4. Decided what information we’ll need to store our database.
5. Sorted this information into logical groups (our tables).
6. Defined the relationships between our tables.
7. Completed our design for each table

Our Database Blueprint is now complete. Take another look at it to make sure you didn’t miss anything. It’s also a good idea to have somebody else review it to see if they can add any input.

You’re now ready to actually build your tables. Remember, you can change something if you have to, but make sure to keep your designs updated at all times. This is extremely important because if your designs are not up to date, you have to go into the database each time you or anybody else has a question about a field.