Microsoft Access Database Design Concept – Step 1 of 7

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.

Tip: It may also be helpful to try and prioritise the task list so that it reflects your business key objectives against any ‘wish list’. This will also keep the focus so that you do not deviate from the main core process.

The only way to confirm a final list is looking good is to consult with as many key personnel as possible (not just management but end-users too) and take as much time as required – Don’t rush it!

I invite you to keep up to date with my articles and can be found at

There are plenty of articles and tutorials with other useful links too.

From Ben Beitler – “Your Access Database Expert”

Article Source:

There are 7 steps in this series of posts (to follow over the coming weeks) which this being the first explains the database plan of the overall system. To gain a complete and more detailed explanation of all the seven steps, you may want to invest a few £ pounds (with a money back guarantee!) in How to Create an Access Database eBook.

This entry was posted in Database Theory, MS Access, Utilities and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>