Getting the Most out of Microsoft Access ®  Part 2:   Planning Your Database

Part 1

Access offers many tools to aid in the development of databases. Wizards and templates allow even novices to create databases quickly and easily. Because it is so easy there is a temptation to sit down right away and start building tables and forms. However, like a well-built house, a good database starts with a blueprint.

Planning a database can be time consuming but it will save time in the long run. Modifications and enhancements become much more difficult to implement the further along you are in the development process. 

The following steps should be taken when planning your database. The process is somewhat iterative. As each step is completed, it may be necessary to go back to the previous steps and revise your plan to reflect new information.

In each phase of the planning process, it is wise to conduct interviews with the people who have a stake in the project’s outcome, such as the people who will be entering the data into the system and the people who will want to extract information from it. This will help to ensure that the plan is on track, which will, in turn, save time and resources.

Step 1: Define the Objective and Determine the End Result

In order to develop a database capable of meeting your organization’s needs, it’s important to know what those needs are. Try to see the “big picture” and anticipate future requirements in order to prevent your database from becoming obsolete for as long as possible. At the same time, however, you may find it necessary to limit the scope of the database. Depending on the desired end results, it may be wise to develop two or more separate databases. For example, if you’ve decided you want to manage your payroll and your inventory there is probably little advantage in trying to accomplish these goals with a single database.

Conduct interviews to determine the expectations of those involved. Determine what types of information end users want to extract from the stored data. Try to obtain samples of reports that will be expected.

Step 2: Analyze the Current System

Whether the existing system is electronic or paper based, it will provide a good starting point for planning your database. Gather and review samples of paper forms that are being used as well as reports that are being generated manually and/or electronically. You might find it useful to add screenshots of data entry forms to your file if possible. Use these reports and forms to begin making a list of the data elements that will be stored.

Conduct interviews to determine the strengths as well as the weaknesses of the current system. Document the workflows and processes. While some of these will evolve to fit the new system, others may need to be accommodated.

Step 3: Develop Data Structures and Relationships

Using the information gathered in the previous steps, decide on what subjects will be covered by the database. These subjects, or entities, will eventually become tables in your database. An example of an entity would be a customer, an inventory item, or an order. Once you have determined the entities, decide how they are related to one another.

In our last article, we discussed the types of relationships that can exist between tables, i.e., One-to-Many, Many-to-Many, or One-to-One. If the relationship between two entities is Many-to-Many, you will need to create an additional entity between them that has a One-to-Many relationship with each of the original entities.  See figure 1.

The final step in this process is to assign characteristics, or attributes, to the entities. Review the list of data elements you made in Step 2. These should become attributes in an entity you have defined. Do not include calculated fields as attributes, though. Fields like “Order Total” or “Average Age” can be easily calculated from other data fields and do not need to be stored. Be sure to assign each entity a field or combination of fields that can be used as a unique identifier. This will become your Primary Key for the table that is developed from the entity.

Interviews can help you with this step by helping to identify which entity an attribute belongs to. They can also be used to clarify the relationships between entities.

Step 4: Determine Business Rules

Access has many ways of allowing business rules to be enforced. For example, data that is entered in a field can be restricted to a specified list of values or can be required to be numeric. Formatting features can be used to ensure that information like phone numbers or social security numbers are entered properly. You can also specify which fields are required to contain data. These are powerful features that help keep data clean and usable. Making good use of them, however, requires knowledge of what the business rules are.

Some rules will be easy to determine. It’s probably safe to assume that an order must consist of at least one item, ages must be greater than zero, and social security numbers must consist of nine digits formatted as “###-##-####”. Other rules, such as what constitutes a valid order status, when an invoice is considered past due, and whether or not a specific field is required will be determined through the interview process.

Step 5: Develop Views and Reports

As we discussed in our last article, Access is a relational database and much of its power comes from its ability to combine information together in a variety of ways. Subsets of tables can be viewed based on specified criteria and tables can be joined together using Primary and Foreign Keys. Calculations can also be performed on various fields to provide additional information. All of these can be displayed in a spreadsheet-like format called a View (or Query), in a series of forms and sub-forms, or in printable reports.

User interviews will provide feedback on what kinds of information needs to be extracted from the database and how it should be made available for review. Be sure you have included all the necessary fields to provide the requested information. For example, if a report is to be generated showing the number of sales per month by employee, the table that maintains the order information will need to have fields for both the date of sale and a reference to the employee that made the sale. Likewise, if a report needs to group customers by sales region, then it will be necessary to be able to associate that information with the customer.

Step 6: Implement the Design

Congratulations! Now that you’ve got a plan, the hard part is over. It’s time to fire up Access and start converting your plan to reality. You may still find that refinements are needed but you will certainly have a solid foundation to work from. Future Bits and Bytes articles will show you how to implement your design in Access using the many tools and wizards Access provides. 

Part 3