Microsoft Access Data Normalization Fundamentals
by
Luke Chung
President of FMS Inc.
http://www.fmsinc.com
Introduction- Data
Normalization Overview
- Spreadsheet
Gurus
Efficient Storage and Unique IDs
Data Normalization Extremes
Storing Duplicate Data
Converting Non-normalized Data - Separating
into Application and Data Databases
- Seek Statements
- Summary
Sample Database: normalize.zip (22KB)
Back to Main Technical Papers Page
Introduction
The ability to analyze data in Access is a fundamental skill that all developers must master. The better you are at organizing your data and knowing how to analyze it, the easier your application development will be. There are lots of ways to analyze data and different techniques must be used depending on your goal. However, there are a few fundamentals that must be understood:
- Data Normalization
- Separate Databases for the Application and Data
Data Normalization Overview
There are lots of articles and books on data normalization. They usually scare people including me. I am not going to get into a theoretical discussion of the pros and cons of data normalization levels. Basically, it comes down to this: how to store and retrieve data efficiently. This differs depending on the database used, so the more you understand how to manipulate data in Access, the more obvious the way you should store data in tables and fields.
A primary goal of good database design is to make sure your data can be easily maintained over time. Databases are great at managing more records. They are terrible if fields need to be added since all its queries, forms, reports, and code are field dependent.
Spreadsheet Gurus
Data normalization is a particularly difficult concept for spreadsheet experts. Having been a spreadsheet developer prior to using databases, I sympathize with those struggling to make the transition. The main reason you’re using a database rather than a spreadsheet is probably because have so much data you can’t manage it properly in Excel. The fundamental advantage of a database is that it allows your data to grow without causing other problems. The big disaster in most spreadsheets is the need to add new columns or worksheets (for new years, products, etc.) which cause massive rewrites of formulas and macros that are difficult to debug and test thoroughly. Been there. Designed properly, databases let your data grow over time without affecting your queries or reports. You need to understand how to structure your data so your database takes advantage of this. How you store your data is totally different from how you show it. So, stop creating fields for each month, quarter or year, and start storing dates as a field. You’ll be glad you did it:
Non-Normalized
"Spreadsheet" Data
Normalized Data
Both tables in the example above contain the same data, but they are distinctly different. Notice how the normalized table lets you easily add more records (years) without forcing a restructuring of the table. In the non-normalized table, adding next year’s data would require adding a field. By avoiding the need to add a field when you get more data, you eliminate the need to update all the objects (queries, forms, reports, macros, and modules) that depend on the table. Basically, in databases, new records are "free" while new columns are "expensive". Try to structure your tables so you don’t need to modify their fields over time.
Efficient Storage and Unique IDs
A fundamental principle of data normalization is the same data should not be stored in multiple places. Information that change over time such as customer names and addresses should be stored in one table and other tables referencing that information should link to it.
Unique IDs (key fields) with no connection to the data are used to link between tables. For instance, customer information should be stored in a customer table with a Customer ID field identifying the record. Access lets you use an AutoNumber field to automatically assign new ID numbers. It doesn’t matter what the ID number is or whether they are consecutive. The ID number has no meaning other than identifying the record and letting records in other tables link to that record.
I’ve seen databases where people use ID numbers that combine a few letters of the last name, first name, and number. That makes no sense and creates a mess over time. The ID should just be a number and if you want your data sorted in a particular order, use a secondary index.
Data Normalization Extremes
It is important to not take data normalization to extremes in Access. Most people are familiar with separating Customers into its own table. If not, they quickly discover why they need to. But what about optional fields like telephone numbers: business phone, fax number, mobile phone, modem, home phone, home fax, etc.? Most customers won’t have all those numbers, but you may want to store them for those that do. There are three approaches:
- All the fields are in the Customer table.
- A separate table is created for each type of telephone (a one-to-one link). The table would contain the customer ID and telephone number.
- A telephone table is created with these fields: customer ID, the Telephone Type ID, and number so you could conceivably have unlimited phone numbers (a one-to-many link).
There are arguments for each alternative and to some extent it depends how well you know your data. Data normalization purists and programs such as Erwin often suggest separate table(s). Obviously option 3 is the most flexible since it allows you to support an unlimited number and type of phones. If you cannot limit the number of alternatives, this is your only choice. However, if you can limit the types, you should opt for option 1 in Access.
First, Access stores data in variable length records. One of the reasons for data normalization is to save disk space. Old file formats such as dBase, FoxPro, and Paradox stored data in fixed length records with each record taking the same space regardless if its fields were blank. The more fields, the larger the table. Not only is disk space is cheap today, Access records only grow if data is contained in them. Therefore, this kind of data normalization is not necessary for efficient data storage in Access.
Second, and more important, the retrieval of data across multiple tables may be an unnecessary hassle. If you are always going to show the customer’s phone and fax number, retrieving those records out of another table is an unnecessary and will hurt your performance. The way Access is designed, it is much easier to just pick the fields from the Customer table rather than using a separate query or sub-report to grab each telephone type separately.
Storing Duplicate Data
It is very important to remember there are situations where you must store what seems like duplicate data. This is most often related to the passage of time and the need to preserve what happened. The typical case is an order entry form where you have an Invoice table linked to Customer and LineItem tables. Each record in the LineItem table is linked to a Product lookup table containing product descriptions and pricing. The LineItem table stores a ProductID to designate which product was purchased.
However, this is not sufficient. The LineItem table must also store the Price and Description at the time of the order. Over time, prices and descriptions in the Product table often change. If it is not preserved in the LineItem table, you will be unable to view or print the original invoice, which could be a disaster (you would actually show the current description and price). Therefore, during data entry, when a Product is selected, you also need to retrieve the Price and Description to fill in those fields in the LineItem table. The customer information may also change, but that’s actually good since we want the latest customer address to appear.
Converting Non-Normalized Data
In the example, we show examples of non-normalized and normalized tables. How do you get from one to the other? You could manually run queries, but that would be very cumbersome. A simple solution is to use Excel’s Transpose feature. In Excel, just highlight the data to transpose, copy it, then select Edit | Paste Special and select the Transpose option.
Within Access, the solution requires some code:
Sub TransposeData()
Const cstrInputTable = "Federal Budget Non-Normalized"
Const cstrOutputTable As String = "Federal Budget"
Dim dbs As Database
Dim rstInput As Recordset
Dim rstOutput As Recordset
Dim intYear As Integer
Set dbs = CurrentDb
Set rstInput = dbs.OpenRecordset(cstrInputTable)
Set rstOutput = dbs.OpenRecordset(cstrOutputTable)
If Not rstInput.EOF Then
' For each column in the Input table, create a record in the output table
For intYear = 1990 To 1997
rstInput.MoveFirst
rstOutput.AddNew
rstOutput![Year] = intYear
' Go through every record in the Input table
Do
rstOutput(rstInput![Data Type]) = rstInput(CStr(intYear))
rstInput.MoveNext
Loop Until rstInput.EOF
rstOutput.Update
Next intYear
End If
rstInput.Close
rstOutput.Close
dbs.Close
End Sub
In the TransposeData procedure, we basically go down each year in the original table (Federal Budget Non-Normalized) and create a new record in the target table (Federal Budget). Since we know the column names are years, we use a For..Next loop to step through each year to transpose. The fields in the target table correspond to the value in the original table’s [Data Type] field. A more general procedure is included in the database that accompanies this article.
Separating into Application and Data Databases
Since Access stores all its objects in one MDB file, it is very important to separate your application and data into separate databases. This can be a hassle during development since it makes it difficult to create and modify tables, but before you put your database into production, you need to separate the two and have your application database link to the tables in the data database. The main reason is to be able to update the application without wiping out the data (assuming your users are adding and editing data). It is also important if you are deploying a multi-user application. The application MDB should include all the queries, forms, reports, macros, and modules. It should also include any tables that are user specific (tables to store user options, temporary tables, etc.).
Done properly, you can deliver a new version of your application, replace the existing ones, and use the current data. If structural changes are necessary with the tables, you will then need to manually modify the data database. The application database can be linked to the data database through the Linked Table Manager under the Access Tools | Add-Ins menu.
You can also write some module code to update a linked table (from Total Visual SourceBook):
Function ReLinkTable_TSB(strTable As String, strPath As String) As Boolean ' Comments : Re-links the named table to the named path ' Parameters: strTable - table name of the linked table ' strPath : full path name of the database containing the real table ' Returns : True if successful, False otherwise ' Dim dbsTmp As DAO.Database Dim tdfTmp As DAO.TableDef Dim strPrefix As String Dim strNewConnect As String On Error GoTo PROC_ERR Set dbsTmp = CurrentDb() Set tdfTmp = dbsTmp.TableDefs(strTable) strPrefix = Left$(tdfTmp.Connect, InStr(tdfTmp.Connect, "=")) strNewConnect = strPrefix & strPath tdfTmp.Connect = strNewConnect tdfTmp.RefreshLink ReLinkTable_TSB = True PROC_EXIT: dbsTmp.Close Exit Function PROC_ERR: ReLinkTable_TSB = False Resume PROC_EXIT End Function
Seek Statements
For the most part, separating the data into a data database does not affect your application. The queries based on the linked tables remain the same, as do your forms, reports, and code. The main exception is Seek statements. Seek statements are used in code to find a record. They are very fast because they use an index you specify. For example, for a given table (strTable), index (strIndex), and search values (varValue1 and varValue2):
Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim fFound As Boolean Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strTable) rst.Index = strIndex rst.Seek "=", varValue1, varValue2 fFound = Not rst.NoMatch
However, this code fails if the table is linked. This is very frustrating and many developers resort to the FindFirst command instead. Unfortunately, FindFirst is very inefficient. It does not use an index and performs a slow sequential search through the entire table. This can be very painful for large tables. The good news is that you can use Seek on linked tables. It’s a matter of properly identifying the database where the table resides. Often, you will know the linked database name and you can easily set the database variable (where strLinkedDB is the linked database name):
Set dbs = DBEngine.Workspaces(0).OpenDatabase(strLinkedDB)
The example below is a general solution where the code tests a table and changes the database variable if it is linked:
Dim dbs As DAO.Database Dim tdf As DAO.TableDef Dim strConnect As String Dim strLinkedDB As String Dim rst As DAO.Recordset Dim fFound As BooleanSet dbs = CurrentDb Set tdf = dbs.TableDefs(strTable)' Connect = "" if it is not a linked table strConnect = tdf.Connect If strConnect <> "" Then ' Database name follows the "=" sign strLinkedDB = Right$(strConnect, Len(strConnect) - InStr(strConnect, "="))' Change database handle to external database dbs.Close Set dbs = DBEngine.Workspaces(0).OpenDatabase(strLinkedDB) End IfSet rst = dbs.OpenRecordset(strTable) rst.Index = strIndex rst.Seek "=", varValue1, varValue2 fFound = Not rst.NoMatch
Summary
By normalizing your data and splitting your database into separate application and data MDB files, you’ll go a long way toward establishing a solid foundation for your database development efforts. Data normalization not only makes your data be more accurate, it makes it easier to analyze, and more importantly, maintain and expand. Separating your application and data databases enables you to support multiple users and upgrade the application without wiping out their data. Assuming the application doesn’t change that often, the separation also makes it easier to just backup the data database since only that is changing everyday.
About the Author
Luke Chung is the president and founder of FMS, Inc., a database consulting firm and the leading developer of Microsoft Access add-in products. He is the designer and co-author of several Access add-ins including Total Access Analyzer, Total Visual CodeTools, Total Access Detective, Total Access Emailer, Total Visual SourceBook, Total Access Statistics. He has spoken at a variety of conferences and user groups across North American and Europe.
Copyright © 1998-2008, FMS Inc. All rights reserved. This information may not be republished, reprinted or retransmitted in any form without the express written permission of FMS Inc. The information provided in this document is provided "as is" without warranty of any kind.
