Re: Bidirectional Binary Self-Joins

On Mar 29, 6:05 pm, "Aloha Kakuikanu" <aloha.kakuik...@xxxxxxxxx>
wrote:
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.

I actually think this raises an issue which is often glossed over -
the situation where there is a relationship with no clear antecedent
involved (which obviously tend to be symmetric). Within a friendship I
have two equally valid components - yet I have to distinguish an
antecedent and a consequent for an "is friends with" predicate. I
could enter two tuples to indicate the symmetry of this relationship,
but should that be necessary? Would it not be possible within the
constraints of good data modelling to have a relationship (as codd
defined it and not as per the scurrulous definitions of E/R) that has
two attributes both of which are "friends" (yes repetition of
attribute names). And if so how would this impact on our manipulation
of such a structure - negatively or beneficially?

Apologies in advance for wanton mulling off the top of my head.

.

Relevant Pages

  • Re: Describe The Disability
    ... I have a Giant Aneurysm in my head and my doctors tell me it's causing ... home and take care of me full-time so my husband can work. ... can't talk to people because I start crying. ... >> only other friends causes a scene, ...
    (alt.med.fibromyalgia)
  • How do others see you?
    ... less fast head up, looking the world in the face ... vary between these two extremes ... someone who's extremely loyal to friends you do make and who expect the same ... cautious, extremely careful, a slow and steady plodder. ...
    (uk.people.silversurfers)
  • Re: How do others see you?
    ... less fast head up, looking the world in the face ... vary between these two extremes ... but someone who's extremely loyal to friends you do make and who ... very cautious, extremely careful, a slow and steady plodder. ...
    (uk.people.silversurfers)
  • looking for book: takes place late 50s early 60s and involves fantasy
    ... same village that the British boy's mother lived in. ... The two mothers expect the boys to become fast ... friends because THEY had been. ... The Uncle sends a tiger's head (real tiger's head ...
    (rec.arts.books.childrens)
  • Re: It is only funny until someone loses an eye. Trip back from Expo
    ... Absolutely Del. ... the top of the head with a golf ball at full speed. ... Both were friends of my Son's & I had a long talk with him ... One of the cardinal rules when dealing with a person that you suspect is considering suicide is to ask them right out "Are you thinking of suicide?". ...
    (rec.games.pinball)