NYCPHP Meetup

[Dan Cech]

On 12/11/2010 8:24 AM, jean-baptiste verrey wrote:
> Hi everyone,
>
> I started using Doctrine some days ago and realized that the way I was using
> tables was simply not possible ...
>
> I have always use an integer as auto_increment and do a primary key composed
> by multiple fields such as (with MySQL)
>
> CREATE TABLE user(
>       id INTEGER(10) not null auto_increment,
>       firstName VARCHAR(32),
>       lastName VARCHAR(32),
>       KEY(id),
>       PRIMARY KEY(firstName,lastName)
> )

You need to read: http://en.wikipedia.org/wiki/Surrogate_key

> and ... I could not replicate that with Doctrine, which would ask me to have
> the id as a primary key ...
> So I went to Doctrine IRC for a little chat, and ... some guy told me that I
> was completely misunderstanding how a RDBMS worked ...

And he's right, in this case you can't ever have 2 people with the same 
name, which is going to be a real problem for all the John Smiths out there!

> I am no expert in SQL but ... this is how all the tables are done in
> OSCommerce...

There may be some reason dictated by the specific requirements of 
OSCommerce, or the designer may just not know what they're doing.

> So my questions are :
> 1- Am I really not using SQL tables as It should be?

In the majority of cases surrogate keys of some kind make sense, the 
fact that you feel the need for a separate id column anyway should be a 
big indicator that this is the case.

> 2- Otherwise, how do you replicate that in Doctrine ?
>
> Regards,
>
> Jean-Baptiste
>
> ps:
> I always thought that this was the best way for the following reason :
> - the id is just an integer, so better performance for joins, and searching
> for a known user

In an ideal world the join column will be the primary key, this will 
yield the best performance because every other index just maps the 
indexed column to the primary key.

> - the primary key is used to prevent duplicates and when searching

Improving lookups is the purpose of regular indexes, if you want to 
prevent duplicates then use a UNIQUE index, but as I mentioned above 
this is probably not a good idea in your case.

Dan