SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Primary Key Basics

    I was watching a few tutorials and wondered if this is good practice or not.

    This guy made two tables, one called Users the other called Content.
    His primary keys were named:

    users_id for Users table
    content_id for Content table

    He suggested this to avoid conflict with the Primary Key on a JOIN.
    I know you can just alias it which I prefer right now.

    Which one is better practice? Or does it not matter?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It doesn't matter, it's like camel case vs. underscores, a preference.

  3. #3
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool thanks

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    That is a conventional rule that all developers must apply to were I work. I remember thinking it was worthless when I began working there but as time has progressed I have seen it to be very useful for the reasons you stated. Although you might not think so that convention makes complex joins and queries in general easier to comprehend. However, as Dan stated its a personal preference.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by oddz View Post
    Although you might not think so that convention makes complex joins and queries in general easier to comprehend.
    i have seen thousands of very complex joins and queries and i still don't think it's a good idea to embed the entity name into the column name

    speaking of camels, the fable of the camel's nose in the tent comes to mind...

    if you prefer user_id instead of id for the users table, then shirley you will also prefer this --
    Code:
    CREATE TABLE users
    ( user_id       INTEGER     NOT NULL PRIMARY KEY 
    , user_name     VARCHAR(16) NOT NULL
    , user_pswd     VARCHAR(16) NOT NULL
    , user_fullname VARCHAR(99) NOT NULL
    , user_email    VARCHAR(99) NOT NULL
    );
    to this --
    Code:
    CREATE TABLE users
    ( id       INTEGER     NOT NULL PRIMARY KEY 
    , name     VARCHAR(16) NOT NULL
    , pswd     VARCHAR(16) NOT NULL
    , fullname VARCHAR(99) NOT NULL
    , email    VARCHAR(99) NOT NULL
    );
    where do you draw the line? and if you say okay, draw the line at "id" then my question is why stop there?



    yes, it is a personal preference, isn't it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Barefoot on the Moon! silver trophy Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,606
    Mentioned
    56 Post(s)
    Tagged
    1 Thread(s)
    I tend to use user_id or uid or the user id. It helps eliminate confusion in my mind as to which id it is. The rest of the fields...I just make reasonably descriptive so they make sense at a glance. That's probably the most important thing.

    After all, I don't think most folks would use something like ZipBingWhee_id as the user id. Unless that one makes sense to you...
    Visit The Blog | Follow On Twitter
    301tool 1.1.5 - URL redirector & shortener (PHP/MySQL)
    Can be hosted on and utilize your own domain

  7. #7
    SitePoint Guru rageh's Avatar
    Join Date
    Apr 2006
    Location
    London, Formerly Somalia
    Posts
    612
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Force Flow View Post
    I tend to use user_id or uid or the user id. It helps eliminate confusion in my mind as to which id it is.
    Or userID

    id is an id. I am begining to doubt whether using user_id is more useful than plain id.
    ------------------

  8. #8
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If you are going to call the foreign key in the other table user_id, then you're going to have to specify the table name or alias in the join either way, so you add verbosity without much benefit.

    "ON user_id = user_id" is going to be ambiguous, "ON id = user_id" will be too if both tables have an id, so you'll end up with "ON users.id = posts.user_id" regardless of the choice you made... so the longer name didn't help.

    If I need an artificial identifier, I use "id". I switched from camel case (userId) to underscore (user_id) some years ago for foreign keys, mainly because it's easier to read quickly, and a good convention if you're ever going to use an ORM. Many of them default to underscores as the column names become machine parseable (split at underscore, left side is the type of object the FK references).

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    If you are going to call the foreign key in the other table user_id, then you're going to have to specify the table name or alias in the join either way, so you add verbosity without much benefit.
    on the contrary, it is vital (in my opinion) that in any query of more than one table (i.e. a join) that every column used in the query be qualified with its table name (or table alias)

    this, to me, is not a matter of preference, it is mandatory, and essential to understanding the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •