NYCPHP Meetup

[Mitch Pirtle]

++ on this approach, took a similar route on a very high traffic site
and that was a major bonus.

-- Mitch

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 Thu, Jul 30, 2009 at 4:45 PM, Glenn Powell <glenn310b at 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
>