SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Tables with one field (design question)

    I was just wondering what the concensus was on tables that have one field and that field is the primary key.

    Example:

    Code:
    Users
    -----------------------------------
    id | username | password | roles_id
    -----------------------------------
    
    Roles
    ---------------------------
    id
    ---------------------------
    The id field in the roles table would be values like "Quality Control" and "Administrator". Since these values will always be unique, I could use it as the primary key rather than using an auto-increment id with a second field labelled "name".

    My question is, is this good design practice?

  2. #2
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Indexes based on numeric fields will be smaller in size.

    Queries based on numeric fields and numeric field indexes are faster.

    The users table would be bigger for no good reason because of the roles_id non-numeric values.

    In the future the role names might change easily as to no longer be unique. Updating text values in the smaller roles table would be easier (for you and the RDBMS) and it would make more sense than updating the whole presumably larger users' table roles_id text column.

    All the above say no. Maybe you have better counterarguments to not use auto-increment?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    using a numeric foreign key in the users table to refer to the roles table means that a join will always be required to retrieve the role name, and a join is always slower than a query on a single table

    searching for users with a specific role name will require not only a search of the roles table but also a search of the users table, and two searches are always slower than one

    when browsing the users table (e.g. in a front end app like phpmyadmin) you will understand what the roles are if the role name is used as the foreign key

    so to answer your question, ryan, yes, it's good practice

    declaring auto_increment surrogate keys indiscriminately all over da place is bad practice

    discriminate, and do it only when it's warranted
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    And most importantly, for no: when you change something in the roles names in the roles table (like, for example, when you misspelled a role name), it won't be automatically reflected in users table.

    While with a auto-increment/sequence/anonymous key, the effective values are a relative quantity.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by itmitică View Post
    And most importantly, for no: when you change something in the roles names in the roles table, it won't be automatically reflected in users table.
    yawn... ON UPDATE CASCADE
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yawn... ON UPDATE CASCADE
    yawn... it works, for kiddies databases.


    <hr>


    If we're at amateur hour, he could just put 1, 2, 3, ..., 13 in the roles_id column in the users table, forget about roles table and use a php array to decode it. I wonder what happens when he loses the napkin with the user roles he got from the HR?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by itmitică View Post
    If we're at amateur hour, he could just put 1, 2, 3, ..., 13 in the roles_id column ...
    now you're just being childishly churlish

    ryan, you have to make up your own mind, there's plenty of information for both the pro and con sides of using surrogate auto_increments

    please try to remember to discriminate
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Nov 2009
    Location
    Ontario, Canada
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll have to do some more reading. Thanks to both of you for pointing out the pros and cons for both methods.

  9. #9
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    now you're just being childishly churlish
    No, your yawning is. I was being exceptionally civil until that moment.

    Anyway, primary keys are supposed to be immutable, never changing, constant. That's the theory, anyway.

    Which makes a trigger like ON ... CASCADE for a PK a bad practice. To paraphrase someone I look up to when it comes to databases:
    If your design requires it -- change your design now if you can.


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
  •