Re: Bidirectional Binary Self-Joins

On Mar 29, 2:15 am, mlef...@xxxxxxxxx wrote:
Hi-
I've been banging my head over how to represent this theoretical
example: in a group of people, trying to keep track of who is friends
with whom. I'm assuming that if person A is friends with person B,
then the reverse is also true. However if B is friends with C, then A
is not necessarily friends with C. What is the best way to represent
this to ensure best integrity and at the same time make the structure
simple and efficient to query. Here is my best go at is:

TABLE 1 - people (PrimaryKey: person_id)
person_id
person_name

TABLE 2 - friendships (PrimaryKey: friendship_id, pair_id, secondary
unique key: friendship_id, person_id )
friendship_id ## this id will start at 1 and increment for each
'pair' of associated recs added (so will be the same number for two
records)
pair_id ## restricted value: 1, 2--> purpose is to only
allow two values to be stored for each 'friendship pair'. Keeps
database structure intact.
person_id ## id of one person in a pair of friends

Friendship is symmetric but not transitive binary relation. You have 2
options to represent it:
1. "Normalized form". Only one tuple per each relationship. E.g.
<person1=A, person2=B> means that A is friend with B and B is friend
with A. You can enforce relationship uniqueness by tuple constraint:

person1 < personB

2. "Symmetrically closed" relation. Two tuples per each relationship.
E.g. <person1=A, person2=B> means that A is friend with B, and
<person1=B, person2=A> means that B is friend with A. You can enforce
symmetric closure with complex constraint.



.

Relevant Pages

  • Re: Bidirectional Binary Self-Joins
    ... I'm assuming that if person A is friends with person B, ... with A. You can enforce relationship uniqueness by tuple constraint: ... using person1, person2 tuples since I thought this would introduce ...
    (comp.databases.theory)
  • Re: Bidirectional Binary Self-Joins
    ... I'm assuming that if person A is friends with person B, ... with A. You can enforce relationship uniqueness by tuple constraint: ... using person1, person2 tuples since I thought this would introduce ...
    (comp.databases.theory)