Take Your Web Site to New Levels

READ THIS PAGE - Prepare Your Database for Online Use

Any database driven website is only as good as the database that it runs on. If you don't take the time to organize your data carefully then you'll find yourself spending more time dealing with poor design and less time being productive.

Always Use an Autonumber Key
Normalize Your Data
Enable Cascade Update and Delete
Use Proper Naming Conventions
Setup Your Table Relationships

If you follow the suggestions on this page, your database will be ready to go once you import it into your website.

Always Use an Autonumber Key

Every single record in your database must be unique. You need a way to positively identify any individual record at any time. This is usually done by assigning one of your database fields as the Key. If you don't select one of your fields as a key field when you create your database MS Access will ask you if you want Access to create one for you. If you select YES, it will create a new Autonumber field called ID.

From this point on, ALWAYS select an autonumber field to use as your key. I know, you already have a key field. You've selected one of your other fields as a key. I did the same thing when I started.

For my nature site, I used the Latin name of plants as the key. How could I have known that scientists have a habit of changing Latin names.

On another customer table I used the Company name as my key. Suddenly their name was changed.

Save yourself a world of hassle and just use an autonumber key from this day forward.

When you use an autonumber key it will never change and it doesn't need to play any active role in your database besides its simple role as the perfect way of identifying any individual record.

One caution though. NEVER use the name ID for your key. Always change it to something more unique such as LatinID, CustomerID or ProductID.

Aaagh someone changed my key

Even worse things can happen if you don't use an autonumber. In my case terrible things happened when suddenly one of my customers changed its name. Not only did the key change, but suddenly all the other tables with date linked to that key were looking for a key value that no longer exists.

Had I used an autonumber in the beginning, I could have avoided all that hassle had I just started with an autonumber at the start.

Normalize Your Data

Normalized databases refer to those databases where redundant data entry has been eliminated through careful design. For instance, many beginner database designers try to place all of their data into a single table. If you are creating a business contact database, you will have a great deal of redundant data entry.

For instance, if there are more than one employee at a company, you will need to re-enter their company name for each individual record. This is not only extra work, but also opens the door to errors. What if you accidentally mis-spell the name on one of those records?

A better design would break your database down into numerous tables, one for Company contact data, one for Employees, one for Customers, one for Products, etc. Each table would have an autonumber key that would allow it to relate to the relevent records in the other tables. For instance, the Employees table would have a field called CompanyID which would connect that record to the correct record in the Company table.

While this sounds difficult, it is not as difficult as it appears.

Enable Cascade Update and Delete

In Microsoft Access, one of the lesser known options is called Cascade Update and Cascade Delete.

Cascade Delete

When you set up the relationships between your tables, Access creates a link between the tables. In most databases, you will have a one-to-many relationship between many of your tables. For instance a single Customer may have many orders connected to him. If you were to delete that customer from your customer table, you would also want to be sure that all of the records in the orders table were also removed.

If they are not, you will end up with a large amount of orphaned data in your Orders table. Cascade delete ensures that if a parent table is deleted then all of the data that is connected to it in subsequent tables is also deleted.

This is a key tool in maintaining your database and ensuring that your database size is kept to a minimum.

Cascade Update

Cascade Update is not usually as necessary as Cascade Delete, especially if you follow our advice and always use an autonumber field as your key.

Cascade Update ensures that if you change the value of the field that connects two databases, that the field in the connected databases will also change to reflect the change. When I first started out, I used the Latin name of birds as a key. When the Latin name was changed by scientists, Cascade Update ensured that the records in my Sightings table remained connected to the correct record in my Birds table.

Use Proper Naming Conventions

While Access will allow you to have spaces in field and table names, it can make things more difficult when you bring that data into FrontPage. To keep things simple, avoid using spaces in any of your form, query or field names.

Also, to make it easier to recognize tables from queries in FrontPage, always name tables beginning with a lower case t, for instance tCustomers. Queries should begin with a lower case q (qCustomerAlphaList).

When you access your database in FrontPage, you need to select from a long list of tables and queries all mixed together. This simple technique will make sure that you always connect to the appropriate table or query.

Setup Your Table Relationships BEFORE You Import Your Database

Before you import your database into FrontPage, make sure that you have set up your relationships between tables. Open up the Northwind Database in Access and select Tools-Relationships. You'll see the following table with all of the appropriate tables linked together.

OK, let's get started

Still keen on creating an interactive database application, then begin with importing your database into FrontPage. Click here to get started