Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    907
    Thanks
    301
    Thanked 2 Times in 2 Posts

    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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    I think there's a clue for you in the fact the you read conflicting advice: There AREN'T any industry-wide standards. Period.

    There might be standards in a given company that all programmers for that company are expected to follow. But that's about it.

    The notion of three letter prefixes (e.g, "tbl" and "int" and "str") comes from Hungarian Notation, which was all the rage in the 80s and early 90s, especially since MicroSloppy used it throughout the company (to distraction!). But even the Slop Shop finally saw the light and has, for the most part, stopped using Hungarian in all the ASP.NET code. (Personally, I always hated it, and even during my short stint at MacroSloop I avoided it. There are many reasons it's dumb...not worth going into here...but I think it is especially dumb in database usage. But you will still find shops today that insist on using it.)

    Unless you are working for a company that has required coding standards, use what makes sense to you. Period.

    (I will temper that a bit by saying that the one convention I *really* like is naming primary and foreign keys the same, when possible. So yes, I would tend to have memid primary key in a members table and I tend to avoid the use of id alone. But I'm not fanatic about it. If you can't stand doing that, don't.')
    Last edited by Old Pedant; 01-22-2012 at 07:11 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (01-22-2012)

  • #3
    Regular Coder djh101's Avatar
    Join Date
    May 2009
    Location
    California
    Posts
    616
    Thanks
    48
    Thanked 65 Times in 65 Posts
    I've never heard of anyone appending the table name to its columns. That seems a little redundant. I name my columns by what they are- id, name, email, etc. There aren't really any universal naming conventions, as far as I'm aware- just use whatever works.
    Last edited by djh101; 01-22-2012 at 07:15 AM.
    "Yeah science!"
    Online Science Tools

  • Users who have thanked djh101 for this post:

    phantom007 (01-22-2012)

  • #4
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    907
    Thanks
    301
    Thanked 2 Times in 2 Posts
    What about table names? Do you prefer using singular or plural names?

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Quote Originally Posted by cancer10 View Post
    What about table names? Do you prefer using singular or plural names?
    Oh, yes. Definitely. Singular or plural names. I wouldn't even consider anything else.

    [That's almost not a joke. Some languages--e.g., Russian--have singular, dual, and plural. In Russian, in some grammar constructions the numbers 2, 3, and 4 use different rule than do larger numbers.]
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    phantom007 (01-22-2012)

  • #6
    Regular Coder
    Join Date
    Jan 2012
    Posts
    134
    Thanks
    0
    Thanked 32 Times in 32 Posts
    No matter what conventions you decide to adopt, the most important thing to do is be consistent. You'll give yourself headaches if you start mixing things.

    As far as table names go, I'm seeing a lot of people move towards singular table names. One reason for that is that you can often think of a row in a table as an object.

    users.name doesn't make as much sense as user.name when you're treating it as an object.

    Regardless, Old Pedant is right when he said there is no industry standard. If you're working in existing code or working for someone else, use the existing conventions. If you're writing your own code, use what you like best, but be consistent.

  • Users who have thanked KuriosJon for this post:

    phantom007 (01-22-2012)

  • #7
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    907
    Thanks
    301
    Thanked 2 Times in 2 Posts
    Thanks for the replies guys. I appreciate it.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Quote Originally Posted by KuriosJon View Post
    No matter what conventions you decide to adopt, the most important thing to do is be consistent.
    A-double-men!

    As far as table names go, I'm seeing a lot of people move towards singular table names. One reason for that is that you can often think of a row in a table as an object.

    users.name doesn't make as much sense as user.name when you're treating it as an object.
    Interesting viewpoint. Personally, I don't see any reason that the table names need to match the row names.

    I often use "people" as a table name and "person" as a record/row name.

    It doesn't bother me to say WHERE people.role = Administrator in my SQL queries, but I can certainly understand that viewpoint.

    But never mind...consistency truly is the key.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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