Re: Modeling Friendship Relationships

On Nov 11, 2014, at 5:38 PM, Robert DiFalco wrote:

> Thoughts? Do I just choose one or is there a clear winner? TIA!


I prefer this model

    user_id__a INT NOT NULL REFERENCES user(id),
    user_id__b INT NOT NULL REFERENCES user(id),
    is_reciprocal BOOLEAN
    primary key (user_id__a, user_id__b)

if a relationship is confirmed (or dropped) I toggle is_reciprocal.  having that value saves a lot of work doing joins
oranalyzing friendship sets 

if you have multiple relationship types, then things get tricky.

you can either
    - treat the row as a triplet ( user_id__a, user_id__b, relationship_type_id)   [i still recommend the reciprocal
bool]
    - if you have a finite set of relationship types, you could just use each one as a bool column within the a2b row

I've tried doing the "one row per relationship" approach, and didn't like it.   the time savings on simple searches
weremarginally faster, but the sql was increasingly more complex and slower to execute as we leveraged the table into
otherqueries.