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.

This is a question that I get more often than you might think:  How do you embed a document, such as a Microsoft Word document, into a field in a table in Microsoft Access?  It may seem like the answer to this would be complicated, however it actually is quite a simple process.

To begin, let’s define what you will actually be embedding.  You will be embedding what is called an OLE Object.  OLE stands for “Object Linking and Embedding”.  It is a standard created by Microsoft that allows you to create an object in one application (say a Microsoft Word document) and embed or link it into another application (such as Microsoft Access).  For a slightly more detailed definition from Webopedia, click here.   In our example, I will be using a Microsoft Word document.

First, go to your table in Design View.  You can do this by following steps 1-3 shown below:

1. Click on Tables.
2. Highlight your table.
3. Click on Design View.

You should see a screen like this come up:

 

This table has two fields.  One is the ID field, which is just an auto-number to be our primary key.  The other field is the test field.  This is where we will be embedding our object.  It has been defaulted to Data Type “Text”.  Change this to “OLE Object”.  Close the table and save the changes to it.

Now, let’s embed a Microsoft Word document into a sample row.  Open the table by double clicking on it. You should get a screen like this:

Right click on the empty test field of the first row.  Select Insert Object from the menu.  The following menu will appear:

Here are your options:

1. Create New - You can pick an object to create from the list box and create an empty object of that type into the test field.  You can then double click on that field and that object will open up in the native application.  For example, a Microsoft Word document will open up in Microsoft Word.  You can then save changes and it will be stored in the database. *Note: be careful, if you just close a document while it is open it will save, make sure to undo your changes before closing if you don’t want them to be saved.
2. Create from File - You can copy an existing object into the database, and then edit that copy in the database (like for a new object) without affecting the original object.  If you choose to link it (there is a “Link” checkbox for this option), the original object will be changed when you edit it in the database and vica-versa.

Choose either an existing Microsoft Word document or create a new one and click “Ok”, and there you have it.  Try editing the document for yourself and see the results.  Also, play around with the options a bit, this tutorial is just a quick way to get started with embedding objects. There are also multiple different options for using this in Forms, depending the purpose of your application.