NYCPHP Meetup

[tedd]

At 3:02 PM -0500 7/30/09, 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   |
>+-----------+-----------+
>

Allen:

All the above tells you is that 100 has a 200 for a friend, which is 
exactly what I would want.

I would solve the problem by simply creating an unique record when 
anyone is identified as a friend of another, but only in a one-way 
relationship. In the above example, you can see that 100 has 200 as a 
friend and 300 has 100 as a friend, but you cannot conclude that 100 
has 300 as a friend.

If 100 agreed that 300 is a friend, then your table would look like this:

+-----------+-----------+
| contact1 | contact2 |
+-----------+-----------+
|      100    |      200   |
|      300    |      100   |
|      100    |      300   |
+-----------+-----------+

So, I would make it one record per one-way relationship to solve this 
problem. That way you don't have to search two columns to find 
one-way relationships and you keep your records short and neat.

Plus, this makes it easy if a relationship is not shared or later 
dissolved -- a single record is either not created or deleted, 
respectfully.

Additionally, I might change the column titles to "person" and 
"friend" to be more semantic (and add an index).

Cheers,

tedd

-- 
-------
http://sperling.com  http://ancientstones.com  http://earthstones.com