Microsoft Access Field Consistency Tips
Introduction
For simplified understanding and maintenance, field names should be
consistently defined throughout an application. This makes it easier for
people to understand (the same field name contains the same type of data
throughout the application) and avoids programming and query problems
(data from the same field name can always be linked or inserted into the
same field name in another table).
There are Many Field Properties
Access allows you to easily define a field in any table with a
variety of property settings such as the field type, field size (for
text fields), description, format, input mask, table lookup, whether it
allows zero length strings, whether it’s required, etc.
Use the Same Name for the Same Data
Fields containing the same information should have the same field
properties, or at least the most important field properties. This would
begin with field name. If you have a CustomerID field which is an
AutoNumber in the customer table, and is used by other tables, using the
same CustomerID name would be very helpful to show it refers to the same
data. Avoid using other names such as CustID, Customer, Customer ID,
etc. Whoever needs to maintain your application in the future will
appreciate your consistency.
Use the Same Data Type and Size
One would presume that data could be freely moved among identically
named fields. However, this may not be the case if the fields are not
consistently defined. In particular, the data type and size (for text
fields) should be identical.
For instance, the CustomerID field, which is an AutoNumber, should be
a Long Integer in the other tables. A CustomerName field, which is 50
characters in the customer table, should be the same size in other
tables. Failure to do so may result in data being truncated if it is
moved from the larger field to a smaller one in an Append query, or may
crash if you do this in code.
Other Important Properties
There are other field properties where it is are important to be
consistent but tend to be less problematic because developers rarely set
them or they are not inconsistent as often. These properties include:
Allow Zero Length Strings
If this setting for text fields is set to Yes, the field may contain
Nulls or zero length strings. If you move a zero length string to a
field that doesn’t allow this, you’ll encounter a crash. We recommend
all text fields allow zero length strings.
Required
Leaving a required field blank could be a problem.
Unicode Compression
If you are dealing with Unicode data (associated with two byte
international characters), you need to make sure your settings are
consistent to avoid moving Unicode data into fields that cannot handle
them.
Field Consistency Verification Tools
In addition to documenting table structures and a wide variety of
database analysis,
Total Access Analyzer
from FMS, Inc., performs a detailed field consistency check for Access
databases. This analysis flags identically named fields which do not
share consistent data types and sizes. When an inconsistency is found,
all the tables where the field name exists are listed along with their
corresponding field description, data type, and size. This simplifies
the process of cleaning up the structures and minimizing the problems
associated with inconsistent field names.
For SQL Server, FMS offers
Total SQL Analyzer Pro that provides server and database
documentation along with field consistency analysis for tables within
each database.
Conclusion
After data normalization, consistently named fields is the foundation
of good database design. Making sure the same field name represents the
same data and can always store the same data not only makes good design
sense, it simplifies the understanding of your database and eliminates
problems associated with moving data between tables. Good luck with your
database development.
Author: Luke Chung,
President and Founder of FMS, Inc. FMS, Inc. (www.fmsinc.com)
is the leading provider of 3rd party products for Microsoft Access users
and developers. Visit the FMS web site for additional Access resources,
utilities, technical papers, demos, and product information.
Back to Main Technical Papers
Page
|
