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
