What are naming conventions and should I use them?

Answer 1

What are naming conventions and should I use them?

A naming convention is simply a defined set of rules when naming database objects, adding a prefix to object names that identifies the type of object. For example, the name of a table might start with the lower case letters tbl, whereas a query name could start with the letters qry. For field names, the prefix identifies the data type: dtmStart for a date/time field, lngQuantity for a long integer, etc.  The most common naming convention still in use by many Access developers is the Leszynski naming convention. A web search will quickly give you a list of the recommended prefixes.

Why bother?

There are pros and cons. Personally, I believe the advantages far outweigh the disadvantages. However, I would be remiss to not mention that Microsoft database templates do not use them!

Reasons why some choose NOT to use them:

When building forms and reports, the label assigned to controls automatically takes on the field name. If you add a dtmStart field to a report, you then also need to modify the label, removing the dtm prefix. (A caption property added to a field at the table level can also be used for this, but that caption property can cause other issues so is best avoided.)

When running queries, the column name that appears will be a user friendly name  - ready for printing and/or exporting as is without needing to modify column names.

At the field level, if data types are changed AFTER the fact, the field name itself doesn't require changing. (One might argue, however, that solid database planning should prevent this from happening!)

For a very simple database, it simply might not be "worth the bother" of doing it, although one has to admit it's a pretty small bother to begin with!

Reasons why I love them:

At the field level, more thought goes into data types during table design. For example:

1. Imagine building a database that tracks employee time, and one creates a field calls "Hours".  Now imagine users come back and say that the form doesn't let them put in 3.5 hours. Why? A quick glance at lngHours (lng for Long Integer) would warn us that only integers are allowed. What should it be? Either sngHours or dblHours to allow decimals! (Single or Double number type.) 

1. How about a zip code called lngZip? There are over 3,600 zip codes in the United States that start with a zero. Assigning a long integer to a zip code results in those leading zeroes getting dropped! Design flaws like this are easy to catch when naming conventions are used. Zip codes should always be a text field.

Query building/exploration: 

1. Tables and queries appear in the top section of the query design view.  Without naming conventions for tables and queries, one has no way to knowing whether a query is based on tables, queries, or both!  You'll spend time searching in the navigation pane just to figure out what your query is doing!
2.  When adding criteria to a field in the query grid, there is no need to second guess data types. You might think you are looking at a date field when you're not. Or a number field that is actually text. Who cares? The rules for setting criteria are different for dates, numbers and text! Dates need to be surrounded by # signs. Text needs to be in quotes.  Access DOES try to 'fix' criteria when you forget to use the correct symbols, but it doesn't always guess correctly!

Formula building:

1. Whether using the Expression Builder or manually creating formulas, knowing the data type is almost always essential. It's annoying to have to go look at the table design before one can successfully build formulas! Worse yet, it's pretty easy to create a formula that returns either an error message or wrong information if you assume an incorrect data type! 

Visual Basic Coding:

1. The syntax required when referring to fields and assigning variable data types requires knowing field data types.
2. References to tables and queries in VBA code is easier to follow/troubleshoot if one knows the type of object being referenced.

Avoidance of Reserved Key words:

1. There are many words that Microsoft cautions against using as names, including very common words such as Application, Date, Desc, Document, Group, Note etc. (The list is very long – do a search in the Microsoft help screens for reserved words!) Recent versions of Access have become more forgiving than earlier versions were, but fields that have been given those names can still cause a myriad of issues. Most of the time, simply referencing the field name in square brackets resolves potential issues, but using naming conventions avoids the possibility altogether!

Summary

I encourage you to use object prefixes at least for tables, queries, and fields! Doing so tends to result in better designed databases, and ones that are easier to work with! If you're not convinced, that's okay too. I'd still love the chance to work with you here on the Wyzant interface 😊