NYCPHP Meetup

[William Klein]

I would us the auto increment field as the primary.
Then index the name fields.

The primary would then be used to associate records in other tables to the
name table.

-----Original Message-----
From: John Campbell [mailto:jcampbell1 at gmail.com] 
Sent: Saturday, December 11, 2010 11:20 AM
To: NYPHP Talk
Subject: Re: [nycphp-talk] Doctrine and auto_increment not being a primary
key

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
_______________________________________________
New York PHP Users Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/Show-Participation