SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    752
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question MySQL table and column Naming Conventions

    Hello MySQL Gurus,

    I have been reading articles, blogs, books about the naming conventions for designing a mysql database.

    Every time I read a new article / blog, I find the authors are suggesting different types of naming conventions which is confusing me a lot.

    For instance, i read an article on the Internet that says that table names should be singular, others say table name should be plural while other say every table name should have a minimum of 3 chars of acronym that represents a short name for that table.

    In terms of column names, some say the primary key should be just "id", while others say primary keys should have the table name followed by the id like "user_id", or the table acronym followed by id like "usr_id".

    So, if there is a message table, the column names should be such as message_id, message_title, message_description

    and for a comments table:
    comments_id, comments_title, comments_description

    I am totally confused as to what conventions should I follow and that should be industry standard.

    Can you guys please suggest me the right one?


    Many thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    there are no industry standards, i'm afraid

    further, it doesn't really matter what table and column names you choose, their behaviour will be the same regardless

    however, there are some conventions which are more useful, or perhaps more aesthetically pleasing

    for instance, embedding a table acronym at the start of all of its column names is, in my opinion, counter-productive, because it adds noise to the signal, as it were

    for instance, suppose you had a threads table and a comments table -- with the prefixing convention, you'd have thread_id and thread_title, and comment_id and comment_comment (see, already it's getting repetitive)

    but what about the foreign key in the comments table? would you call it thread_id, or comment_thread_id?

    the convention i prefer does not use table prefixes on column names ~except~ for foreign keys, so in the threads table i would use id and title, and in the comments table id and comment, with thread_id being the foreign key to the threads table

    and i prefer a plural table name, because it represents a collection of rows, such as threads and comments, rather than a single instance

    in the end, though, it's all your choice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    there are no industry standards, i'm afraid

    further, it doesn't really matter what table and column names you choose, their behaviour will be the same regardless

    however, there are some conventions which are more useful, or perhaps more aesthetically pleasing

    for instance, embedding a table acronym at the start of all of its column names is, in my opinion, counter-productive, because it adds noise to the signal, as it were

    for instance, suppose you had a threads table and a comments table -- with the prefixing convention, you'd have thread_id and thread_title, and comment_id and comment_comment (see, already it's getting repetitive)

    but what about the foreign key in the comments table? would you call it thread_id, or comment_thread_id?

    the convention i prefer does not use table prefixes on column names ~except~ for foreign keys, so in the threads table i would use id and title, and in the comments table id and comment, with thread_id being the foreign key to the threads table

    and i prefer a plural table name, because it represents a collection of rows, such as threads and comments, rather than a single instance

    in the end, though, it's all your choice
    I totally agree with u. But if u ask me, I prefare table naming nomaclature i.e if u hav a table 'comment'.. comment_id, comment_name... Whatsoever style u used. Ithink consistancy is bottom line..Stick to anyone u choose..

    thanks

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    I don't like the prefixing either. It's extra stuff you need to type that doesn't serve any purpose. When I look at the column in the `comments` table it's clear to me that that column belongs to the comments table, I don't need a prefix to tell me that.
    I also wouldn't name my cat (if I had one) "Cat Bob"; I can see it's a cat, no need to call him a cat too. I'll just call him Bob.

    Whatever you do, please don't prefix your tables with `tbl_`; there's really no point whatsoever.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


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
  •