Naming Conventions
-
Comments posted here are about the content posted at temp
Brian Knight
Free SQL Server Training Webinars -
A particular area where you can never please everyone - and just to prove my point
...I've never found prefixing table names with "t" or "tbl" to be of any use. A table is the "core" object and I like their names to be as user friendly as possible. Give me "the Employee table" over "the tEmployee table" any day. Prefixes for other objects make sense to me, and it's useful to be able to identify that you're using e.g. a custom function. The only one I feel a bit guilty about favouring is using a v or vw for a view. I like it because if I look at someone's SQL I can immediately see they're not accessing a table directly. But - the data analysts don't really need to know it's a view.
For stored procedures and other objects that are created by other projects we include in the prefix something that identifies the the owning project, e.g. vw_payroll_CurrentEmployees. That's proved very handy in figuring out who was responsible for what. Of course we also require comments, and have change control - but I like to be able to easily locate objects that aren't "native" to the database development.
-
plus, prefixes on object names defeat the UI of various tools (i.e., Enterprise Mgr, isqlw) when looking for specific objects, which is why I tend to abhor prefixes. More specifically, it defeats keyboard "jumpaheads" in list/combo boxes. Not a big deal if the list fits on your screen easily, but a PITA when you have several hundred objects that tend to have very similar names otherwise...
Postfixes imho work slightly better in this case..
-
I use suffixes (isn't Postfix a mail server?
myStoredProcedure_usp is much better than a prefix, as I can actually jump to the sp in the list by typing it's first few letters. I do the same for udf's (_udf) and triggers (_tr) and basically anything else I can get away with!
I don't prefix table names either, but I would consider it for databases with a large number of tables, but would probably use a _tb suffix.
-
I'm not a lover of non specific prefixes, such as t, tr or p. It makes the sorting more difficult. As a contractor I have to fit in with client's standards so I see quite a few differences!!
A good script will always extract the objects you're interested in - I do usually prefix a view, mainly because I don't like views ( generally - partitioned and indexed views are different ) but importantly to identify views hidden deep in multitable joins which can often be a pointer to some needed tuning.
I guess in the end it's what you get used to and what works for you. I've seen some bad techniques such as prefixing every table with table_ and prefixing every column with the table name. Another excellent, not, technique is to number the columns - hands up the expensive apps which use an abreviation and number for each column ^^
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Sorry - just thought of a couple of worst others - inconsistent rmving of vwels from names or rmvngvwls alltgthr + anything looking like txt speak + spelling mistakes in names -- arrghh + inconsistent using of plurals , booking or bookings.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Joe Celko's SQL Programming Style is, in my opinion, the authority on naming conventions and other style practices in any SQL variation. This book provides a justification for every "rule" and a list of exceptions where appropriate. I am working towards making new coding habits based on this book and it's been smooth sailing so far.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
One place where having object-type prefixes can be useful is in written documentation.
“tCustomers is populated by pCustomersAdd” provides more information than “Customers is populated by CustomersAdd”, and lacks the whiff of grammatical error, while being more succinct than “The ‘Customers’ table is populated by the ‘CustomersAdd’ stored procedure”.
I find it useful to used mixed-case to indicate word breaks, but then use underscores to indicate separate clauses or actions within the name. E.g.:
tCustomerAddress
tCustomerAddress_Import
pCustomerAddress_Delete
pCustomerAddress_Import_Control
pCustomerAddress_Import_Validate
For indexes, does having the table name in the index name provide any benefit? Isn’t the index always accessed or referenced via the parent table? Index names can already get uncomfortably long if they include the name of every field involved; to include the (possibly lengthy) table name as well seems like overkill.
In the posts above, lancea mentions including “owning project” names in the object name, after the prefix but before the main part of the name. Those people fortunate to be working with SQL2005 could consider using Schemas for that sort of demarcation: objects that aren’t native to the core project could be assigned to separate schemas. E.g.:
ServerName.DatabaseName.Payroll.vw_CurrentEmployees
This could not only provide a clean naming convention, but would also allow permissions to be set up to control access to the core project data via other projects’ code.
Finally, in a database that includes a large number of reporting processes (such as a data mart or warehouse), I’ve found it useful to clearly separate the objects that are related to reports from those that are related to the more fundamental load or application processes. Complicated reports and extracts can involve multiple stored procedures and working tables; having a convention that separates them from other processes and then groups them by report can be helpful. E.g.
pRptWeeklyFinancials_Control
pRptWeeklyFinancials_Settlements
tRptWeeklyFinancials_CheckTotals
-
Hello all,
You are correct Lance, you can't please everyone! I wrote this article not as a panacea for all situations but rather as a starting point for others to develop their own naming convention and to spark discussion.
For those that hate prefixes, I completely understand. Two notes on this subject:
- The "t" prefix helps searchability in other applications as well as in SQL Server, as noted in the "Behind the Scenes" section. I use it as a way of tracking code that uses the database, not just the database itself.
- The jump-ahead in all combo boxes still works, you just start with one extra character ("t" or "p", etc.)
Regards,
Michael Lato -
Joe, I did some research back March/Apil 2006 and corresponded with two of the individual members of that ISO board. They informed me that their ISO was intended for Metadata only and not for database objects. However, they stated I could borrow from ISO-11179 when I discussed maybe drafting an ISO standard for database object naming conventions. I started a draft but currently am over extended at work and home with several projects to get any further than concept.
Please refrain from stating 11179 is the standard. You may, however, suggest it as an alternative until an official ISO is drafted to address database object naming conventions.
-
Michael - Excellent article and very thought provoking. We do not use the same standard you have placed in our hands but the reasoning for having a standard from another point of view is valuable. Good work on this.
To comment further your points about using the same standard in a database or in an institution is critical, and for many of the same reasons you give. We have found over the decades in writing code that the maintainer has not only the need to get into the code but at times they have to get into the head of the developer.
Determination of developer style if it is complex is not easy. Then it can and often has been complicated by a unique style of database design and implementation. Instead of adding the complication of unique name schemes it is far better to use a standard and give the maintainer of the code a hint as to what is going on, and what the data is and what it is being used for.
Lastly, the conversation now going on is interesting for one fact. No one is discussing or saying that a standard is not needed. That is accepted across the conversation. What is being said is I am more comfortable with 'this way' or 'that way'. This adds to the creditability of the article. Good work! Keep it up.
BTW ours is different from all stated, it has been in place for about 14 years and it has been a solid backbone of the work. Everyone knows of it, it is well publicized, and every data model is reviewed to the standard, the business practices, and to the integration model. We have proven over time what you propose will work. As the beer commercial says "Use a data name standard. Data Law? Data Law!"
Thanks...
Not all gray hairs are Dinosaurs!
-
Why should a column in a table be prefixed with the table name? E.g. column CustomerName in table Customer when e.g. Name suffices. To me this is plain silly.
-
Hello Jane,
The "Name" field is a special case. I don't like to use reserved words for column names, so the prefix prevents this.
Regards,
Michael Lato -
I thought that procedures starting with "sp_" were searched in the master database first, not merely "sp" Is that wrong?
-
You are correct... it's "sp_" that goes to master first
.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply