SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast ParfaitMacaron's Avatar
    Join Date
    Mar 2013
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Naming conventions for MYSQL

    Hi,

    I'm wondering how you name your fields within a table. I've tried googling but there doesn't seem to be one standard way to do so.

    For example, in my member table, I have fields ID, forename and surname.

    Should I name them:

    - member_id
    - member_forename
    - member_surname

    ---------or
    - memberID
    - memberForename
    - memberSurname

    ---------or

    - ID
    - forname
    - surname

    Thank you!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    the last alternative is far better

    embedding the table name as part of the column name is counter-productive and needless noise
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast ParfaitMacaron's Avatar
    Join Date
    Mar 2013
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the last alternative is far better

    embedding the table name as part of the column name is counter-productive and needless noise
    Thanks for your reply. What happens though if I have another table named 'parent' and within that table I have a field also named 'forename'?

    How would I distinguish between the two "forename" fields in the two different tables? Thanks!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ParfaitMacaron View Post
    How would I distinguish between the two "forename" fields in the two different tables? Thanks!
    this would only occur if you happened to reference both tables in the same query

    in that case you would use table column qualifiers, as well as column aliases...
    Code:
    SELECT members.forename AS member_forename
         , parents.forename AS parent_forename
         ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast ParfaitMacaron's Avatar
    Join Date
    Mar 2013
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    this would only occur if you happened to reference both tables in the same query

    in that case you would use table column qualifiers, as well as column aliases...
    Code:
    SELECT members.forename AS member_forename
         , parents.forename AS parent_forename
         ...
    Thank you very much for your help. Appreciate it!

    Just one more question, if a field is called "date of birth" for example (has more than 1 word) would I write "dateOfBirth" or "date_of_birth"? Thanks again.

  6. #6
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    dateOfBirth or dateofbirth or dob (less chance of a typo in the last one)
    An underscore can sometimes be hard to spot in a hurry.

    The only time I add anything to identify a field as from a particular table is when using id.
    As there are usually several tables with a field called id, I tend to add a single letter in front of id. eg mID for members table, fID for the flight table, aID for the accounts table. I find this helps me when including a foreign key, as I can tell which table it is related to; months later this is a real benefit, imho.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ParfaitMacaron View Post
    ... would I write "dateOfBirth" or "date_of_birth"? Thanks again.
    birthdate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast ParfaitMacaron's Avatar
    Join Date
    Mar 2013
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    birthdate
    haha - good one! Thanks Rudy!

    Quote Originally Posted by Dr John View Post
    dateOfBirth or dateofbirth or dob (less chance of a typo in the last one)
    An underscore can sometimes be hard to spot in a hurry.

    The only time I add anything to identify a field as from a particular table is when using id.
    As there are usually several tables with a field called id, I tend to add a single letter in front of id. eg mID for members table, fID for the flight table, aID for the accounts table. I find this helps me when including a foreign key, as I can tell which table it is related to; months later this is a real benefit, imho.
    Thanks Dr John. Yes, I was curious about what people do for id fields.

    Thanks a lot! Much appreciated.


    ----By the way, one more question, if I have a table named member type should I name it with an underscore e.g. member_type or memberType or membertype?

    Thanks!

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    membertypes

    all table names should be plural, in my opinion, to constantly reinforce the idea that they are sets of rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast ParfaitMacaron's Avatar
    Join Date
    Mar 2013
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    membertypes

    all table names should be plural, in my opinion, to constantly reinforce the idea that they are sets of rows
    I read somewhere online somebody was encouraging to use singular, not plural. :/ What shall I do?!

    Ok, just found this article: http://leshazlewood.com/software-eng...l-style-guide/

    and they also suggest to use plural. Thanks! Is underscore between 2 words okay for tables as it looks easier to read? e.g. member_type as opposed to membertype?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ParfaitMacaron View Post
    i don't particularly like his use of "_maps" for relationship tables, but that's a minor peccadillo

    Quote Originally Posted by ParfaitMacaron View Post
    Is underscore between 2 words okay for tables as it looks easier to read? e.g. member_type as opposed to membertype?
    it's fine with me, i do the same
    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
  •