I’ve had the pleasure (great deal of sarcasm intended) of recently working with a legacy system where the database objects are limited to 8 or less characters. Table names are often numbered. Column names use some abbreviations in an effort to provide the illuminated with some meaning. To work with these databases a person requires a special decoder ring to translate objects in to human understandable entities. That’s how things used to be.
Today, I find it funny that these kinds of designs continue. In SQL Server, objects names have a specific type, SYSNAME, which has an underlying data type of an NVARCHAR(128). This allows for object names in pretty much any language, and provides enough space to make object names meaningful.
That being said, it makes sense to me to use meaningful names for our database objects. If you need to use shorter names when writing your code you can use aliases to have abbreviated names. By using aliases, the core objects continue to maintain a meaningful name that may be self describing.
Personally, I don’t like the prefix technique of naming convention. I don’t name a table with the prefix tbl. There is one exception I use sometimes when writing functions. I may prefix a function with sfn for a scalar function, and tfn for a table value function. That information provides some guidance on what is expected from the function, and how to consume it. But that is a personal preference I don’t think is really transferrable to other people.
With intellisense being made available for most SQL editors, there is less value from having shortened object names, when it comes to quickly writing SQL code. Eve n for the slow typist, auto completion can remove some of the pain from having longer names.
For me, one of the final considerations could be the readability of your SQL code. Using names that are too long can make your code be very wide, and reduce readability. The logic of what is being performed can get lost in the volume of characters on the screen. Once again, aliases come into play here. This is a good reason to use aliases, to make code more readable. The neat part about using an alias is that the fuller definition of what the abbreviated name means I right there inline in the SQL. You don’t have to review some external resource to understand what is intended.
One thing I do try and do when using aliases is to be consistent. If you use the same alias for an object in all of your SQL statements, it becomes easier to make the association as you become more familiar with the code.
If you’re using an ORM, the longer object names can provide value as well. Especially if you are using database first ORM code generation. Now you not only have meaningful database object names, but your ORM code also has meaningful names, more consistent with code objects.
In summary, you have a lot of room to name your database objects. I prefer to use names that are meaningful, as short as possible, but long enough to be meaningful without assistance if possible.
Cheers,
Ben