I am going for a course known as "database systems" as well as for our class project I must build a website.

Here's one particualr table I produced:

CREATE TABLE customers

(

  uid INT NOT NULL AUTO_INCREMENT,

  username VARCHAR(60),

  passhash VARCHAR(255),

  email VARCHAR(60),

  rdate DATE,

  PRIMARY KEY(uid)

)

The professor explained "uid" (user id) was completely useless and unnecessary and that i must have used the username because the primary key, since no two customers can have a similar username.

I told him it had been convenient for me personally make use of a user id because after i call something similar to domain.com/viewuser?id=5 I simply look into the parameter with: is_number($_GET['id'])... obviously he wasn't convinced.

Since I have seen user_id along with other similar characteristics (thread_id, comment_id, amongst others) on lots of lessons and searching in the database schema of popular software (eg. vbulletin) there has to be lots of other (more powerful) reasons.

So my real question is: How does one justify the necessity of a not null auto incrementing id like a primary key versus using another attribute such as the username?

Auto-incrementing primary secrets are helpful for many reasons:

  • They permit duplicate user names as on Stack Overflow
  • They permit the consumer title (or current email address, if that is accustomed to login) to become transformed (easily)
  • Chooses, joins and card inserts are faster than varchar primary secrets since it's considerably faster to keep a number index
  • While you pointed out, validation becomes quite simple: if ((int)$id > )
  • Sanitation of input is trivial: $id = (int)$_GET['id']
  • There's much less overhead as foreign secrets do not have to duplicate potentially large string values

I'd say attempting to use a piece of content of string information like a unique identifier for any record is an awful idea when a car-incrementing number secret is so easily available.

Systems with unique user names are acceptable for really small amounts of customers, however the Internet has made them essentially damaged. Considering the sheer number of individuals named "john" that may need to connect to an internet site, it's absurd to require all of them to utilize a unique display title. It results in the awful system we have seen frequently with random numbers and letters designing a username.

However, even just in something in which you enforced unique usernames, will still be an undesirable option for a principal key. Make a user with 500 posts: The foreign type in the posts table will retain the username, copied 500 occasions. The overhead is prohibitive even before you think about that a person might eventually have to change their username.

When the username may be the primary key along with a user changes his/her username, you will have to update all of the tables that have foreign key references towards the customers table.

This really is typically known as a surrogate key and contains benefits. One of these is insulation your database associations in the application data. More particulars and also the corresponding disadvantages are available in the wiki link provided above.