I'd go with option #2, because it allows a "two-way" friendship, similar to how LiveJournal does it. Or, you could run a logic hook and check that both relationships exists, or the friendship is "pending." Option #1 means that you'd have to query the table twice (or the RDBMS would) to get a friends list (whether you ran a single query with an "OR" statement or two queries, the table is going to have to be opened twice) and is less flexible.
And assuming that you had a table called people with a column names, just to get "your" friends out, with option 1: SELECT name FROM people,friends WHERE ( people.ID = friends.ID1 AND friends.ID2 = $yourID ) OR ( people.ID = friends.ID2 AND friends.ID1 = $yourID ) As opposed to option #2: SELECT name FROM people, friends WHERE people.ID = friends.ID2 AND friends.ID1 = $yourID Now, expand this to a query with more than two tables and your looking at a lot more work. On the other hand, Option #2 is inherently twice as much data, so I guess it really depends on what you're looking for. I'd definitely err on the side of option #2 because (IMO) it's more flexible and would almost definitely be faster to query against, at least until you had a mountain of data. On Thu, Jul 30, 2009 at 4:18 PM, Brian O'Connor<gatzby...@gmail.com> wrote: > I did this a few years ago and I believe I went with option 1 (disclaimer: > most what I did back then would be shunned by even the most beginner of > programmers). > > #2 definitely has its advantages but goes against database normalization > theory (in my opinion), especially since there is inherently no "direction" > to friendship. > > #1 can easily be sorted out using a few lines of php but is definitely > uglier. > > Of course, if you really wanted to go table-happy you could make even the > notion of friendship based on joins :) > On Thu, Jul 30, 2009 at 4:02 PM, Allen Shaw <as...@polymerdb.org> 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 > > > > -- > Brian O'Connor > > _______________________________________________ > 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