NYCPHP Meetup

[John Campbell]

On Sat, Dec 11, 2010 at 11:20 PM, jean-baptiste verrey
<jeanbaptiste.verrey at gmail.com> wrote:
> ok to make it quick:
>  it means that I simply should have id as a primary key and use unique for
> the fields that makes the row unique!?

Yes and no.  Use id as the primary key.  Use UNIQUE sparingly.  Use
regular keys when you want to speedup the lookup of that field.

> CREATE TABLE user(
>           id INTEGER(10) NOT NULL AUTO_INCREMENT,
>           firstName VARCHAR(32),
>           lastName VARCHAR(32)
>           PRIMARY KEY(id),
>           UNIQUE(firstName,lastName)
> )
> (or something like that)

This is better, but still wrong.  This says:
"I'll assign every user a unique id, and I'll reference them by that.
(good)   I'll make looking up by first name really fast, but deny the
possibility that two people have the same first and last name.
(wrong)"

Use PRIMARY KEY for fast lookup, and the absolute reference to the
record.  Use KEY, to speed up lookup on that field, use UNIQUE to
speed up lookup and also to prevent duplication.

below is a sample:

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(24) NOT NULL DEFAULT '',
  `lname` varchar(24) NOT NULL DEFAULT '',
  `username` varchar(36) NOT NULL DEFAULT '',
  `pass` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  KEY `lname` (`lname`)
) ;

This says:
"Assign each user an id, and use that to reference each user.  Make
lookups on last name, email, and username, very fast. Don't allow
duplicate usernames or emails."


Regards,
John Campbell