email easier to do mentally, and to avoid having a customer service supervisor with the email address [email]crackwhore@bigcorp.com[/email]. One of these forms is: [email]first.last@domain.com[/email] and in my case, the first 15 characters is: gordon.burditt@ which doesn't include ANY of the domain. Another problem is that you'll reject more than one address that starts with: john.smith@mail [quote] >I prefer the email address, since it has meaning and searches would be >straight forward.[/quote] Regardless of WHAT you use as a primary key, you're probably going to be doing a lot of lookups by email address (or did you expect users to memorize a member number?). You'll probably need at least one table (the membership table) containing email address, member number (the autoincrement key), name, etc. It will need a unique index on the email address (remember that a primary key IS a unique index). If you have other tables it might be best to use the member number as (or as part of) the primary key for those tables. If you need lookups by email address for the other tables, do a join against the membership table. If, for example, you have a message table whose primary key is compound (user, message #), you may want to avoid storing the entire email address in a record for each message, and the "member number" is shorter. There's not a lot of difference between a non-primary unique index and a primary key. Question: how do you determine which child is the primary child and which child is the twin? Gordon L. Burditt [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <12cfrl7gel3p396@corp.supernews.com> [ref] => <1153920477.501258.102210@m79g2000cwm.googlegroups.com> [htmlstate] => on_nl2br [postusername] => Gordon Burditt [ip] => gordonb.gfdgc@b [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 3 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> primary key - an email address or autoincrement integer - MySQL
Professional Web Applications Themes

primary key - an email address or autoincrement integer - MySQL

I have a users table, the typical firstname, lastname, phone, email etc.. Will have about 10,000 users. Mostly all selects as opposed to updates, inserts I was thinking of having the primary key as the email address field which I have set at varchar(70) and limiting the index to the first 15 characters. Is this going to be too slow compared to an index using an autoincremented integer? I prefer the email address, since it has meaning and searches would be straight forward. Any suggestions? My platform is : MySQL 5.0.22 /INNODB Thanks Roshan...

  1. #1

    Default primary key - an email address or autoincrement integer

    I have a users table, the typical firstname, lastname, phone, email
    etc..

    Will have about 10,000 users.
    Mostly all selects as opposed to updates, inserts

    I was thinking of having the primary key as the email address field
    which I have set at varchar(70) and limiting the index to the first 15
    characters. Is this going to be too slow compared to an index using an
    autoincremented integer?

    I prefer the email address, since it has meaning and searches would be
    straight forward.

    Any suggestions?

    My platform is : MySQL 5.0.22 /INNODB

    Thanks
    Roshan

    roch77@gmail.com Guest

  2. #2

    Default Re: primary key - an email address or autoincrement integer

    [email]roch77[/email] wrote:
    > I have a users table, the typical firstname, lastname, phone, email
    > etc..
    >
    > Will have about 10,000 users.
    > Mostly all selects as opposed to updates, inserts
    >
    > I was thinking of having the primary key as the email address field
    > which I have set at varchar(70) and limiting the index to the first 15
    > characters. Is this going to be too slow compared to an index using an
    > autoincremented integer?
    >
    > I prefer the email address, since it has meaning and searches would be
    > straight forward.
    >
    > Any suggestions?
    >
    > My platform is : MySQL 5.0.22 /INNODB
    >
    > Thanks
    > Roshan
    >
    One problem with limiting the primary key to 15 characters is you might
    have different emails - but they are rejected. For instance,

    [email]membernameexample.org[/email]
    [email]membernameexample.com[/email]

    Not likely, I will admit - but with 10K users it will happen. And what
    happens if someone doesn't have an email address (not likely, but
    possible).

    Remember - the reason for a primary key is to uniquely identify a row.
    That way if you do something like:

    SELECT rec_id, name, email FROM member_tbl WHERE state='confusion';

    Then while you're going through the records you want to change
    something, you can do

    UPDATE member_tbl SET state='not confused' WHERE rec_id=$rec_id;

    (assuming you saved the rec_id in variable $rec_id, of course).

    This would be much faster than doing the same in email.

    I use the member id for the primary key in my association work. Of
    course, you can still add an index to the email if you are doing
    requests which match or sort on the field.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: primary key - an email address or autoincrement integer

    >I have a users table, the typical firstname, lastname, phone, email
    >etc..
    >
    >Will have about 10,000 users.
    >Mostly all selects as opposed to updates, inserts
    >
    >I was thinking of having the primary key as the email address field
    >which I have set at varchar(70) and limiting the index to the first 15
    >characters. Is this going to be too slow compared to an index using an
    >autoincremented integer?
    Email addresses are not guaranteed unique in the first 15 characters.
    There are a number of standardized ways of assigning email addresses,
    often used by corporations to make name <--> email easier to do mentally,
    and to avoid having a customer service supervisor with the email address
    [email]crackbigcorp.com[/email]. One of these forms is:

    [email]first.lastdomain.com[/email]
    and in my case, the first 15 characters is:
    gordon.burditt
    which doesn't include ANY of the domain. Another problem is that
    you'll reject more than one address that starts with:
    john.smithmail

    >I prefer the email address, since it has meaning and searches would be
    >straight forward.
    Regardless of WHAT you use as a primary key, you're probably going
    to be doing a lot of lookups by email address (or did you expect
    users to memorize a member number?). You'll probably need at least
    one table (the membership table) containing email address, member
    number (the autoincrement key), name, etc. It will need a unique
    index on the email address (remember that a primary key IS a unique
    index).

    If you have other tables it might be best to use the member number
    as (or as part of) the primary key for those tables. If you need
    lookups by email address for the other tables, do a join against
    the membership table. If, for example, you have a message table
    whose primary key is compound (user, message #), you may want to
    avoid storing the entire email address in a record for each message,
    and the "member number" is shorter.


    There's not a lot of difference between a non-primary unique index
    and a primary key. Question: how do you determine which child is
    the primary child and which child is the twin?

    Gordon L. Burditt
    Gordon Burditt Guest

  4. #4

    Default Re: primary key - an email address or autoincrement integer

    Ok, thanks for the replies.

    I thought that by defining the primary key as the first 15 characters
    of a field, the uniqueness of each row would still be the whole email
    field. (not just the first 15 characters). Anyway, I see that I am
    wrong and it would be better to do the integer route as the primary
    key.
    And I will build another index for the email addresses.

    Thanks once again for clarifying.. Roshan

    roch77@gmail.com Guest

Similar Threads

  1. Email address in a form
    By TrickyTiger in forum Macromedia Dynamic HTML
    Replies: 4
    Last Post: December 16th, 06:29 AM
  2. email address??
    By RichyMan in forum Macromedia Dynamic HTML
    Replies: 7
    Last Post: August 2nd, 04:29 PM
  3. Hiding Email address
    By me in forum ASP.NET Building Controls
    Replies: 1
    Last Post: April 4th, 07:32 AM
  4. Getting email address.
    By Sailor in forum Microsoft Access
    Replies: 0
    Last Post: August 3rd, 11:22 AM
  5. ViewState + AutoIncrement -- Okay; Session + AutoIncrement -- Not okay
    By Gene Gorokhovsky in forum ASP.NET General
    Replies: 0
    Last Post: July 17th, 02:48 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139