SQL Naming Conventions prevent bad SQL Smells
Anything written about Information Technology has to mention naming conventions. Phil Factor’s article on SQL Smells describes several smells which can be caused by poor naming of objects in the database. In this article, I am going to concentrate on naming tables and columns.
What makes a good naming convention?
I don’t think I can do better than quote Phil Factor in his original article:
“Identifiers should help to make SQL readable as if it were English.”
By “Identifiers” Phil means the names of the objects. The name of the object is to distinguish it from all the other things in the database. It does not need to fully describe the object. It is good if it tells people what the object contains, but it does not have to describe it in detail. There are better ways to do that.
By the way, SQL was originally meant to be called “Structured English Query Language” but the name was altered because of a trademark clash.
Short, meaningful names are good. Nobody will thank you for meaningless names like “T1231237” or “TEMP000”. I’ve actually seen both of those! Similarly, nobody who has to type it in is going to like really long names.
Any standards, naming conventions, or guidelines should help you to choose names which are useful where you are working. I’m not going to tell you what to do, but I am going to suggest some things to avoid, or at least to watch out for.
Naming things well is an art. Naming linking tables is especially hard. It is worth spending a little (but not too much) time on naming things. The names you choose will be referenced throughout your system and will be with with you for a long time.
Over the years, the maximum size permitted for table names has increased. That has made life easier. Really short table names practically demanded structured, coded names which were a pain. On the other hand, please don’t get carried away with long names.
Naming Conventions by exception
Rather than provide detailed guidance on a naming convention, other than the “Goldilocks – Meaningful, not too short, not too long, just right!”, I’m going to give you a list of naming SQL Smells which should make your nostrils twitch:
- Avoid using reserved words in names: I did it inadvertently once by calling a column “Desc” (for “Description”) which clashed with DESC for “descending” in SQL. It caused all sorts of trouble and confusion.
- Avoid special characters (including spaces) in names: This is allowed if you enclose the name in square brackets ([Table$name]), but this really is looking for trouble and making work. It is far better to keep things simple. In the same spirit, don’t use square brackets where they are not necessary ([Table_Name]). It’s ugly and harder to read.
- Be suspicious of numbers in object names: Why would a table-name need to contain a number? There are occasional cases where a column name might contain a number. By convention “Address_Line_1” etc. is fairly common. But they are rather rare. Numbers in object names should make you suspect the data model.
- Avoid system-generated object names: SQL Server (and other database managers) will provide default names for some objects, like indexes and constraints. The names it uses are ugly and sometimes give little clue what the object is doing.
Summary
When you are designing your own database, follow the local conventions and concentrate on making the names readable and easy to use.
Where next?
Having dealt with the issue of naming, in the next article I’m going to look at a specific SQL Smell – “Packing lists into a column”.