Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Database and its Objects Naming Standards Expand / Collapse
Author
Message
Posted Thursday, March 6, 2014 12:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, May 24, 2014 3:11 PM
Points: 72, Visits: 309
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
Post #1548429
Posted Thursday, March 6, 2014 12:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
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 Moden's 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)
Post #1548435
Posted Thursday, March 6, 2014 12:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1548439
Posted Thursday, March 6, 2014 1:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #1548448
Posted Thursday, March 6, 2014 4:05 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 15,527, Visits: 27,909
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1548524
Posted Monday, March 10, 2014 2:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, May 24, 2014 3:11 PM
Points: 72, Visits: 309
Thank you all for your inputs these are really helpful.

Esha
Post #1549487
Posted Tuesday, March 18, 2014 7:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:04 PM
Points: 2,663, Visits: 5,798
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




Post #1552164
Posted Tuesday, March 18, 2014 1:10 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 374, Visits: 2,286
I like underscores in names.

I try to create naming conventions that sort nicely also, but that goal could be counter to other concerns.
Post #1552369
Posted Wednesday, March 19, 2014 3:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 3,009, Visits: 3,200
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 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
Post #1552496
Posted Wednesday, April 2, 2014 1:42 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 1,302, Visits: 3,738
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.
Post #1557677
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse