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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
