NYCPHP Meetup

[Allen Shaw]

[reformatted]

Mitch Pirtle wrote:
> On Thu, Jul 30, 2009 at 5:07 PM, Elijah Insua<tmpvar at gmail.com> wrote:
>   
>> This is how I would approach the problem (simplified/pseudocode)
>>
>> CREATE TABLE user (
>>     id INT,
>>     username VARCHAR
>> );
>>
>> CREATE TABLE friend (
>>     id INT,
>>     user_id INT,
>>     friend_id INT
>> );
>>
>> -- Get all of user #1's friends usernames
>> SELECT u.username FROM users u, friends f WHERE f.user_id = 1 AND u.id =
>> f.friend_id;
>>
>> -- Get all users who think #1 is their friend
>> SELECT u.username FROM users u, friends f WHERE f.user_id = u.id AND
>> f.friend_id = 1;
>>
>> with a clean/consistent naming scheme and  _not_ duplicating data you will
>> be set for faster
>> development, and not to mention the benefits down the road when you may have
>> to actually debug
>> this (possibly many months later).
>>
>> If speed becomes a problem there are some measures you can take:
>> 1) make sure you create proper indexes
>> 2) caching
>>
>> -- Elijah
>>
>>     
> ++ on this approach, took a similar route on a very high traffic site
> and that was a major bonus.
>
> -- Mitch
>   
To be more clear, in this app, friend relationships are reciprocal by 
definition -- the CRM user is managing his own business contacts and 
marking them as connected to each other for his own reference, which is 
different from a social networking user marking his own friends among 
other users.

These two comments from Elijah's example highlight the difference:
> -- Get all of user #1's friends usernames
> ...
> -- Get all users who think #1 is their friend
To say "user #1" and "users" here is incorrect.  The "friends" in this 
app are not users -- they're business contacts of the user who 
themselves have no access to the app.  In this app, if the user says 
they're friends, then by definition they're both friends to each other.

Since friend relationships are always reciprocal in this app, if we use 
Elijah's suggestion, we have to perform a union query (or two separate 
queries) every time we want to find friends of contact #1. 

I'm thinking the multi-table joins performed twice for each search will 
make more of a hit in performance (and maintenance) than writing two 
rows for each relationship and then always being able to search only once.

- Allen

-- 
Allen Shaw	
slidePresenter (http://slides.sourceforge.net)