Microsoft Access Database Design Concept – Step 1 of 7 – Overall System Design for Microsoft Access

By Kathi J. Pettry 11 months ago

Working with Microsoft Access designing a new database, the question here is what is the core process of the database that you are trying to define? Access Database design is more of an art than a science and common sense will prevail.

By taking the time to identify the core process correctly, you will find that additional processes will plug in easily keeping the structure of the system stable and solid enough as time goes by and data volume increases. If you apply some of Microsoft Access techniques including ‘Database Normalisation’ rules (up to the third norm) and deal with relationships (tables joins) and setting their keys & indexes, this too will determine the solid foundation to build on.

Scenario: Let’s take an example process like a ‘Sales Order Processing’ database system which has a list of international customers based in the UK, USA and/or Canada who place orders (eventually invoiced) for one or more food products (items) periodically (for a single order) and is dispatched by an independent distributor. The company is deemed a medium sized business and the sales team are responsible for processing their own orders. So, the first consideration will be the types of several tasks required to fulfill this process; here are 6 examples:

  1. A facility to enter customer information including company name, address and contact details.
  2. A facility to enter product information including product name, price and stock inventory information.
  3. The process to raise the order and assign quantity, price and any special offers plus the need to assign a freight company to distribute the goods.
  4. What types of reports will personnel need from a single order to customer lists and analysis?
  5. Any management reports to be included on a monthly basis
  6. The personnel responsible for running and maintaining the system from raising the order, controlling stock, running more sensitive reports to maintaining the database system (administrator role).

This list should be more detailed and explicit from the examples above but it hopefully focuses your mind to the type of database system required. This is an iterative procedure. That is before you start the reverse engineering process itself (which is discussed in the step 2 of 7 article), you need to repeat the tasks required by looking again at each item and make sure nothing has changed in the design objectives.