Access Database Design Process - Overview

Designing a database is both an art and a science. In our Access Database Design Bootcamp course, I teach a step-by-step process to designing a database. The class also includes a case study, where students have the opportunity to design a database in class.

Source: Access Database Design Bootcamp.

1. Gather Requirements.
   Interview the primary decision maker and key database users to determine the purpose and scope of your database. Collect sample files and documents to determine what they are currently using to accomplish database activities. In some cases, the team may be using a hodge-podge of Excel and Word files to accomplish the tasks they want the database to handle.
   
   
2. Sketch Forms & Reports.
   Sketch all forms and reports on paper and give them to the key users to approve.
   
   
3. List fields.
   Use the approved forms to list every single item you need to track in the database. These will become your database fields. Describe what each field is supposed to track.
   
   
4. Map fields to tables (on paper).
   Create a preliminary list of tables. Assign each field to a table.
   
   
5. Normalize your draft tables (on paper).
   Normalization will reduce redundancy and errors.
   
   
6. Establish relationships between tables (on paper).
   Link tables to establish 1-to-many relationships, and create linking tables to implement many-to-many relationships.
   
   
7. Create tables (in Accesss).
   Now that you've sketched your tables on paper and mapped your relationships, create your tables in Access and enter sample data.
   For more information, see our Access Module I - Tables Class.
   
   
8. Create your queries, forms and reports (in Access).
   Use the documents you've worked on thus far to create your queries, forms, and reports. For more information, see our Access Queries, and our Forms & Reports classes.
   
   
9. Test your database.
   Test your database yourself, and then present it to your key users to review and test. Repeat this process until you have a deliverable database.