Database Design
For Executives & Professionals
Information is an
essential tool for any business or professional person.
All the information in the world is worthless until we can
change it into knowledge. Creating
and using a database is an effective method to harness all our information
and turning it into powerful knowledge.
The most important
aspect of designing a custom database is planning.
With good planning our databases will aid our long-term efficiency
and effectiveness. Take the
time to design and plan. Too
often our databases are created while we're in the working process, which
leads to poor design and functionality.
Plan on using a relational database – MS Access is a good choice. 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
very basic
steps for insuring that your database foundation is solid:
-
Begin with the end in mind. What is the expected out-put?
Why is the information being gathered? What reports will you
want? What is expected from the database?
-
Design all reports first.
Perhaps you need mailing labels, letters or special summary reports.
What information needs to be in them and in what format?
Do you need reports to show trends of statistical data?
-
Know the capabilities of your database.
MS Access has extensive capabilities for importing and exporting data.
- Determine your Queries. What information will be retrieved from the collected data.
- Develop your table structures. Keep things as simple as possible!
-
List all fields needed.
Group like fields together. Create a customer database
place all customer contact information in one table,
order information in another, and a history of contact with
contacts in another. Do not make separate tables for prospects,
customers, leads, etc. Instead, add a TYPE field. Good choices here
will help later when you query the tables.
-
Make each table self-contained. Eliminate redundant fields. For example, do not repeat the
customer's name, address or phone number in your Orders table.
Link tables together with key fields.
-
Assign key fields. A key
field is the column(s) used to reference any given record in a table.
This is the field that 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 a combination of fields makes a record
unique. Key fields link
tables together and help to reference information.
-
Create efficient relationships between tables.
You are working with a relational database - how will your tables
connect? You may
use ContactID in both a contact table and an order table to
give them a common link.
-
Design input forms. What
is the most efficient way to enter information with a minimum number
of keystrokes? MS Access supports subforms & learn how to use
them. 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. Remember to design the lay–out for
your out-put and reports first -
then create your tables, forms, and then final creation of reports.
Finally,
test, test, test –
revise & fine-tune your database as needed.
Be flexible - your database is a dynamic living entity. You will return to the planning and design phases as you find new uses for your custom database and your needs change.
