[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