Microsoft Access Database: Think Relational, Not Flat with An Access Database

Microsoft Access databases are typically used as relational data sets of information but the clarity of what is a relational database versus the ‘flat‘ file database seems to muddy the waters from at times.

I personally have fallen foul to when I have used a relational, flat or both types of data capture processes and this is down to the lack of planning – shame on me!

It’s a common mistake to use ‘Yes/no‘ data types fields to attach transactional values in the same table as holding say, profile information. For example, if I have a list of people and I want to keep track of the their food likes (and dislikes) then I should really have three tables related to form a flexible database management structure meeting the rules of how you use Microsoft Access and not keep it all in one table which is tempting. You may as well leave it as an Excel spreadsheet if you want this effect.Microsoft Access Flat File v Relational Tables

In the above illustration notice how the first table (using just one Microsoft Access table) contains the obvious names but it also contain the moving data of their likes and dislikes of various food types.

The other part which is the recommended way to capture the same data forming the relational database is deemed the better practice but as you can see has three tables with a common field between the joining tables.

Why use this Structure at all in an Access Database?

I could list many reasons for (and some against) using the latter example but this is about using Microsoft Access for what it really was intended and here are are just a couple of reasons why.

  1. From a design point of view, what if I wanted to add another food type lets say ‘Japanese Food’ (Sushi)? In the first example, I would have to redesign and add another field to cater for this. In the relational structure, I simply add another new record (to the ‘food type’ table) ready to be tracked in the ‘People Likes’ table. It means no redesigning of a table, just adding and maintaining data sets in tables.

     

  2. What if we had two people with same name (which happens)? By having a dedicated table to capture just ‘People’ which generates a unique identifier, it’s the identifier value that binds to the other related tables maintaining data integrity.

Finally, Microsoft Access other objects namely forms and reports automatically recognise a relational database structure and generate views to make it seem as one view but encapsulating the related tables for you.

Microsoft Access Database – What to do next?

Take some action and learn about Microsoft Access relational databases well. My eBooks will be a good start and I offer an eBook Bundle covering all the main objects and the principles, techniques and step-by-step instructions. They all come with a 100% money back guarantee with email support too which can be purchased individually too – what do you have to lose?

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

One Response to Microsoft Access Database: Think Relational, Not Flat with An Access Database

  1. Naomi says:

    Exceptional page, We are checking back on a regular basis to search for up-grades.

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>

Confirm you are human.