CODEWITHSUNDEEP
phpmysql
ilhan
ilhan

Reputation: 8961

IDs in MySQL, standard IDs or alphanumerics?

I'm thinking of this, if I make a web site for a specific university's students should I make the ID as standard IDs on MySQL (integer, auto increment) or should I give the IDs as how is their email address is, like if the student's email address is [email protected] then his/her id is e12234 (on my web site). Is it ok, what about performance?

Edit: Also, there are such email addresses: [email protected] (exchange student) [email protected] (this is a professor)

Upvotes: 0

Views: 103

Answers (3)

methodin
methodin

Reputation: 6712

Generally you'd want to map strings to ids and reference the ID eveywhere

CREATE TABLE `student` (
    `id` int unsigned NOT NULL auto_increment,
    `email` varchar(150) NOT NULL
    PRIMARY KEY  (`id`)
)

This will reduce the size of any table reference the email table as it will be using an INT instead of a VARCHAR.

Also if you used part of their email and the user ever changed their email you'd have to go back through every table and update their ID.

Upvotes: 0

Wrikken
Wrikken

Reputation: 70490

If an emailaddress is unique and static in your population (and make very sure it is), you may make it a primary key, and actually a full normalization would favor that option. There are however some pitfalls to consider:

  1. People change emailaddresses once in while. What if a student becomes a professor, or is harassed on his/hers emailaddress so he/she applied for a new address and got one? The primary key shold not change, ever, so there goes your schema.
  2. Sanitizing emailaddresses takes a little bit more effort then integers.
  3. Depending on how many foreign keys point to this ID, needed storage space could be increased, and joining on CHARs rather then INTs could suffer in performance (you should test that though)

Upvotes: 0

bzarah
bzarah

Reputation: 358

I would strongly recommend a separate, independent value for the id (integer, auto increment). Id values should never change, never be updated. People change their emails all the time, corporations reissue the same email address to new users sometimes.

Upvotes: 1

Related Questions