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.

ACCESS Tips & Tricks

 

Return

To Contact us by E-Mail:  JOANS followed by @ joansvoboda.com (formatted to foil Spam Bots!)