SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    848
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    id or user_id as primary key

    hi I am wondering whether there is any advantage of using id as a primary key in a users table, versus something more descriptive like userid or user_id.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You mean the name of the column? It's a bit shorter.

  3. #3
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,810
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Just make sure that you don't have fields in other tables that use the same field name for something different. Having the same field name mean the same thing across the entire database makes a lot of things a lot simpler.
    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="^$">

  4. #4
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    user_id in a table named users is repetitive when "id" is more then enough to convey its purpose.
    Now when used in another table as a forgien key that is different.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  5. #5
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would use userId in a table of users, prodId in a table of products and catId in a table of categories.

    If they were all named just id it could make the readability of sql queries a little alkward - just my

  6. #6
    SitePoint Guru
    Join Date
    Nov 2008
    Posts
    848
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok so it seems both are recommended, and both are good.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    there are actually two separate issues -- whether to use a surrogate key, and what to call it

    the surrogate key would be an auto_increment, where the user is identified by a number, as compared with the user being identified by a userid like 'BigTodd'

    my own preference is never to use "id" as part of the column name unless you're dealing with a surrogate key

    so in the previous example, 'BigTodd' would be called username, and it would still be a perfectly good primary key

    when i use an auto_increment, i always give it the name "id" (so that i know that every table that has a column called "id" is using an auto_increment as the primary key)

    then foreign keys in other tables are called "userid" if they reference the id column in the users table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kalon View Post
    I would use userId in a table of users, prodId in a table of products and catId in a table of categories.

    If they were all named just id it could make the readability of sql queries a little alkward - just my
    You could use the "[table].[field]" syntax if you are having readability problems.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by logic_earth View Post
    You could use the "[table].[field]" syntax if you are having readability problems.
    exactly

    and, on a somewhat related topic, i insist on the "[table].[field]" syntax (although i would use "column" instead) whenever a query involves more than one table

    as a direct consequence of this, the convention of stuffing the table name into the front of the column name (as in ord_custname, ord_total, ord_shipdate) is redundant

    which means that you name your columns custname, total, and shipdate

    of course, if a query involves only one table, then the "[table].[column]" syntax is unnecessarily wordy, and i would then drop the table qualifier
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by logic_earth View Post
    You could use the "[table].[field]" syntax if you are having readability problems.
    Yes I could and do when the same column name exists in more than one table - eg catId in the category table and catId in the products table. But I prefer the primary key name to be specific to its table.

  11. #11
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,810
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Kalon View Post
    But I prefer the primary key name to be specific to its table.
    That means you can't use the same name for your foreign keys which will make your naming a real mess. The primary key name is the one that is almost guaranteed to be needed in other tables as a foreign key where you should use the same name since it is the same data.
    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="^$">

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by felgall View Post
    That means you can't use the same name for your foreign keys which will make your naming a real mess.
    i disagree

    use id as the PK, and foobarid as the FK

    using the same name because it's the same data is a slippery slope
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    That means you can't use the same name for your foreign keys which will make your naming a real mess. The primary key name is the one that is almost guaranteed to be needed in other tables as a foreign key where you should use the same name since it is the same data.
    Maybe I'm not explaining myself clearly.

    Here is an example of what I do.

    Table name: tblcategory

    Column

    fldCatId - primary key

    Table name: tblproducts

    Column

    fldProdId - primary key
    fldCatId - foreign key

    tblcategory has a 1 to many relationship with tblproducts.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    first suggestion (and it's a pretty strong suggestion) -- lose the "tbl" in your table names, and the "fld" in your column names

    sql is a strongly typed language

    you cannot use a table name where a column name should be used, and vice versa

    there is no point in making your names harder to read
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    first suggestion (and it's a pretty strong suggestion) -- lose the "tbl" in your table names, and the "fld" in your column names

    sql is a strongly typed language

    you cannot use a table name where a column name should be used, and vice versa

    there is no point in making your names harder to read
    Imho the red bit is subjective and I accept many people feel the same way.

    But I was taught to use the Hungarian Notation System (or whatever its actual name is) and I can see merits in it and that is what I am used to using.

    I also use it (or my version of it) to name html form elements - eg textboxes are prefixed with txt, selects lists are prefixed with sel, labels with lbl etc etc.

    I think what naming convention you use is not so important as long as it is clear. What is important, imho, is that you use a naming convetion consistantly throughout your code and not mix and match naming styles/conventions because that would make your code really hard to read for anyone else looking at your code.

    btw - your second suggestion is..........................

  16. #16
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,810
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Kalon View Post
    But I was taught to use the Hungarian Notation System (or whatever its actual name is) and I can see merits in it and that is what I am used to using.
    There are no merits whatever in using it for names inside the database and many many reasons to NOT use it there. Whoever taught you to use that obviously didn't make it clear enough to you that it doesn't apply to databases.
    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="^$">

  17. #17
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes I agree it is not mandatory for databases and most probably for anywhere else for that matter.

    But when using data like for example

    Code:
     
    $row['fldProdName']
     
    $_GET['txtUsername']
     
    $_GET['selCatId']
    I prefer to use this type of notation as it makes it perfectly clear to me where the data is coming from.

    Please don't misunderstand me. I am not saying this is the only or best way of naming things. It is just the way I do it.

    I said before, it is more important to use a consistant naming convention throughout your code. Whether that convention makes it easier or harder to read for anyone else looking at your code is totally subjective.


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
  •