Database and its Objects Naming Standards

  • Grant Fritchey

    SSC Guru

    Points: 396716

    Cars is an extra letter and I'm a completely lazy SOB. I'd rather just have Car, as long as it's clear, right. I mean, not Cr or something else stupid.

    ----------------------------------------------------
    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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Cody Konior

    SSCarpal Tunnel

    Points: 4827

    Grant Fritchey (4/13/2014)


    I mean, not Cr or something else stupid.

    And thus the challenge has been issued for the most stupid name we can come up with. tblFWB is my guess, as it's a FourWheeledBuggy of course!

  • Sean Lange

    SSC Guru

    Points: 286536

    Jeff Moden (4/12/2014)


    I don't believe I've ever seen a Companies table. 😀 It's always been just Company where ever I worked.

    I have and it has left a permanent scar on my brain. I am a naturally good speller and I was a consultant at a place for almost 3 years that had a "Companys" table. To this day I have to stop and think about how to spell it. This is a real world example of what you describe about the complexities of plurals in English. The table was designed by a non-native English speaker. :w00t:

    _______________________________________________________________

    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/

  • Cody Konior

    SSCarpal Tunnel

    Points: 4827

    Sean Lange (4/14/2014)


    I am a naturally good speller and I was a consultant at a place for almost 3 years that had a "Companys" table.

    Yeah I regularly see columns named [SomethingDescrption] in real life and posted here in queries too, where someone didn't spell it right the first time and then it hangs around like that forever. I also saw a column named InvoiceDetails.[InvoiceDetailslId]. See that l? Me neither, for a long time.

    Worst of all, were dozens of stored procedures that used dbmail and the word "udpate". They must have been copied pasted or done by a dyslexic but my email inbox was filled with "udpate successful" and "udpate failed" emails for a year before I was confident enough to go through and fix them all.

  • humbleDBA

    Hall of Fame

    Points: 3460

    My opinion...

    I agree with Mike John, patrickmcginnis59 10839, andrew gothard, Jeff Moden.

    Just my opinion 😀

  • Scott Arendt

    SSCertifiable

    Points: 7887

    I will agree that it is a personal preference and completely meaningless unless enforced.

    Also, my personal preference is No_Underscores!!

  • Eirikur Eiriksson

    SSC Guru

    Points: 182514

    Scott Arendt (4/25/2014)


    I will agree that it is a personal preference and completely meaningless unless enforced.

    Also, my personal preference is No_Underscores!!

    Do not want to underscore any preferences but I find object_subject_action conversation very useful

    😎

  • andrew gothard

    SSChampion

    Points: 12297

    Oh, and one other thing.

    Anything justified purely on the basis of "It Saves Typing" is grounds for the removal of their keyboard, possibly bodyparts depending on how bad the witlessness being justified in this manner is - and a phone call to HR. Possibly an undertaker.

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • OCTom

    SSChampion

    Points: 11755

    The very worst naming convention I saw was years ago on an AS400 (1981).

    Tables are called files on that machine and reside in a library.

    The naming was such:

    Library001

    File001

    Field001

    Field002

    Field003

    File002

    Field001

    Field002

    ...

    Library002

    File001

    ... etc ...

    Their program names were Pgm001, Pgm002, etc.

    It was awful to work with. It was a major ERP system. One of the biggest at the time.

    So, whatever you choose for naming convention, make sure they're clear, consistent, and enforced.

    Tom

  • andrew gothard

    SSChampion

    Points: 12297

    OCTom (4/25/2014)


    The very worst naming convention I saw was years ago on an AS400 (1981).

    Tables are called files on that machine and reside in a library.

    The naming was such:

    Library001

    File001

    Field001

    Field002

    Field003

    File002

    Field001

    Field002

    ...

    Library002

    File001

    ... etc ...

    Their program names were Pgm001, Pgm002, etc.

    It was awful to work with. It was a major ERP system. One of the biggest at the time.

    So, whatever you choose for naming convention, make sure they're clear, consistent, and enforced.

    Tom

    Saves Typing

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • Jeremy Brown

    SSCarpal Tunnel

    Points: 4223

    I find more often than not, people go overboard on naming standards (in my honest and humble opinion).

    I know I'm probably going to catch a lot of flames for this, but, naming standards that include things like the object type seem senseless to me. Each object in SQL Server has relevant metadata that accurately describes what the object type is (among other things).

    I'm more a fan of the simple rule that code should be self-describing. In other words, entity names should accurately represent what entity is being maintained. Executable code should describe the function or behavior performed (more or less).

    The "naming standard" should establish the common language usage for these things. For example, functional code that "gets" things from the database should begin with "get". In other words, its an agreement between the developers that we use these common words to describe the action performed. Likewise anything that commits something should be "put" or "write". Choose whatever verbs you like, but make is sensible.

    In other words, make it lightweight. Easy enough that can be generate -able in code, but not so difficult that it takes a maintenance programmer a degree in quantum physics to be able to understand.

  • Sean Lange

    SSC Guru

    Points: 286536

    Jeremy Brown (4/30/2014)


    I find more often than not, people go overboard on naming standards (in my honest and humble opinion).

    I know I'm probably going to catch a lot of flames for this, but, naming standards that include things like the object type seem senseless to me. Each object in SQL Server has relevant metadata that accurately describes what the object type is (among other things).

    I'm more a fan of the simple rule that code should be self-describing. In other words, entity names should accurately represent what entity is being maintained. Executable code should describe the function or behavior performed (more or less).

    The "naming standard" should establish the common language usage for these things. For example, functional code that "gets" things from the database should begin with "get". In other words, its an agreement between the developers that we use these common words to describe the action performed. Likewise anything that commits something should be "put" or "write". Choose whatever verbs you like, but make is sensible.

    In other words, make it lightweight. Easy enough that can be generate -able in code, but not so difficult that it takes a maintenance programmer a degree in quantum physics to be able to understand.

    I agree that the naming standards can go insanely overboard. However your comment about starting the name with the verb is one I disagree with immensely. When you have a system with even a few hundred stored procs it is a PITA when they are all grouped by what they do instead of by what they deal with. It is difficult to find a given stored proc for "get" when there are 200+ that all begin with "get". This is why starting with the object name is helpful. The list is now sorted by object instead of function making it much easier to find the 10-12 procs all dealing with Orders. 😉

    _______________________________________________________________

    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/

  • Jeremy Brown

    SSCarpal Tunnel

    Points: 4223

    I agree that the naming standards can go insanely overboard. However your comment about starting the name with the verb is one I disagree with immensely. When you have a system with even a few hundred stored procs it is a PITA when they are all grouped by what they do instead of by what they deal with. It is difficult to find a given stored proc for "get" when there are 200+ that all begin with "get". This is why starting with the object name is helpful. The list is now sorted by object instead of function making it much easier to find the 10-12 procs all dealing with Orders. 😉

    See? It didn't take very long to get a response :).

    I understand what you're saying. Honestly I don't feel as strongly one way or another. Fine, you want the object name first, go for it. You want the behavior first, fine by me. Its all a matter of perspective.

    If you're using SSDT (and you should be) to develop your database, you can quickly view object dependencies for any entity. When you can do that, it starts to be "not such a big deal" frankly, what the name of the object really is. You are able to make changes to that object and / or make changes to any dependent objects pretty quickly - and validation checks make sure it doesn't break any other dependent code.

    The only gotcha is in "generated" code - dynamic SQL for example. Dynamic SQL tends to be table driven in many cases, so for those cases you have to maintain custom metadata - and in that case I can go along with your method.

    In other words, it may have been a huge deal if most of your SQL development were being done in the legacy method of using management studio connected to a development server. Yeah I can see your point there - it is a pain in the butt.

  • Sean Lange

    SSC Guru

    Points: 286536

    Jeremy Brown (4/30/2014)


    Honestly I don't feel as strongly one way or another. Fine, you want the object name first, go for it. You want the behavior first, fine by me. Its all a matter of perspective.

    That is the basic sentiment of this entire thread. Everybody, and every shop, has their own opinion. We all agree that no matter what you choose the MOST important thing is to be consistent. Most of us can, have and will work with established conventions as long as they are consistent.

    _______________________________________________________________

    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/

  • Eirikur Eiriksson

    SSC Guru

    Points: 182514

    Sean Lange (4/30/2014)


    Jeremy Brown (4/30/2014)


    Honestly I don't feel as strongly one way or another. Fine, you want the object name first, go for it. You want the behavior first, fine by me. Its all a matter of perspective.

    That is the basic sentiment of this entire thread. Everybody, and every shop, has their own opinion. We all agree that no matter what you choose the MOST important thing is to be consistent. Most of us can, have and will work with established conventions as long as they are consistent.

    IMHO and as Sean said, it is about serving the purpose and being consistent. What ever standard is used, stick to it. Do not change half way through and plan for the target size and complexity of the system. Often names such as get_me_this,_that or _the_other are fine but sometimes they are not. And of course, this should be applied to [datacentre].[cluster].[server instance].[database].[schema].[whatever...]

    😎

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply