SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database and its Objects Naming Standards


Database and its Objects Naming Standards

Author
Message
Eshika
Eshika
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 314
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62347 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71436 Visits: 40930
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44473 Visits: 14925
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98163 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Eshika
Eshika
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 314
Thank you all for your inputs these are really helpful.

Esha
Mike John
Mike John
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3354 Visits: 5983
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



patrickmcginnis59 10839
patrickmcginnis59 10839
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4318 Visits: 5893
I like underscores in names.

I try to create naming conventions that sort nicely also, but that goal could be counter to other concerns.

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7398 Visits: 4820
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 Smart way to ask a questionThere 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
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40456 Visits: 19461
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.
Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search