Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts

    userID - best field type to use

    I have a userID field in my database and was curious to know what was the best type of data to store.

    Should i use:

    an auto-increment (int) field type or
    a (varchar) field type which is generated using hash5 or
    something else

    Does it really matter?

    sorry for the basic question but want to start on the right track

    thanks
    Luke

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Nothing to do with PHP, moving to other databases.
    If I'm using a surrogate, I always use a binary(16). Don't use a varchar for a hash; use a char with the exact size required.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    It all depends on what you want people to use as their userid - if you use their email address then it needs to be a char field big enough to hold up to 320 characters (the maximum valid length for an email address). If you let them choose a name then you want a varchar field of whatever maximum length you decide to allow them to use. If you want to allocate a number then an int field would be most appropriate (in which case you might use an autoincrement so that each new user gets a different number allocated automatically.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    appoligies for the incorrect forum, had posted in there moments before and didnt think about the topic when posting this.

    thanks for the advice, with the binary value do you generate it with a hash?

    thanks!!

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    @felgall - thanks for your reply, tbh im not sure what i want, thats the problem i wont be using their email address nor name but not sure whether to use an int or a random string.

    think the auto increment would look neater and potentially easier to follow but just curious to see what others use

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    I use a custom generated 128-bit GUID and save it to the binary.

    Edit:
    In mysql, you could use the built in UUID() function instead of a custom guid generator. That can't fit into a binary(16) though since that includes the hyphens. You can remove them and convert the hex:
    Code:
    UNHEX(REPLACE(UUID(), '-', '')))
    Last edited by Fou-Lu; 02-13-2014 at 10:09 PM.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    LJackson (02-13-2014)

  • #7
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    One problem with using autoincrement is that it becomes trivially easy to work out what other accounts probably exist and so someone just has to crack the password - they don't have toguess what accounts might exist first.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •