On Sat, Dec 11, 2010 at 11:20 PM, jean-baptiste verrey <jeanbaptiste.ver...@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
