March 6, 2014 at 12:20 pm
I am trying to establish the standards for naming convention in my new project. Can you please provide me the best standards which worked for you.
Thanks
Esha
March 6, 2014 at 12:34 pm
Eshika (3/6/2014)
I am trying to establish the standards for naming convention in my new project. Can you please provide me the best standards which worked for you.Thanks
Esha
This is more personal preference than anything to be honest. I personally detest prefixes so I don't use them at all. Things like usp_ to indicate that the object is a user stored procedure just create noise and make it hard to find things.
Generally speaking I name tables as a collection of whatever object it holds. That means plural (Accounts, Users, etc).
Procedures usually have some sort of verb in them to indicate some level of functionality. I like to use the object first followed by the verb so they will sort nicely. Accounts_Insert, Accounts_Update for example.
There are many ideas and opinions on this as there are people on the planet. There is not right or wrong way. Well mostly that is true. The right way is to be consistent, the wrong way is to be inconsistent. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 6, 2014 at 12:56 pm
well, i'll throw my two cents in, and you can evaluate them for yourself
i can only tell you my experiences and the way we do things at my shop; you can review them and see what you like, since there's no real rules to follow:
for prefixes, our database supports multiple applications that share a common database, so tables related to a specific application have a matching prefix; cmsBusinesses, cmsContacts, etc for a contact management system for example. in your example, we might have a table named appTransactions and appTranDetails for example, instead of a prefix to imply a master-detail relationship;
1. no underscores allowed in table names; CamelCase is preferred, and the table name must imply the content/business purpose of the table.
2. every table has an identity as the first column. the column name is always the table name + 'ID' or 'TBLKEY' or something so it's obvious it's the key. cityid for tbCity, stateid for tbState, etc. we leave identities starting at the default of 1,1 unless a business reason needs it otherwise.
3. foreign keys in child tables have the column name the same as the key it is pointing to; so an cmsAddress table has a column named cityID pointing to tbCity(cityID); in the cases where we need more than one, the column name must still be a part of the column name, adn just add a prefix: percityId and buscityID for collections of addresses for personal vs business; helps later so you can confirm that every column ending in "ID" is either a PK or FK, for example.
4. lookup/shared reference tables start with tb; tbCity,tbState,tbCounty; "TB" tables are shared lookups. if a table of say statuses is needed (Open,Closed,Pending) whatever, that table would be for a specific application, so cmsStatus and appStatus might start out with the same values, but from experience, we found that eventually one app needs to change a shared table.
appCity for example, might be a subset of all cities, as not city is really needed.
5. try to avoid abbreviations if possible; in your example would a new developer intuitively understand that tm_,tr_ and td_ relationship?
or would something like AppMaster,AppMasterTransactions, and AppMasterDetails be more descriptive and intuitive?
same thing in column names...silly things like lglstatus vs LegalStatus and other abbreviations; made sense to the person who coded it at the time, but if you step back or someone else looks at it, would they know?
6. no Hungarian notation for data types, only their purpose; a perfect example is "amount" columns; we might name a column InvoiceAmt, and it's decimal or money for the data type, but some business processes capture whole dollars;sAddress, iInvoiceAmt and dInvoiceAmt might imply the data type, but that might need to change in the future; there is lots of info here about Hungarian Notation and it's pitfalls:
http://www.sqlservercentral.com/search/?q=hungarian+notation
now that i said what we strive for, let me also say this: there are exceptions to every rule above; some tables don't get an identity, sometimes a table gets an underscore to match a different business process, etc. but they change because there was a reason, and not for an arbitrary reason.
I hope others chime in and offer some more examples and guidelines they use as well.
Lowell
Lowell
March 6, 2014 at 1:08 pm
I love these discussions. Instead of typing it all in this thread, here's a blog post I wrote about my preferences, http://wiseman-wiseguy.blogspot.com/2008/11/naming-conventions.html.
I have found that I'm in the minority in liking underscores as a break between names instead of camelCase or PascalCase. My reasoning for underscores instead of casing is to be able to handle case-sensitive collations, you don't have to worry about right-casing objects because they are all lower_case with underscores. I've learned to work with and deal with camelCase and PascalCase without getting annoyed now.
My only other contention with the way Lowell's company has done their standards is that the prefix for a specific application and lookup tables should be a schema in 2005+ and not a prefix.
I totally agree with cityID being cityID in the cities table AND in any tables referencing it.
My final comment is that, it doesn't really matter what the standard is, but you need to have a standard and enforce it. You use tools like SSDT, PBM, and maybe others to help enforce the standards as well. Document the standard and make it part of the standard orientation for any new people brought in. There will be people who don't agree with all of it, but they need to honor all of it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 6, 2014 at 4:05 pm
I really don't care what naming standards we have. Let's just make them clear and let's enforce them. After that, it's all good.
I have a presentation where I spend about 20 minutes on this topic. Here's my naming slide:
Names should be descriptive
Procedures should be a phrase
Abbreviations should be common (no Ddltbl)
Use aliases
Clear
Common
Be consistent
A foolish consistency is the hobgoblin of little minds
Keyword in that sentence is “foolish”
Please, anyone who has seen the session, don't give away what ddltbl is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2014 at 2:29 pm
Thank you all for your inputs these are really helpful.
Esha
March 18, 2014 at 7:16 am
I'll chip in in support of underscores to break words up and all names lowercase. Reasons are simply I have seen a db that was PascalCase moved to a system that changed all names to lowercase so names that were just about readable when in mixed case became totally unintelligible.
I therefore got used to and still prefer "first_name" rather than "FirstName" - either is OK - but I still hate mixing the two so "First_Name" would be a NoNo (should that be no_no 🙂
table names meaningful (definitely no "tbl" prefix, and preferably plural names (eg accounts, members, people etc)
Only use identity columns if you actually need a surrogate key - if there is an appropriate real-world key use it. - Eg ISO country codes are 2 characters so why invent a surrogate? Emphasis here is on the word "appropriate"
But above all be consistent!
Mike John
March 18, 2014 at 1:10 pm
I like underscores in names.
I try to create naming conventions that sort nicely also, but that goal could be counter to other concerns.
March 19, 2014 at 3:07 am
Most of it comes down to personal / team preference - you should be trying to make life easier for yourself by knowing what something will be called rather than trying to thinking of a name.
However, you may not be there for the whole life of the database so you need to be clear in your naming - document it!
Don't be too verbose or over abbreviate as per Grant's comments
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
April 2, 2014 at 1:42 pm
MY_TWO_CENTS
Naming conventions are primarily to enforce consistency, readability and prevent ambiguity. A good solid naming convention is worth hundreds of pages of documentation on a large system.
😎
April 3, 2014 at 2:43 am
Wot no Celko? 😉
April 3, 2014 at 5:07 am
I don't get pluralisation, certainly US based folk seem to be all for it, Brits less so. Perhaps it's due to a difference in the teaching of sets?
Here, I was taught
Set:Red a set with the attribult of being red
Set:Car a set with the attribute of being a car
the intersection in a venn diagram would be a set which are both Red and a Car.
Pluralisation also, IMO, leads to inconsistency in naming.
Take the table holding sales orders;
Name SalesOrder - and you call the table holding the associated lines SalesOrderLine - makes sense to me;
SalesOrders - so then you have SalesOrdersLines (or more correctly [SalesOrders'Lines])? That just looks wrong to me.
Or is it SalesOrderLines - which means you're naming the SalesOrder(s) element inconsistently
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 3, 2014 at 6:28 am
Andrew,
Here's my explanation for pluralization. Because the table doesn't contain a car it contains cars. The row is a car the table is a set of cars. In your Sales Order example my naming would be:
SalesOrders - the table contains multiple sales orders not a single sales order
SalesOrderLines - Non-plural Sales Order because the each of the lines belongs to a single sales order, but the table contains many sales order lines
Naming conventions are always going to be debated, but to paraphrase Grant the important thing isn't what the standard itself is, but that you have and enforce one. In reality I'd name the tables sales_orders and sales_order_lines.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 3, 2014 at 6:46 am
Totally agree with Jack.
Mike John
April 12, 2014 at 11:39 pm
Mike John (4/3/2014)
Totally agree with Jack.Mike John
I don't. I don't believe I've ever seen a Companies table. 😀 It's always been just Company where ever I worked.
I tend to name tables after what a single row contains because plurals are a PITA for me. There are "s" plurals, "ies" plurals, "es" plurals, plurals that are the same spelling as the non-plural word, and plurals that are a totally different spelling than the original word.
At the column level, plurals are also a pain. For example, SalesOrdersID just doesn't sound right and I also maintain the standard that, if used, the IDENTITY column in a table must follow the standard naming convention of tablenameID.
I also agree that naming conventions are hotly debated so please understand that my comments above are just an opinion.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply