James's Database
~~James La Borde

Naming Conventions

"What is a Naming Convention and why is it important to me as a database designer?"

A naming convention is a uniform method of naming objects within a database. There are many naming conventions out there and we will address some of them. The important thing is not which one you use, or why you choose to use it, it is simply that you use one.

Why is a naming convention important? Simply put, it helps you better organize your database. Let's start very simply. The most basic aspect of a naming convention is in naming your tables, queries and the like. As your database grows, you may end up with a fairly large number of tables. By using the simple convention of adding a prefix to your table's name, you can more easily locate the table you are looking for; it also helps avoid confusion between names of tables and names of queries, since Access considers a table and a query to be the same type of object. In fact, in the original release of Access, all tables and queries actually appeared in the same list. A common convention is to add "tbl" to the beginning of a table name, "qry" to the beginning of a query, "frm" to the beginning of a form, and so on. You can also differentiate between main and sub-objects (such as reports and forms) by adding "s" to the beginning of the original prefix. Thus, "sfrm" would indicate a sub-form, "srpt" would indicate a sub-report. This extra step helps ensure that all of your sub-objects appear in the list together.

Now that the simple part is out of the way, we will attack the true intent of a naming convention. In the strictest sense of the phrase, a naming convention applies primarily to the code within your database. According to Developing Applications with Microsoft Access97, a naming convention "standardizes the structure, coding style, and logic of an application." What does that mean in plain English? It means that if you start developing an application (database) and end up shelving it for a year, you can go back and pretty much know what is going on in the database. You will easily be able to figure out and find your data and object types.

When getting into the code aspect of a database (once you start using VBA) the most prominent naming convention is the Leszynski Naming Convention (LNC). In LNC, all variable and procedure names are made up of three parts: a prefix, a body, and a qualifier. The prefix is always in lower case and denotes object or data type ("tbl" or "frm"). Next, the body is in a mix of upper and lower case to eliminate the use of spaces. It should be as descriptive as possible ("EmployeeNames"). The qualifier simply defines what the procedure is doing or regarding. Qualifiers are most often used in functions or procedures, so they don't necessarily need to be part of your table, form, or query name. For instance, you might call a procedure "intGetRecordLast". "Last" would be your qualifier.

There are many advantages to using naming conventions. They make team development much easier. If all members of a team are using the same conventions, one can pick up another's code and add to or modify it from where the first person left off. By using the same naming convention, all aspects of the work integrate much more easily. Using a naming convention also makes troubleshooting (also known as de-bugging) easier. By consistently naming objects in your database and code, it is easier to determine what each object is supposed to do. Finally, it makes it easier to re-use your code in future applications. If you find that you need to create your own function, by using a naming convention you can readily adapt it to the new application rather than rewriting the function from scratch.

I recently came across an anecdote in Access 97 Expert Solutions. In the anecdote Tod Nielson, a General Manager in Microsoft's Access Business Unit, relates a story of hiring a contractor to help write some code for him. When he went back to look at the code and troubleshoot it, he discovered that the contractor had named all of his variables after cartoon characters. This really had me going as one of the first experiences I had with writing code, I actually named my variables after Peanuts characters. (Hey, I was working at an amusement park who had the Peanuts gang as mascots.) Now, whether you find this amusing or not, it definitely brings to light the importance of being consistent. Who knows what Snoopy refers to as a variable six months after they have written the code?

As I said early on, the importance of using a naming convention is not in which one or why but simply that you find one you are comfortable with and stick with it. Most people I have talked with have created their own from a mix of what they see as the best of others. No matter which one you use, it will make your programming much more efficient, more readable and structured.

I am still looking for ideas for future articles. I want to know what everyone wants to read about. Send your questions to linda@personal-computer-tutor.com please. If I don't get a compelling question to write about, next month's article shall be on Union Queries!

James La Borde works in the computer department at a Credit Union, where he uses Access, SQL Server, VBA, and ODBC daily.  He also teaches online Access classes at Eclectic Academy.