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