[reformatted]

Mitch Pirtle wrote:
On Thu, Jul 30, 2009 at 5:07 PM, Elijah Insua<tmp...@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)

_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk

http://www.nyphp.org/show_participation.php

Reply via email to