NYCPHP Meetup

[Dan Cech]

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