Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Bidirectional Binary Self-Joins

Bidirectional Binary Self-Joins

From: <mlefkon_at_yahoo.com>
Date: 29 Mar 2007 02:15:14 -0700
Message-ID: <1175159714.015259.222600@b75g2000hsg.googlegroups.com>


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 Received on Thu Mar 29 2007 - 04:15:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US