Tony's Table and Field Naming Conventions

(Last updated 2010/09/27)

• All table names are in camel case and can be up to 50 characters long if required in very large databases
• All field names are prefixed with the initials of the table name, e.g. cName, ihInvoiceNbr and dQuantity.,   
• The autonumber primary key has the initials of the table name suffixed with ID. e.g. cID, ihID and idID
• Foreign keys have the table name initials, the foreign table name suffixed with ID, e.g.. cStatusID, ihCustomerID and dInvoiceHeaderID

So I like doing things a bit differently than most people.  Well, tough.  My naming convention works quite well for me. <smile>  And I disagree with Microsoft and many of my fellow MVPs.  I don't mind being a contrarian.  Some of my fellow MVPs and fellow newsgroup denizens will rightly accuse me of enjoying being a contrary person.

Why do I do things this way?  Because it can be somewhat time consuming and irritating to determine just what table a given field comes from.  The query design view screen has no room for longish table or query names where the first ten or fifteen characters are identical or similar.  Forms aren't too bad as they are usually based on only one or two tables. But when you have reports with fields coming from many tables things can get quite ugly.    And if you're in complex VBA code which has two or five  record sets open at once each pulling data from multi table queries things can quite confusing in a hurry.

I should note that I came up with these conventions when working on a database with 160 tables in the back end.

There is next to no tendency to label fields with just common reserved words such as the favourite Date or Name.  One friend encountered a problem with the field name Union.   but SQL things he meant a Union query.  Fellow MVP Allen Browne has a exhaustive list of reserved words at Problem names and reserved words in Access

I also do not like using duplicate field names in different tables.   When these fields are on the same form or report and you are in design view all you can see is a portion of the table name.  Quite useless that. 

Let's take a simple example.  Three tables named Customer, InvoiceHeader and InvoiceDetails.   You don't want spaces or any special characters in the names as these can cause irritations within VBA code particularly.

Customer
 - cID (autonumber primary key)
 - cName
 - cAddress1
 - cAddress2
 - cCityTown
 - cPostalCode (text length of ten.  No assumptions about U.S. Zip codes for this Canadian!)
 - cComment
 - cStatusID  (foreign key to Status table)

InvoiceHeader
 - ihID (autonumber primary key)
 - ihInvoiceNbr (or ihInvoiceNumber)
 - ihCustomerID (foreign key pointing to cID on the Customer table)
 - ihDate
 - ihAmount
 - ihTaxes *
 - ihComment
 - ihShippingInfo
 - ihShippingDate

InvoiceDetails
  - idID (autonumber primary key)
  - idInvoiceHeaderID (foreign key pointing to ihID on the InvoiceHeader table)
  - idItemID (foreign key pointing to iID on the Item table)
  - idQuantity
  - idPrice (yes, you are fetching the price from the Item table but what if the price changes next week)
  - idTaxes **
  - idComment

* Depending on the levels of jurisdiction involved taxes may require two or three fields or even be in a separate set of tables.  The Americans can have all kinds of sales taxing authorities covering any given geographical point, or shipping or receiving location, such as hospital region tax, fire region tax, municipality, education or other. 
** Yes, you are calculating the taxes at the InvoiceHeader level but what if a particular item, or group, of items such as a timed setback thermostat doesn't have a provincial sales tax on it due to provincial environmental policy.

Other examples would be lot for LabourOperationsType table (shortened to LabourOpType) and qcwtt for the QCWeldTestType table.   There are times though when there are duplicate one or two letter table initials so you have to watch for those.

Now sometimes I'm not exactly sure what a table should be called.  So I create all the fields in it without the table initials/prefix, save it under an working name and review its function along with already existing tables in that area.  Once I come up with its final name I then go back in and put the table initials in all the field names using cut and paste.

Now when working in queries, forms, reports or modules you can tell at a glance what table a field comes from.  Makes things much clearer.  Changing things months or years down the roads becomes much easier because you immediately tell where a field is coming from.

Occasionally I come across a situation where the table initials are identical.   Say Customer and Carriers.   I usually add a second word to the second table name.  Other times I'll add a 1 to the table name initials.  For example in this case the autonumber primary key on the Customer table would be cID and on the Carrier table it would be c1ID.

Object naming

See Tony's Object Naming Conventions for an discussion of this topic.

Other naming conventions

I do generally use the following naming conventions for module level variables.  They help somewhat but usually modules are small enough that you can figure out what a field is by its context anyhow.

Q110264 INFO: Microsoft Consulting Services Naming Conventions for VB
Naming Conventions for Microsoft Access - The Leszynski/Reddick Guidelines for Microsoft Access
Office 2000 > Visual Basic Programmer's Guide - Visual Basic Programmer's Guide - Writing Solid Code
Reddick VBA (RVBA) Naming Conventions

Here's an .Net page which states   "Do not use Hungarian notation. Hungarian notation is the practice of including a prefix in identifiers to encode some metadata about the parameter, such as the data type of the identifier. " NET Framework Developer's Guide >> General Naming Conventions

Recommended books on database design

Designing Relational Database Systems by Rebecca Riordan
Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design by Michael J. Hernandez

Do note that while I have used links to Amazon's website I do not have an associate number in the URL which would in turn give me a commission.  Also support your local computer book store. 

[ Access Tips | Access | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available