Microsoft Access Database Normalization Versus DeNormalization

Microsoft Access Database Normalization Versus De-Normalization

To normalize or to de-normalize that is the question!

Database normalization is widely misunderstood. Any new database system should adopt some level of database normalization (a minimum of ‘Third Normal Form (3NF)’) but what does this really mean?

The idea behind database normalization is to protect and maintain the data integrity keeping your data complete and consistent when storing data (input) to handling effective reporting (output). Keeping records in one place which can be related and impact data values in other places is the trick and this is why normalization is normally the methodology used here when designing an Access database.

Having smaller and re-usable manageable units of information maintains the data integrity and developers can be proud of the fact they have a true RDBMS (relational database management system).

Technical advantages include better memory management with less space used and faster performing results. but is it always justified?

database normalizationMicrosoft Access Database Normalization Versus De-Normalization

So database de-normalization is by comparison inefficient, clumsy and sometimes slower! But it still can serve as a useful method of delivering data to end-users.

Take a closer look at the term ‘de-normalization’ and ask yourself what this really means. In order to ‘de-normalize’ anything, it must first have been normalized and that is the important point here to note.

In the context of Microsoft Access with database normalization, I’m now implying that my database structure was designed in a normalized state (which of course is the correct way) but have chosen to de-normalize it for the purpose of allowing general users to produce easier reports.

In Access, we normally use a query to serve and run a report and this query may have been built using one or more tables (as related) and if you look at this query in isolation you can say the query has been de-normalized (one view of related data) for the purpose of reporting.

If you are designing an Access database for users to build and run their own reports, you may want to include another tier of queries (as de-normalized) to help make building reports easier, more user-friendly and intuitive to use. This is what is sometimes deemed as a ‘flat-file’ database (which is what an Excel spreadhseet is).

Designing an Access database is an overlooked process and one which can be costly as the system evolves. Get a grasp of this methodology fast, start by engaging in my eBooks, one to start with is How to Build an Access Database which covers the principles of database normalization too.

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

One Response to Microsoft Access Database Normalization Versus DeNormalization

  1. Ben Beitler says:

    The process of Database Normalisation was developed by E. F. Codd who is widely considered the father of relational database theory.
    There are five levels; aim for level 3 (3NF).

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.