locked
What is the proper convention for informative column names? RRS feed

  • Question

  • I have few columns with full names:

    Legislative Assembly Name

    Village Assembly Name

    I want to use proper convention to keep the standards and also to make column name informative.

    How would you name the columns for the above column full names?

    • Moved by Tom PhillipsEditor Thursday, February 16, 2012 1:44 PM Database Design question (From:SQL Server Database Engine)
    Thursday, February 16, 2012 6:01 AM

Answers

  • Propercasing is actually faster to type than underscore separations. And as indicated, propercasing does have a couple of negatives -not as easy to read, and prone to abject confusion when all upper case is introduced. For folks that have to transition between multiple platforms, under scores make for easier transitions.

    In my observations, when databases are designed by deveopment teams, object names tend to be propercased -that is, upper case each word (ThisIsAProperCaseName). Dev teams tend to work in a case sensitive environment (C# at least) and they are often concerned about typing speed. Intellisense reduces the reading and intrepretation burden.

    Long term data professionals 'tend' to use underscores to separate words -and that's a throwback to all cap platforms.

    The arguement for plural tables is similarly developer driven. Many ERD design tools expect a table to contain entities, therefore the [Orders] table will contain multiple entities of type 'Order'.

    Since most data object development is actually done by dev teams these days, I tend to help create naming conventions that work best for those doing the task.

    As indicated previously, explore what works 'best' in your environment/organization, and create standards that will actually be used and supported.

    There's a story about when Eisenhower was the President of Columbia University. The head of the groundskeepers was in his office complaining about how students were not using the sidewalks, but instead cutting across the large grassy quadrangles to get to their classes. This created ruts, muddy spots, and was a bit 'unsightly'. The groundskeepers wanted an edict to compel 'proper' behavior. Eisenhower stood and looked out his window for a few minutes, watching students cross the grass, and then remarked that the sidewalks around the large grassy rectangles were just not serving the need of users. New sidewalks were installed that supported diagonal paths. Problem solved.

    Standards should support users doing their jobs. Standards should not attempt to compel behaviors that are antithical to normal practice. Attempts to do so are doomed to failure.

    And that's my 35,138 old Turkish Lira.


    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson



    Saturday, February 18, 2012 4:47 PM
    Answerer
  • Names should be informative and consistant. Some people feel they should follow a naming convention.

    I believe names depend on context. What are the DB, schema, and TABLE names? What is the purpose of the DB itself. These things all provide the context within which the COLUMN is named.


    Thursday, February 16, 2012 2:08 PM
    Answerer
  • I completely disagree. I dislike Celko's naming convention. Pluralizing TABLE names is just plain wrong, and based on a silly implementation of an academic argument.

    I understand your position on this, Brian; my position was similar for many years.  One thing that I have seen Joe post that I don't see any real argument against is the "eye movement" study in which he found that based on eye movement measurements that names such as "this_Is_A_Name" was easier to read because it consumed less eye movement than "thisIsAName".  Moreover; if you ever get forced into working with a mainframe DB2 database you will find that in that scenario there is no such naming convention to use "thisIsAName" because there is no usage of lower case letters.

    Joe's posts are frequently abrasive, sometimes offensive and often not personable.  Joe's posts are often in such an unfriendly style that I make an effort to avoid posting in a similar style.

    But that doesn't talk about content points.  It is my opinion that most advanced database administrators prefer to use names in which only case defines boundaries between features.  I have recently moved away from that position and am now re-adapting the use of underscores as what defines boundaries between features.

    My reason for the change:

    It is my opinion that the eye movement studies clearly decide which of the two methods is easier to read -- based on the eye movement studies the underscore defined names clearly require less eye movement and are therefore easier to read.  To me this moves this particular decision from an opinion based decision to a measurement based decision.  I feel like the reason to choose underscore defined names stands on a ground that is more firm and objective.

    EDIT:

    Toward the original post:

    I think that if you will search the web you will find more recommendations toward names in which case defines boundaries between features of a name.  During the late 80's and until about '95 I used underscores to define boundaries between name features.  Around '95 I switched to the use of case to define feature boundaries except when using DB2 databases that had to use underscores for feature boundaries.  Sometime over the past two years I have switched back to the use of underscores for feature boundaries.

    I suggest you read all of this and also take a look at the ISO standards for names.  The most important thing about naming standards in my opinion is to try to be consistent.  Weigh all of this out, choose and adopt a system.  Also keep in mind that it is important for you and others to understand the reasons for adopting a system.  This is so that when someone later comes along and wants to do something else that you can say why you are doing it the way that you are.

    Friday, February 17, 2012 2:12 PM

All replies

  • Names should be informative and consistant. Some people feel they should follow a naming convention.

    I believe names depend on context. What are the DB, schema, and TABLE names? What is the purpose of the DB itself. These things all provide the context within which the COLUMN is named.


    Thursday, February 16, 2012 2:08 PM
    Answerer
  • Read Joe Celko's book "Joe Celko’s SQL Programming Style", chapter 1 in particular and further chapters explain in great detail proper naming conventions for columns, tables and more. It's a MUST read for any SQL developer, programmer, administrator, designer or architect.

    John

    http://knowledgy.org

    Thursday, February 16, 2012 11:17 PM
  • Please refer

    http://msdn.microsoft.com/en-us/library/ms175874.aspx
    http://msdn.microsoft.com/en-us/library/ms229043.aspx


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, February 17, 2012 3:31 AM
  • I completely disagree. I dislike Celko's naming convention. Pluralizing TABLE names is just plain wrong, and based on a silly implementation of an academic argument.

    Friday, February 17, 2012 1:20 PM
    Answerer
  • I completely disagree. I dislike Celko's naming convention. Pluralizing TABLE names is just plain wrong, and based on a silly implementation of an academic argument.

    I understand your position on this, Brian; my position was similar for many years.  One thing that I have seen Joe post that I don't see any real argument against is the "eye movement" study in which he found that based on eye movement measurements that names such as "this_Is_A_Name" was easier to read because it consumed less eye movement than "thisIsAName".  Moreover; if you ever get forced into working with a mainframe DB2 database you will find that in that scenario there is no such naming convention to use "thisIsAName" because there is no usage of lower case letters.

    Joe's posts are frequently abrasive, sometimes offensive and often not personable.  Joe's posts are often in such an unfriendly style that I make an effort to avoid posting in a similar style.

    But that doesn't talk about content points.  It is my opinion that most advanced database administrators prefer to use names in which only case defines boundaries between features.  I have recently moved away from that position and am now re-adapting the use of underscores as what defines boundaries between features.

    My reason for the change:

    It is my opinion that the eye movement studies clearly decide which of the two methods is easier to read -- based on the eye movement studies the underscore defined names clearly require less eye movement and are therefore easier to read.  To me this moves this particular decision from an opinion based decision to a measurement based decision.  I feel like the reason to choose underscore defined names stands on a ground that is more firm and objective.

    EDIT:

    Toward the original post:

    I think that if you will search the web you will find more recommendations toward names in which case defines boundaries between features of a name.  During the late 80's and until about '95 I used underscores to define boundaries between name features.  Around '95 I switched to the use of case to define feature boundaries except when using DB2 databases that had to use underscores for feature boundaries.  Sometime over the past two years I have switched back to the use of underscores for feature boundaries.

    I suggest you read all of this and also take a look at the ISO standards for names.  The most important thing about naming standards in my opinion is to try to be consistent.  Weigh all of this out, choose and adopt a system.  Also keep in mind that it is important for you and others to understand the reasons for adopting a system.  This is so that when someone later comes along and wants to do something else that you can say why you are doing it the way that you are.

    Friday, February 17, 2012 2:12 PM
  • Thanx for the in detail reply Kent. I also disagree heavily with Celko's personal style and avoid his replies.

    Regardless, just on naming itself, i think his theories do not reflect the way most people see or use DBs.


    Friday, February 17, 2012 2:44 PM
    Answerer
  • Thanx for the in detail reply Kent. I also disagree heavily with Celko's personal style and avoid him replies.

    Regardless, just on naming itself, i think his theories do not reflect the way most people see or use DBs.

    I don't think I can argue with any of that; I think you are right.

    EDIT:

    I was thinking about this some more.  I was also using plural naming in the early 90's but switched to singular names about the same time I went to using case for bounding name features.  It does seem odd and regressive after about 20 years to say, "Yeah, I'm going back to the way I named objects in the 80's." 

    This does feel suspicious.

    Friday, February 17, 2012 4:16 PM
  • Propercasing is actually faster to type than underscore separations. And as indicated, propercasing does have a couple of negatives -not as easy to read, and prone to abject confusion when all upper case is introduced. For folks that have to transition between multiple platforms, under scores make for easier transitions.

    In my observations, when databases are designed by deveopment teams, object names tend to be propercased -that is, upper case each word (ThisIsAProperCaseName). Dev teams tend to work in a case sensitive environment (C# at least) and they are often concerned about typing speed. Intellisense reduces the reading and intrepretation burden.

    Long term data professionals 'tend' to use underscores to separate words -and that's a throwback to all cap platforms.

    The arguement for plural tables is similarly developer driven. Many ERD design tools expect a table to contain entities, therefore the [Orders] table will contain multiple entities of type 'Order'.

    Since most data object development is actually done by dev teams these days, I tend to help create naming conventions that work best for those doing the task.

    As indicated previously, explore what works 'best' in your environment/organization, and create standards that will actually be used and supported.

    There's a story about when Eisenhower was the President of Columbia University. The head of the groundskeepers was in his office complaining about how students were not using the sidewalks, but instead cutting across the large grassy quadrangles to get to their classes. This created ruts, muddy spots, and was a bit 'unsightly'. The groundskeepers wanted an edict to compel 'proper' behavior. Eisenhower stood and looked out his window for a few minutes, watching students cross the grass, and then remarked that the sidewalks around the large grassy rectangles were just not serving the need of users. New sidewalks were installed that supported diagonal paths. Problem solved.

    Standards should support users doing their jobs. Standards should not attempt to compel behaviors that are antithical to normal practice. Attempts to do so are doomed to failure.

    And that's my 35,138 old Turkish Lira.


    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson



    Saturday, February 18, 2012 4:47 PM
    Answerer
  • Nice post. A lot of good points. I would like to comment on one of them however:

    >Long term data professionals 'tend' to use underscores to separate words -and that's a throwback to all cap platforms.

    I prefer underscore, and i never used an all cap platform. I use it because it clearly separates each word/entity.

    I'll bet it's psychological. On the MBTI, developers who prefer camel case are P, designers who prefer underscores are J.

    I'm a J.

    Monday, February 20, 2012 1:18 PM
    Answerer
  • FWIW, there is a certain level of "moot point" to this...

    As a contractor and a consultant, I find it rare to come across a truly Green Field in this area. Your conventions will usually be proscribed by the environment that already exists. The vast majority of SQL Databases have already established naming conventions. Whether or not you have personal preference (and I certainly do), you are always better off to adhere to an existing convention than to mix them in a single database - or even a single system, where new databases interact with old ones.

    In those rare cases where you have the luxury, well, my 2p is this is a "Tastes Great/Less Filling" argument.


    duncan davenport . data engineer and architect

    Monday, March 5, 2012 9:54 PM