NYCPHP Meetup

[Allen Shaw]

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)