tedd wrote: > At 12:43 PM -0400 10/6/09, Dan Cech wrote: >> Kristina Anderson wrote: >>> Exactly, the table would contain probably nothing more than a primary >>> key autonumber field, the user id and account id in each row. >> >> Why do you need a separate primary key? The relationship is defined by >> user id and account id. >> >> CREATE TABLE some_rel ( >> user_id int(11) not null, >> account_id int(11) not null, >> some_attribute varchar(255), >> PRIMARY KEY (user_id,account_id) >> ); >> >> I added the attribute column to illustrate that you can still identify >> the row like: >> >> SELECT some_attribute FROM some_rel WHERE user_id=X AND account_id=Y >> >> HTH, >> >> Dan > > Dan: > > That's a good point, but I still like having a "separate from data" > auto_increment index for all records. Besides adding one field to a > record, can you see any harm in that? > > Cheers, > > tedd
You also end up maintaining separate indexes to support the lookups you actually want to do: Say you want to be able to do lookups in both directions, like: SELECT account_id FROM some_rel WHERE user_id=X and SELECT user_id FROM some_rel WHERE account_id=Y The most efficient system for doing this is: CREATE TABLE some_rel ( user_id int(11) not null, account_id int(11) not null, PRIMARY KEY (user_id,account_id), KEY account_id (account_id) ); The primary key is used for lookups by user_id, and the second key for lookups via account_id. If you add an auto_increment column, you then have to do: CREATE TABLE some_rel ( some_rel_id int(11) not null auto_increment, user_id int(11) not null, account_id int(11) not null, PRIMARY KEY (some_rel_id), UNIQUE KEY user_id (user_id,account_id), KEY account_id (account_id) ); You need a unique index on user_id,account_id to enforce the relationship rule. The account_id lookup cannot leverage the primary key, so you either have to add the user_id field to that index or accept that it's going to have to read the primary key and look up the user_ids from the table itself. Dan _______________________________________________ New York PHP Users Group Community Talk Mailing List http://lists.nyphp.org/mailman/listinfo/talk http://www.nyphp.org/Show-Participation
