SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Data-Type for User-Roles?

    I have been persuaded to create a more generic table called "User" and add a column called "RoleType" to distinguish between 'Admins', 'Visitors', 'Customers', etc.

    My question is, "What Data-Type should I use?"

    I tend to prefer verbose data-types because looking at a column with integers in it tells someone else looking at an isolated table NOTHING!!

    By contract, seeing 'Admins', 'Visitors', 'Customers', etc. in a column is pretty self-documenting when the column is called "RoleType".

    The only down-side is that it is more of a pain programmatically to have to look for a string versus an integer value.

    What do you think?



    TomTees

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by TomTees View Post
    The only down-side is that it is more of a pain programmatically to have to look for a string versus an integer value.
    i don't buy that argument at all, sorry

    there is no "more of a pain" writing
    Code:
    WHERE roletype = 'Admin'
    as compared with
    Code:
    WHERE roletype = 3
    go ahead and use a VARCHAR instead of an integer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i don't buy that argument at all, sorry

    there is no "more of a pain" writing
    Code:
    WHERE roletype = 'Admin'
    as compared with
    Code:
    WHERE roletype = 3
    go ahead and use a VARCHAR instead of an integer
    Glad you agree with me on wanting to use something self-identitfying!!


    TomTees

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,799
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    It would be more of a pain using numbers since then you'd need to be constantly looking up what a particular number means.

    If you do use numbers then the simplest solution is to add a lookup table that converts them to the appropriate descriptions so that you can substitute those for the numbers.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by TomTees View Post
    I have been persuaded ...
    and whatever you do, don't let whoever this was try to get you to use ENUM

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    It would be more of a pain using numbers since then you'd need to be constantly looking up what a particular number means.

    If you do use numbers then the simplest solution is to add a lookup table that converts them to the appropriate descriptions so that you can substitute those for the numbers.
    I agree, but back in my MS Access days you'd be labeled a heretic for saying that! (Way too much over-normalization and using too many id's and codes in that world?!)

    Thanks,


    TomTees

  7. #7
    SitePoint Evangelist TomTees's Avatar
    Join Date
    Apr 2010
    Location
    Iowa
    Posts
    553
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    and whatever you do, don't let whoever this was try to get you to use ENUM

    Yah, I think we had that conversation earlier. (Or I did with someone?!)



    TomTees


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
  •