SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Field naming conventions

    How does everyone name table fields?

    Which style is better/more acceptable...

    PRODUCT table (approach #1)
    ID
    Name
    Color
    Weight
    Style

    OR

    PRODUCT table (approach #2)
    ProductID
    ProductName
    ProductColor
    ProductWeight
    ProductStyle

    Why do you use one approach versus the other versus another alternative?

    I prefer the first approach because having Product.ProductName in a query seems verbose and it adds no real value.



    TomTees

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,081
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    I also go for the first approach, but I write ID it "id" and don't start column names with a capital either, since in my opinion everything starting with a capital is a classname, while everything starting with a lowercase character is a variable name, and I deem database fields name to be variables, in a slight abuse of notation. (In other words iUseCamelCase)

    I use the first approach because
    1) It isn't as verbose as the second approach, like you said
    2) It makes JOIN queries very readable: WHERE city.countryId=country.id

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by TomTees View Post
    I prefer the first approach because having Product.ProductName in a query seems verbose and it adds no real value.
    i totally agree -- (1) verbose (2) no real value

    the only thing worse would be Product_table.ProductName
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I very much prefer the first option myself, but I've had a hard time getting any traction at work. This is mainly because they insist on foreign keys being named identically to the fields they reference in other tables. This imposes the latter approach at least as far as keys are concerned.

    What is most perplexing about this that we use SQL Server, which offers no syntactic support for natural joins, which for me is the only convincing argument to keep field names identical across tables.

    Cheers,
    D.

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,826
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Naming the id field productID is about the only field that should ever be considered for using the second style of naming convention. There is no reason whatever for any of the other fields to need product on the front of the field name since you can easily place it there when necessary by adding the table name to the front eg. product.Name

    There is the possibility of some tables containing more than one id field though and since they are also likely to have the same id field appear in multiple tables means that specifying what it is the id for as a part of the field name can help make things easier to follow.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  6. #6
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,267
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by disgracian View Post
    I very much prefer the first option myself, but I've had a hard time getting any traction at work. This is mainly because they insist on foreign keys being named identically to the fields they reference in other tables. This imposes the latter approach at least as far as keys are concerned.

    What is most perplexing about this that we use SQL Server, which offers no syntactic support for natural joins, which for me is the only convincing argument to keep field names identical across tables.

    Cheers,
    D.
    Seems to me you've got a large number of people involved, and someone's trying to enforce some sort of consistency/standards so people can look quickly at a query (especially complex ones) and know what is what.

    As long as the naming style is consistent from table to table, you'll be fine. So if a field is named ProductID, and everyone knows that ProductID reflects to the ID field on the Product table, you'll be fine. But if people start using the ID nomenclature and it's not for a foreign key, you'll have problems. Or if the field is ProductID in one table and Product_ID in another (not a major problem, but consistency...)

    The only noticable benefit to using more verbose names is you won't have to alias like fieldnames on complex joins (ex Product.Name and Category.Name)

    (oh, and I would never use Product.ProductName in a query. It would either be Product.Name or ProductName. If you've got ProductName in two different tables, then you've most likely got a problem with your nameing conventions)
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  7. #7
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I prefer any convention that involves less typing. "ID" is a perfectly valid name for a surrogate key field, because identifying a record is the only role it plays. My naming convention for foreign keys is almost always the name of the table they reference.

    Cheers,
    D.

  8. #8
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,826
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    Seems to me you've got a large number of people involved, and someone's trying to enforce some sort of consistency/standards so people can look quickly at a query (especially complex ones) and know what is what.
    I agree.

    Where a business has a set of standards for their business that specify how something should be done then that's the way you do it regardless of whether it is your preferred way or not. So if their standards say to use option 2 then that's the only choice you've got in that situation.

    The standards are there to ensure that everyone in the team does things the same way so that it doesn't matter who does what, when you get the job of having to fix something it will look the same regardless of who did it.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by felgall View Post
    The standards are there to ensure that everyone in the team does things the same way ...
    as well as to foster innovation

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I just grit my teeth and bear it, but few things annoy me as much as having to type "join some_table_with_a_long_name as b on a.some_table_with_a_long_name_id = b.some_table_with_a_long_name" for the thousandth time (yes, some get almost that long).

    That's why I shall relentlessly pimp for my convention. It's better in every conceivable way.

    Cheers,
    D.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •