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 or analyzing 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 were marginally faster, but the sql was increasingly more complex and slower to execute as we leveraged the table into other queries.