SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best practices for using mysql field names.

    Best practices for using mysql field names.

    CASES:

    1>Type1
    Code:
    ---------
    users
    ---------
    id
    first_name
    last_name
    email
    password
    ----------
    VS

    2> Type2

    Code:
    ---------
    users
    ---------
    user_id
    user_first_name
    user_last_name
    user_email
    user_password
    ----------
    ie prefixed with singular table name : user


    I would like to know the opinions of forumians that which naming conventions (among two as shown above)
    do you deploy and why?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    never, ever option 2

    embedding the table name into the column name is unnecessary, wasteful, and above all, obfuscating

    it makes queries ~so~ much harder to read
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    never, ever option 2

    embedding the table name into the column name is unnecessary, wasteful, and above all, obfuscating

    it makes queries ~so~ much harder to read
    I also prefer the #1 usage. But in some open source like dotproject i got such table name prefix in table fields, so thought to arise the post in sitepoint.

    Anyway thanks Rudy for the opinion.

  4. #4
    SitePoint Member
    Join Date
    Mar 2008
    Location
    London
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So what do you do in the situation where you have two tables such as 'product' and 'category' and they both have a column named 'description'. If you do a simple * and join the tables, the resulting set of data will have a duplicate field.

    Code:
    select * from product p, category c where p.id=c.id;
    result->description = ???

    Yes you could do this:
    Code:
    select p.description as `product.description`,
    c.description as `category.description` etc..
    .

    But that means any time you change the database, you have to update all your data access code.

    Is it recommended to alias each column on every query as I've done in the 2nd query above?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jspash View Post
    Is it recommended to alias each column on every query as I've done in the 2nd query above?
    when necessary, yes
    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
  •