I’m working on an Access database which has been designed by another Access programmer over several years. My goal is to convert the database to have an SQL back end (data file). As I am reviewing the database today, I’m finding a number of issues with table and field names. So, I’m taking a 15-minute break to blog about the issue and how to address it.
Access is very forgiving when it comes to table names (Customers, Order #s, Order Items) and field names (First Name, Last Name, Phone #). But most other databases, like SQL and SQL Azure, are not so forgiving. In the case of this database, I need to convert it to SQL Azure. So, now I’m painstakingly going through the database and renaming tables and fields with standard names. Even if you aren’t planning to upgrade to SQL in the future, I highly recommend using certain database standards in names. Even in Access, having odd characters can cause issues and you have to be more careful when referencing these.
In the case of this database, the previous database developer had table names like “Company History”, “List-A&Bdealer” and “List-location”. And, they used filenames like “TT_Acct#”, “Lot/Area”, “A&BDealer”,etc…,. As a rule, users should stay away from characters like # (pound sign), % (percent), / (forward slash), \ (backslash), ? (question marks), etc…,.
In practice, I prefer to only use Alpha characters (A-Z, a-z) and numbers (0-9). Even though characters like – (dash), “ “ (space) and _ (underline) are generally acceptable, I prefer to use what is called CamelCase, which basically means that you eliminate all spaces and capitalize each of the words, so Company name would become CompanyName. This way, all my tables and fields maintain the same look and feel and I don’t have to worry about searching for “Company-History” or “Company_History” instead of “CompanyHistory”. It makes life easier down the road. For the tables I listed above, I renamed them to “CompanyHistory”, “ListAABDealer” and “ListLocation”. For the fields, I renamed those to “TTAcctNo”, “LotArea”, “ABDealer”. Remember, you can still use the Caption to display the field with spaces on the form itself, so your users generally don’t care what the actual field name is.
One other tip I recommend is when naming Yes/No fields, put a “YN” at the end of the field. If I were just to name the field “Employee”, the user would not know whether it is a text field or a Yes/No field. By using YN, you would name “Employee” or “Employee?” as “EmployeeYN” and easily be able to spot what fields should be checkboxes. Other programmers go so far as to use prefixes like “dt” in “dtStartDate”, where dt means Date, but I typically have the word Date at the end of the field and that would be redundant. I’ve also seen developers use “int” in a field like “intBirthYear”, where int means integer, a field that only holds numbers. I will use that occasionally if I have dozens of fields in a table.
My last rule of thumb is never to use reserved words as field names. In the case of this database, I found field names called “Date”, “Year”, “Total” and “Active”. Again, Access is very forgiving, allowing you to use these as table and field names, but why take the chance?
If you inherit a database that you need to upsize to SQL or just want to make it cleaner, there is a little utility, Rick Fisher’s “Find and Replace” which would help to rename field and object names throughout a database. It can be found at http://www.rickworld.com/download.html. Even when using a tool like this, there is usually some clean-up involved. But at least it catches the majority of issues.
Now, back to the cleanup.
Andy Tabisz is a Microsoft MVP and owner of WorkSmart Database Masters, developers of custom Access, SQL and mobile database software. He can be reached via email at [email protected].