How to plan your Access or FileMaker Pro database – Part 1

One of the biggest mistakes new database developers make is they don’t plan.  Instead, they just open up Microsoft Access or FileMaker Pro and start creating their tables.  But, as with just about everything else in life, a little bit of planning upfront can make a huge difference down the line.  Think about it – would you dare go into a building that was designed without blue prints?

The first step in creating the “blue prints” for your Access or FileMaker Pro database is to get out some paper and pencils and draw the input forms and reports that your users will need.  Draw the input boxes, the drop downs, the check boxes, the buttons, and the labels that you want users to see and use.  Take your time and make at least one drawing for each form and each report.

Sample Database Form

As you draw your forms you should also make some notes that answer the following questions:

• Does this field have a default value?
• Does this numeric field have a maximum or minimum value?
• Is this a required field, or can it be left blank?
• Does this text field have a minimum, maximum or required number of characters?  For example, the State field should allow only two characters, while the zip code 5 or maybe 9 or 10 characters (five + four + maybe one for the dash).
• How will the date field be formatted?  Will you show the fourth of July as 7/4/2013 or as July 4, 2013 or as 4-Jul-2013?
• What values will be available from the drop down?  Will those values come from another table, a query, or will you hard-code them with a value list?
• Will users be allowed to enter values that are not in the drop down?  If so, should the new value be added to the list?

As you draw your reports you should consider the answers to these questions:

• How will this report be sorted?
• What columns will appear in the report?  In what order will the columns appear?
• Will the report include totals?  If so, how are the totals calculated (sum, average, minimum, maximum, etc.)?
• Will each page of the report include a page header and/or footer?  If so, what will it contain (company logo, page number, print date, etc.)?
• Does the report need a report header (which appears only on the first page)?
• Does the report need a report footer (which appears only on the last page)?
• Will the data in the report be based on a filter?  For example, will the user be able to run the report and designate they only want to see customers in their zip code?

Once you have created your drawings, you will be prepared to analyze them to create a “Field List Worksheet” and an “Entity Relationship Diagram” which we will discuss in future blog posts.