++ on this approach, took a similar route on a very high traffic site and that was a major bonus.
-- Mitch On Thu, Jul 30, 2009 at 5:07 PM, Elijah Insua<tmp...@gmail.com> wrote: > This is how I would approach the problem (simplified/pseudocode) > > CREATE TABLE user ( > id INT, > username VARCHAR > ); > > CREATE TABLE friend ( > id INT, > user_id INT, > friend_id INT > ); > > -- Get all of user #1's friends usernames > SELECT u.username FROM users u, friends f WHERE f.user_id = 1 AND u.id = > f.friend_id; > > -- Get all users who think #1 is their friend > SELECT u.username FROM users u, friends f WHERE f.user_id = u.id AND > f.friend_id = 1; > > with a clean/consistent naming scheme and _not_ duplicating data you will > be set for faster > development, and not to mention the benefits down the road when you may have > to actually debug > this (possibly many months later). > > If speed becomes a problem there are some measures you can take: > 1) make sure you create proper indexes > 2) caching > > -- Elijah > > > On Thu, Jul 30, 2009 at 4:45 PM, Glenn Powell <glenn3...@mac.com> wrote: >> >> 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 > > > _______________________________________________ > 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
