|
Date Published: 2000-03-01
by Marnie Pehrson
http://www.pwgroup.com
The most overlooked task in designing a custom
database is planning. Too often we plan our database while we're creating it
which leads to poor design and functionality. Planning and developing your table
structures is equivalent to building the foundation of a home. If you cut
corners on the foundation of your home or use cheap materials, your home will
have structural problems down the road. Likewise, if you have poorly structured
tables in your database, you'll be limiting the functionality of your database.
Here are some basic steps for insuring that your database foundation is solid.
Begin with the end in mind.
Get feedback from anyone who will be
using the system or receiving reports generated by it. What reports will they
want? What do they expect from the database? Communication with everyone
involved is critical. Get others involved and excited about the new database.
Too many database applications fall by the wayside - not because they are not
good, but because they were the ''brainchild'' of only one person. If that
person leaves the company, the system will probably not be maintained and used.
Sketch all reports first.
Perhaps you need mailing labels, letters or
special summary reports. What information needs to be in them and in what
format? Again, involve others, when applicable, in discussing reports.
Learn the limits of your database software.
Will it display
information in the desired format that you need for your reports? Will it import
and export in formats that allow you to use other software? You can gain even
greater flexibility by using other software applications in conjunction with
your database program. For example, add a desktop publishing flair to your
letters by exporting the results of queries to a word processing program and
merging there. Most database programs will export to word processing merge data
files. Microsoft Access, for example will export a Microsoft Word merge data
file.
Determine your selection criteria.
How should you sort or query your
database? For letters and labels, will you normally print by state? Zip code?
Company name? Or perhaps you will print contacts by their type, such as a
customer, prospect, lead or OEM. Ask yourself these questions for each report
that you sketched in step two.
Develop your table structures
List all fields needed.
Brainstorm and write everything down that you
might need. Specify the lengths and data types of each field. For example, Zip
Code would be Text 10. Although a zip code may seem to be a number, it should be
considered a text field because of the leading zeroes and dash. Tip: Unless you
have a special reason for querying by the +4 on a zip code, keep the zip code
all one field.
Group like fields together.
If you were creating a customer database,
you would place all the customer contact information in one table, all the order
information in another, and a history of contact with people in another. Do not
make separate tables for prospects, customers, leads, etc. Instead, add a field
for Type of Contact in your contact table. Then you can query on that field
later.
Make each table self-contained.
Eliminate redundant fields. This is a
beginner's biggest mistake. For example, do not repeat the customer's name,
address or phone number in your Orders table. What happens if a customer changes
his address and you have the address repeated on all of his orders? You'll have
to go back and change it everywhere. Instead, the name, address, and phone
number should only be found in the Contact table. Change it there, and it
ripples throughout your system. Link tables together with key fields.
Assign key fields.
A key field is the column(s) with which you can
reference any given row in a table. It is what makes each row unique. For
example, ContactID is the key field in our Contact table. InvoiceNo is the key
field in our Invoices table. Sometimes you need to use a combination of fields
to make each row unique. This is called a concatenated key. Use key fields to
link tables together and look up information. Speed up queries and searches by
creating indexes on your key fields.
Decide on the relationships between tables.
How will your tables
connect? For example you might use ContactID in both a contact table and an
order table to give them a common link.
Layout your input screens.
What is the most efficient way to enter
information with a minimum number of keystrokes? If your database program
supports subforms, learn how to use them. For example, you can combine contact
information and a history of every interaction you have had with that person on
one screen. The contact information would be in the main form, and the list of
activity with that contact would be in a subform.
After the planning is complete, the creation process should be relatively easy if you know how to
use the basics of your database. Create your tables, forms, and then reports.
Finally, test, test, test. If you are revising an existing system,
continue to use it simultaneously with the new system until everything is
optimized and correct. Often you do not find errors or missing fields until the
database is fully loaded with data. Ask anyone who will be using your database
to use it and offer suggestions and comments. Expect to fine-tune your database.
Your database is a living entity. You will return to the planning and design
phases as you find new uses for your custom database and your needs change.
by Marnie Pehrson
http://www.pwgroup.com
Marnie L. Pehrson creates database driven Web sites. These are Web sites that
use relational databases as their foundation, and allow visitor interactivity
through the Web. Sites she has programmed include IdeaMarketers.com and Pros-N-Cons. If you need help with a
database driven site, call 800-524-2307 or email ces@pwgroup.com.
|
