Database Naming – One Last Time
I had some good thoughts come through from a few readers today that I felt were helpful and touched on areas we haven’t really covered at this point. So, I’ll drop them in here without comment, and move on with the discussion about code naming conventions in the editorial section.
Ken provides some thoughts about index naming conventions:
FK Index naming convention is fine, but I think using that style of naming for routine indexes (those designed for performance) is a bit excessive. I simply use:
IDX_TableName__Description
This allows you to make small modifications to the index in the future without a) having to rename it or b) have it become out of sync with its name.
The double underscores are there since many table names contain underscores. Examples are:
IDX_Sales_Journal__By_Date_And_GL_Type
IDX_Patients__By_Last_Name_And_State
This keeps names short and descriptive. Also, it should become obvious if you are creating a second index very similar to an existing one, you can then merge the two, and with some judicious query tuning, make sure the original use of the original index is unfettered.
I use the prefix CIDX for clustered indexes, and UIDX for unique indexes created to enforce uniqueness.
And before you address coding, could we PLEASE stop prefacing database objects with the object type
tbl_Patients
usp_Update_Age
If you really don’t know what type of object it is, you probably shouldn’t be in the database. It is just extra garbage that means nothing to the developer. The only exception to this rule that I practice is a preface of a "v" on a view, since it can show up in many places where table does (e.g.; Select statements). If you are polling sys.objects look at the Type column for what type of object you are looking at.
Marc Writes about Check Constraints:
SQL Server produces a clear message – “You violated this [Primary Key|Unique Key|Foreign Key|Unique Index|Not Null constraint]” with the names of tables and keys or columns. These error messages are clear as to why the constraint was violated.
But since check constraints can contain any simple or complex expression, the error message can’t tell you _why_ the constraint was violated. This descriptive naming convention helps address that.
For check constraints, I used to name them “ch<table_name>_<column_name>”, but found that it wasn’t very descriptive. If a .NET developer sees a SQL Server error message as he/she is debugging their code, they will often just ask me, “Hey, what’s this error mean? What does that constraint do?” With over 800 tables, I don’t know off the top of my head, so I have to look up the definition of the check constraint.
So I find it useful, for Check constraints, to be more descriptive in their naming. So the convention would be “ch<table_name>_<description_in_plain_english>”
Here are some examples. As you can see, I don’t even need to give you the code or table definitions for you to know what they’re supposed to check for:
“chCustomer_EndDateMustBeOnOrAfterStartDate”
“chService_StatusEnumMustBeBetweenZeroAndEight”
“chContact_FirstNameCannotBeWhitespaceOrEmptyString”
“chPurchaseOrderHeader_SubTotalMustBeGreaterThanOrEqualToZero”
Code Naming Conventions
When I started this topic I did it gritting my teeth. One of the things I hate is standards for the sake of having standards. One of the things I like is having standards that enhance communication without having to look somewhere to figure out what is going on.
I did some work extending Great Plains accounting software years ago. I remember how fun it was to work with tables named t19271 and figure out what the column c4091 was (that’s not literal…but that was how the product schema was obfuscated). You had to have a data dictionary to understand what was going on, and it helped to have a photographic memory if you wanted to do anything quickly.
Still, we have a tendency to do the same thing to ourselves when we code today. We know what things are…so why take a lot of space with long names because you have to type them over and over in code? My primary reason is that it communicates what the thing is you are designing, what is the purpose, and how it may be utilized.
Tomorrow I am going to delve more into specifics on this topic without being pedantic. I really don’t care if your names start with a lower case letter, if they are camel case, all uppercase, use an underscore character, etc. Those kinds of conventions are nice to have, but I don’t think they are as relevant as the name chosen for Classes, File Names, Projects, Solutions, etc.
Personally, I’m going to address the topic from a Dot Net perspective. Feel free to offer suggestions from other languages as well. I’m sure there are great helpful tips in Java, Java Script, PHP, Python, Ruby, etc. For all you Visual Cobol users, be sure to send in your two cents as well.
To participate in the discussion simply drop me a note at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Troubleshooting problems with backup or restore SQL Server 2008 R2 databases
This article describes the problems you can have when backup or restore SQL Server 2008 R2 databases.
Featured White Paper(s)
All-At-Once Operations
Written by Itzik Ben-Gan with SolidQ
SQL supports a concept called all-at-onc… (read more)