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
