NYCPHP Meetup

[Eddie Drapkin]

I'd go with option #2, because it allows a "two-way" friendship,
similar to how LiveJournal does it.  Or, you could run a logic hook
and check that both relationships exists, or the friendship is
"pending."  Option #1 means that you'd have to query the table twice
(or the RDBMS would) to get a friends list (whether you ran a single
query with an "OR" statement or two queries, the table is going to
have to be opened twice) and is less flexible.

And assuming that you had a table called people with a column names,
just to get "your" friends out, with option 1:

SELECT name FROM people,friends
   WHERE
    (
      people.ID = friends.ID1
      AND friends.ID2 = $yourID
    ) OR (
      people.ID = friends.ID2
      AND friends.ID1 = $yourID
    )

As opposed to option #2:

SELECT name FROM people, friends
   WHERE
     people.ID = friends.ID2
     AND friends.ID1 = $yourID

Now, expand this to a query with more than two tables and your looking
at a lot more work.  On the other hand, Option #2 is inherently twice
as much data, so I guess it really depends on what you're looking for.
 I'd definitely err on the side of option #2 because (IMO) it's more
flexible and would almost definitely be faster to query against, at
least until you had a mountain of data.

On Thu, Jul 30, 2009 at 4:18 PM, Brian O'Connor<gatzby3jr at gmail.com> wrote:
> I did this a few years ago and I believe I went with option 1 (disclaimer:
> most what I did back then would be shunned by even the most beginner of
> programmers).
>
> #2 definitely has its advantages but goes against database normalization
> theory (in my opinion), especially since there is inherently no "direction"
> to friendship.
>
> #1 can easily be sorted out using a few lines of php but is definitely
> uglier.
>
> Of course, if you really wanted to go table-happy you could make even the
> notion of friendship based on joins :)
> On Thu, Jul 30, 2009 at 4:02 PM, Allen Shaw <ashaw at polymerdb.org> 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
>
>
>
> --
> Brian O'Connor
>
> _______________________________________________
> New York PHP User Group Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
>
> http://www.nyphp.org/show_participation.php
>