1. Computing

Discuss in my forum

Database Naming Rules

Developing Clear, Consistent Names for Database Objects

By

Whether you’re using a desktop database like Microsoft Access or you’re using SQL to create objects in a relational database, the time will come when you find yourself trying to come up with a logical name for a database object.

It’s very tempting to simply come up with something off the top of your head and run with it, but that’s not a great practice. Either you or your successors will curse the difficult field names you created down the road. In this article, I offer my advice on creating logical, consistent names for database fields, tables, reports, stored procedures and other objects.
  • Use plain English. Some database administrators seem to wear their ability to decipher object names like EMHADRZP as a badge of honor. Don’t make reading object names an exercise in decryption. This practice is a holdover from the days long ago when object names were limited to eight characters. You can now have much longer names, so if it’s a field that contains a home ZIP code, call it HomeZIPCode!
  • Include an indication of the object type in the name. You’ll thank yourself later for following this rule. It’s very helpful when you’re trying to reverse engineer SQL statements if you’ve included some hints to help you with the process. For example, if you encounter an object called “Employee”, it might leave you wondering whether that’s a table name, a field name or even the name of a stored procedure. If you include an abbreviation of the object type at the beginning of the name, you won’t have this problem. It’s apparent that tblEmployee is a database table while spGetSalary is a stored procedure. You might choose to leave the abbreviation off for field names.
  • Avoid using spaces. Spaces in database object names are just bad practice. Even though some platforms allow spaces, they can cause all sorts of issues when writing SQL statements or migrating data from one platform to another. It’s best to just leave the spaces out. I prefer to simply omit spaces and use names like tblEmployeeData but it’s just as acceptable to use underscores for this purpose (such as tbl_Employee_Data). Whatever practice you choose is fine, just be consistent!
  • Avoid field names that will become outdated. If you have a field that lists an employee’s job assignment, don’t call it JobAssignment2011 unless you plan to keep these fields for every year in the future. Just call it JobAssignment and you won’t be stuck trying to change the name in a year. If you want to keep historic information, there are better ways to do so. You’ll want to read more about database normalization before going down that road.
  • Don’t be redundant. Redundant field names are one of my pet peeves. You don’t need to call something rptEmployeeReport. The prefix indicates that it is a report! Similarly, if a field containing home addresses is in the Employee table, don’t call it EmployeeHomeAddress when a simple HomeAddress would suffice. While you want to use plain descriptive English, conciseness is also a virtue!
  • Avoid using numbers in object names. Except in very limited circumstances, the presence of numbers in the name of a database object indicates that you haven’t done a very good job of normalization. For example, if you have a table for course registrations that contains a student ID number and the fields Course1, Course2, Course3, and Course 4, proper normalization would dictate that you make this a many-to-many relationship by having a single course field and then creating multiple rows for each student (one for each course registration).
  • If you’re unsure, ask someone. If you find yourself wondering whether the name of a database object follows these rules, chances are that it does not! But it’s always a fine idea to get a second opinion. Show your proposed name to another database administrator and see if he or she can figure it out without any “helpful hints” from you. Even better, show the name to your non-techie significant other and see if it makes sense to a layperson. If it doesn’t start over!
So, there you have it, a few simple rules that can help you create readable, consistent names for your database objects. Following these rules might require you to spend a little more time up front, but I guarantee that you’ll appreciate it later. Spread the word to your colleagues and we can make the world a much friendlier place for those of us who have to work with the databases created by others!
  1. About.com
  2. Computing
  3. Databases
  4. Design
  5. Database Naming Conventions: Developing Names for Database Fields, Tables, Reports and More

©2014 About.com. All rights reserved.