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 Thu, Jul 30, 2009 at 4:45 PM, Glenn Powell <glenn3...@mac.com> wrote:
> imho,
>
> For option 1, if looking for friends of 100, maybe you only have to look in
> col 1.
>
> if 100 considers 300 a friend, that does not mean that 300 considers 100 a
> friend.
>
> It could be maddening for a user to see that they are a friend of 300, when
> in fact they have not done anything in the system
> to create that relationship.
>
> Looking for friends of 100 should probably mean looking for friends that
> 100 listed as friends?
>
> best,
>
> glenn
>
>
> On Jul 30, 2009, at 4:02 PM, Allen Shaw wrote:
>
> Hi Gang,
>>
>> To support "friend" relationships in a contacts management database, I'm
>> expecting to use a simple table like this one (plus a few columns for unique
>> id, etc.):
>>
>> CREATE TABLE `tcontactrelationships` (
>> `contact1` int(11),
>> `contact2` int(11)
>> );
>>
>> My concern is that when I'm looking for friends of contact #100, I'll have
>> to search for '100' in both columns, because there's no reason that the
>> value should be in only one column or the other. For example:
>>
>> +-----------+-----------+
>> | contact1 | contact2 |
>> +-----------+-----------+
>> | 100 | 200 |
>> | 300 | 100 |
>> +-----------+-----------+
>>
>> Alternatively, I could create two rows for each relationship, so that I
>> can always search only one column to find all relationships, e.g.,
>>
>> +-----------+-----------+
>> | contact1 | contact2 |
>> +-----------+-----------+
>> | 100 | 200 |
>> | 200 | 100 |
>> | 300 | 100 |
>> | 100 | 300 |
>> +-----------+-----------+
>>
>> The second method seems better, smoother, less complicated when it comes
>> time to pull out data from this table with joins to multiple other tables.
>> Of course I should only be adding/removing relationships within a
>> transaction. Can anyone suggest drawbacks or caveats I may be missing, or
>> maybe suggest a better way?
>>
>> Thanks,
>> 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
>>
>
> _______________________________________________
> New York PHP User Group Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
>
> http://www.nyphp.org/show_participation.php
>
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org/show_participation.php
