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!
