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
_______________________________________________
New York PHP User Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org/show_participation.php
