- database solutions and downloads for microsoft access

Database Design

Creating and using Descriptive and Meaningful Field Names in your Database Design:

A field in your database represents a characteristic of the subject of the table to which it belongs. If the field is appropriately named it should be easy to identify the characteristic that the field is supposed to represent. If a field name is ambiguous, unclear or is vague it may suggest that the purpose of the field is not fully identified.

How should you create Database Field Names

  • Define a unique, descriptive name that is meaningful to the organisation. The field name should only appear the once in the entire database; the exception to this rule being when the field serves to establish a relationship between two tables. Ensure that the field name is descriptive enough to convey its meaning to everyone who views it.
  • Define a name that accurately and clearly identifies the characteristic that a field represents. Using "Contact Phone Number" is an example of an inaccurate, possibly ambiguous name. Which type of contact number does this field represent: Home Phone, Work Phone, Mobile Phone? This needs to be more specific. If we need to store each of these numbers, we will need to create "Home Phone", "Work Phone" and "Mobile Phone" number fields.

We may at times, need to resolve generic field names, such as "Address", "Town" and "County" by using an appropriate table name as a prefix for the field name. If you have information on both Employee and Suppliers for example you should consider naming fields using "EmpAddress" or "SupAddress" to define which set of data that these fields belong to.

  • Try to use the minimum amount of words to portray the meaning of the characteristics that the field represents. If possible, you will want to avoid lengthy field names, however it is also best to avoid using single words as a field name if that word will not sufficiently define the field. For example, if you are wanting to record the date when an Employee joined the company, "Hired" is probably too short (an vague) and "Date That The Employee Was Hired" is too long! "DateHired", however, is a far more appropriate and accurate representation of the field characteristics.
  • Don't use acronyms, and use abbreviations with caution. It can be particularly difficult for a user who may not be fully aware of the system to understand the acronyms that may have been used. Fields named "GST_RT" could be extremely misleading, and the user may find these hard to determine the characteristics of the field. You may use abbreviations, although use them sparingly, and only use them where they may supplement or enhance the field name in a positive manner.
  • Do not use names or words that may confuse the meaning of a field name. Ensure that the field name does not contain redundant words that would make the field names meaning unclear or could possibly lead to the field name being misunderstood. Use words or names that would provide the most meaningful description of the field characteristic within the organisation.
  • Ensure that you do not use field names that implicitly identify more than one possible characteristic. It should be possible to identify the problems here by seeing words such as "and" or "or". Field names that also include characters such as (\) or (&) will lead you to problems. If you see a field named "Town or City" or "Phone\Mobile\Fax" then you will need to be looking at creating new fields to split these characteristics.
  • Use the singular form of the name for a field characteristic. A field with plural names, such as "Subjects", "Lessons" etc. implies that the field may contain multiple values for a single record. A field name should be singular as it should represent a single field characteristic of the subject of the table to which it belongs. A table name, on the other hand, can be plural as this represents a collection of similar objects or events. When using correct database naming conventions, you should easily be able to distinguish between table names and field names.