SitePoint Sponsor

User Tag List

Results 1 to 18 of 18

Hybrid View

  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,529
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Are Long Names OK??

    Is there a problem having a MySQL table named "article_survey_question"??

    Is there a problem having a MySQL field in a junction table named "articleSurveyQuestionID"??


    I am thinking I have to join 4 tables to get what I need, and I just can't think of more descriptive names that are shorter, so I figure it is better to err on the side of clarity even if it gives me verbose table and field names?!

    What do you think?


    Debbie

  2. #2
    padawan silver trophybronze trophy markbrown4's Avatar
    Join Date
    Jul 2006
    Location
    Victoria, Australia
    Posts
    4,095
    Mentioned
    28 Post(s)
    Tagged
    1 Thread(s)
    Generally no, you obviously want a short self explanatory name.
    A standard for id's is simply just to use 'id' for every table, which is shorter

    We'd need to see your schema to see, but I don't know you'd need to join four tables
    Does article_questions work as a name?

    Another standard I've adopted from Rails is to make all table names plurals e.g. article_questions

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,529
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by markbrown4 View Post
    Generally no, you obviously want a short self explanatory name.
    A standard for id's is simply just to use 'id' for every table, which is shorter
    Right. I do that.

    But my example was a Foreign Key in my Junction Table...


    We'd need to see your schema to see, but I don't know you'd need to join four tables
    Does article_questions work as a name?
    Far be it for me to stop someone who wants to help some more!!

    Check out this new thread about Thoughts-on-Tables-for-Surveys.


    Another standard I've adopted from Rails is to make all table names plurals e.g. article_questions
    I really dislike plural table names, but I know this is a religious battle...

    Thanks for all of the comments!!


    Debbie

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Is there a problem having a MySQL table named "article_survey_question"??

    Is there a problem having a MySQL field in a junction table named "articleSurveyQuestionID"??
    no to both

    long name, short name, it's up to you
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru Jason__C's Avatar
    Join Date
    Oct 2009
    Location
    Racoon City
    Posts
    656
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no to both

    long name, short name, it's up to you
    Spot on. Unless you work for someone else, who gives a crap how it's worded. Just be consistent with how you name the columns, you will have less frustration.
    Chuck Norris is so tough, mosquitos ask for permission before they bite him

  6. #6
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,439
    Mentioned
    58 Post(s)
    Tagged
    0 Thread(s)
    I agree with the others. I generally prefer names that are fully descriptive, even if it means some extra typing. I also try to avoid non-obvious abbreviations in names as far as possible.

    Another important point is consistency. For example, always name your surrogate primate keys, say, ID (as per Mark Brown's suggestion). Going further, name the foreign keys that refer to the IDs as, for example, CustomersID, OrdersID, etc (plural form of the entity name, no underscores). (This is just an example. The point is to adopt a convention, and to use it consistently.)

    Mike

  7. #7
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,807
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    I want to throw in my agreement as well, and this gem that arrived in my RSS feed sometime within the last week so you can at least have a laugh of what you might be dealing with if you opted not to give meaningful names to your columns/tables.

  8. #8
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,439
    Mentioned
    58 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    I want to throw in my agreement as well, and this gem that arrived in my RSS feed.
    Thanks for that. It's It's pretty horrible, isn't it.

    Mike





  9. #9
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    I want to throw in my agreement as well, and this gem that arrived in my RSS feed sometime within the last week so you can at least have a laugh of what you might be dealing with if you opted not to give meaningful names to your columns/tables.
    Probably made by the same guys who created a database I was asked to help with, which had such user friendly names as col60, col61, col64, col70 etc., a naming convenion they used in several tables in the db. Although there were about half with names like first_name, surname, etc.
    Real problem was the likes of col64 etc were almost always boolean !!!

  10. #10
    SitePoint Member
    Join Date
    Aug 2012
    Location
    Brighton
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No there is no issue with long names

  11. #11
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    695
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Personally, I would never use a simple name like "id". It's confusing for one, especially if there are no FK constraints to point you to what that ID refers to. So I make my IDs common across all tables. A quick example would be something like this:

    Code:
    Blog Table
    blog_id
    blog_title
    blog_content
    author_id
    
    Tags Table
    tag_id
    tag_name
    tag_count
    
    Blogs2Tags Table
    blog_id
    tag_id
    
    Authors Table
    author_id
    author_name
    author_email
    author_password
    It's a very simple example so it may not appear necessary, but I've worked on some systems with 100s of tables and foreign keys are all over. I've seen some where they'd have:

    • id
    • cid
    • tid
    • pid

    You may work out eventually that they mean:

    • id - table PK and row identifier
    • cid - category ID. In this case the manufacturer
    • tid - tariff ID
    • pid - phone ID

    Only then you go elsewhere and pid means product ID, and there are still cids, tids and ids. If you're going to use an ID, make it unique. It gets even more confusing when you end up with something like:

    Code MySQL:
    SELECT *
      FROM products p
         , phones ph
         , pictures pic
         , people pe
     WHERE p.pid = ph.id
       AND ph.pid = pic.id
       AND pic.pid = pe.id

    Wait, what? All of them have an id column and a pid column, and it's not obvious what any of them do! This is better:

    Code MySQL:
    SELECT *
      FROM products p
         , phones ph
         , pictures pic
         , people pe
     WHERE p.phone_id = ph.id
       AND ph.pic_id = pic.id
       AND pic.pe_id = pe.id

    But this is better still IMHO:

    Code MySQL:
    SELECT *
      FROM products p
         , phones ph
         , pictures pic
         , people pe
     WHERE p.phone_id = ph.phone_id
       AND ph.pic_id = pic.pic_id
       AND pic.people_id = pe.people_id

    Suddenly the relationships appear much more clear.

    Yes, it's a deliberately messy situation created completely off the top of my head, but I've actually been in situations where databases have been that confusing (more so, actually) so I always try to keep field names unique, especially where they'll be used as FKs.

    And no, I can't always use FK constraints to make relationships clearer. As we found out when we migrated from InnoDB to NDB Cluster, it didn't support FK constraints (I believe that is now remedied, mind you)

    I think it's personal preference, but I'd always try to make it as clear as possible, especially if I'm sure that a) I won't be the only person maintaining it and b) it's likely to grow large

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Antnee View Post
    Personally, I would never use a simple name like "id". It's confusing for one...
    it may be confusing to you, but certainly i agree that the proliferation of different and overly short id names that you just made up are confusing...

    ... but perhaps i can restate the convention and see if you might not find it simple enough if you grasp the basic idea behind it

    the basic idea is this: when the PK of a table is an autoincrement, it's called "id", and when an id isn't the PK, then it's a FK, and it isn't called "id" but rather entity_id, where entity is the table it's referencing

    so joins will always look like this --
    Code:
    ON orderitems.product_id = products.id
    there is absolutely no ambiguity about the role that each of these columns is playing, and there is no guessing as to which of these is the "one" table and which is the "many" table, since each PK-FK relationship is a one-to-many relationship

    dead simple, self-documenting, easy peasy

    as to the second, implied, convention in yourt post, antnee, of stuffing the table name into each column name as a prefix, i find this most disagreeable -- you've upped the noise factor without increasing the signal
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    695
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    as to the second, implied, convention in yourt post, antnee, of stuffing the table name into each column name as a prefix, i find this most disagreeable -- you've upped the noise factor without increasing the signal
    Sorry, missed that bit - assume it was edited in while I was replying

    I disagree. Saying product_id = product_id is perfectly clear. I would usually be using shortened aliases in tables though so I would never directly reference products.product_id or phones.product_id, but rather it would be something like p.product_id, ph.product_id. I find it much easier to understand when you're 70 lines down from where you defined the alias.

    Again though, I feel that it's personal preference, or you should be conforming to your projects guidelines, whatever they may be. I just disagree with ambiguous column names. The tid, cid, pid etc examples are actually not made up. I worked on that database for a couple of years and I saw every single developer pull his hair out more than once because he'd got mixed up. Mostly because in the products table there was a pid that referred to a phone ID, but elsewhere pid meant product ID and phid was the phone ID.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Antnee View Post
    Sorry, missed that bit - assume it was edited in while I was replying

    I disagree. Saying product_id = product_id is perfectly clear. I would usually be using shortened aliases in tables though so I would never directly reference products.product_id or phones.product_id, but rather it would be something like p.product_id, ph.product_id. I find it much easier to understand when you're 70 lines down from where you defined the alias.
    you're still thinking about the ids, and i think we've all agreed that a PK/FK convention is necessary, and that it needs to make (some sort of) sense, and that it needs to be followed

    no, i was talking about the other columns, the ones marked in red here, where the table name is embedded as a prefix in the column names --
    Blog Table
    blog_id
    blog_title
    blog_content
    author_id

    Tags Table
    tag_id
    tag_name
    tag_count

    Authors Table
    author_id
    author_name
    author_email
    author_password
    i find this most disagreeable, in a signal-to-noise kind of way
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,439
    Mentioned
    58 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i was talking about the other columns, the ones marked in red here, where the table name is embedded as a prefix in the column names -- i find this most disagreeable, in a signal-to-noise kind of way
    I missed that too. So, you're referring to the practice of including the table name in the names of the columns within that table (so the Authors table contains a column named Authors_Email, for example).

    I also find that disagreeable. It adds redundancy, hinders readability, and increases the general clutter.

    Mike

  16. #16
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    695
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yeah, that works, and that was my second example (mostly)

    I still think it's down to personal preference if you're working on your own project, or it's down to a project leader (lead dev? dev manager?) to define conventions that everyone should conform to, and whatever is decided on needs to be actually used. A while back some of us had email addresses like joe.bloggs@company.com while others had jbloggs@company.com and others just joe@company.com, even AFTER the IT manager put a policy in place where all email addresses should be firstname.lastname@company.com, so we had a few different people called Pete (three IIRC) and they all had email addresses following different conventions (no conventions?) and therefore mail used to go to the wrong place.

    My point is simply that some kind of convention should be agreed on, and then enforced!

  17. #17
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,439
    Mentioned
    58 Post(s)
    Tagged
    0 Thread(s)
    Antnee,

    You've made some good points. I agree with much of what you said.

    However, unlike you, I always use ID as the name of the primary key in every table - even for those tables that don't strictly need an additional ID field, such as your Blogs2Tags table. This has saved me a significant amount of time over the years. Whenever I do a join, I never have to think about the linking column name in the parent table: it is always ID. You might think that's not a big deal, but it really does make life simpler, and I'd hate not to be able to do it.

    Similarly, I always use a consistent naming system for foreign keys - as you do too. You use the singular form of the entity name, followed by an underscore, followed by the referenced table's primary key. That's fine. I've standardised on the plural form without the underscore. But that doesn't matter. The important thing is that it's the same for every table. Again, the benefit is not having to think about it when writing your queries.

    We're drifting away from the original question somewhat, but this is all useful stuff for people to keep in mind.

    Mike

  18. #18
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    695
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sorry, yeah, I missed the point. Terrible example, I do agree. Over exampled? :|


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
  •