Wrap Up Database Naming Conventions
I thought I would close the thoughts on Database naming conventions with a few things I find helpful, and then include thoughts submitted by others.
These are a few things I like to use in my naming conventions…these are ideas that I don’t simply follow blindly.
- I don’t mind long names for tables, views, columns, functions, etc. SQL Server supports names (using the SYSNAME data type) up to 128 Characters. So, why not take advantage of the length to provide meaningful names.
- When writing TSQL code I will often use an alias for shorthand on long table names. I like to be consistent with an Alias used for a table.
- I like to use a suffix of ID for system assigned values or unique identifiers.
- For stored procedures and functions I like to include the subject area as a prefix to the name, if I am not using schemas to organize them. That way procedures and functions are grouped by the subject area they implement.
Here are a couple thoughts from our readers…
Baeck:
Many times I have seen a db design where everything was named beautifully and still didn’t perform. Before I even go into that area, I have a list of items that I check before I bother to look at naming standards, yet there are many “GateKeeper” DBAs that look at naming standards mostly.
Specifically, I can’t take the “Get” or “Save” prefixes anymore. GetPatienFirstName is lumped together with all the other get… and one scans a db up and down to find what might be useful when you come in as a new guy to a team. This applies especially to a consultant like me who is called when the mess is established.
How about PatientFirstNameGet similar to the well established OO idea of Object.Message?
William:
Regardless of the size of your operation, naming conventions matter in the same way that rough drafts and outlines mattered back in high school: they force you to organize your thinking and help you stay organized as you work.
Also, one aspect of naming which seems to get minimal thought in database design is the use of namespaces. How many databases do you work with where every table is “dbo.some_name”? Compare that to the class-naming structure you’ll find in the source code of any modern application. How much easier is it to keep track of (or just find) relevant objects when they’re divided into functional namespaces such as “customer_info”, “user_info”, “inventory”, and “shipment”? I think one key is getting folks to think past the documentation’s constant reference to object “owners” and “creators” – after all, how many of those tables “owned” by dbo weren’t really created by somebody besides The DBO?
Tomorrow we’ll close the naming convention topic with some thoughts about how the concept can be applied to other kinds of code. If you have ideas you’d like to contribute then wrap it up and send it to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
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)
Featured Script
dba3_Using_Identity()_With_TempTables_And_Tables_Demo
The Identity() function may be used with Select [] InTo for tables, temp tables and global temp tables… (read more)