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
