Thursday, April 29, 2010

Good Databases (Part 1)

It has recently occurred to me that the design, construction, maintenance, and information-mining of Good Databases are the quintessential keystones of knowledge for any advanced Excel user. Today will be the beginning of a Three-Part Series on this vital topic.

Creating a Database in an ongoing Excel workbook can save you time, money, and frustration. By creating a database for information that is routinely updated, you can automate your reports and simplify your users’ interface.

Today we will look at what constitutes a Good Database, and what pitfalls to watch out for.

First of all, a database should contain data, and that is all! No formulas should exist in a database, just pure Data waiting to be turned into Information on a separate worksheet.

Secondly, there should be no blank rows (called “Records” in a database) and no blank columns (called “Fields” in a database).

Thirdly, put only one piece of data in each field. This will eliminate the need for repeating fields, and make your information-mining much easier.

Lastly, make sure the information is entered in the proper field. If the data entry person (maybe you) cannot find the right place for a piece of data, perhaps the database needs some redesigning.

Next week, we will look at some phenomenal ways of deriving information from your new, improved database. You can use one of your own, or create a database using the example below.

Fantastic stuff coming! Cheers!

No comments: