Re: Bidirectional Binary Self-Joins
From: mAsterdam <mAsterdam_at_vrijdag.org>
Date: Fri, 30 Mar 2007 13:49:40 +0200
Message-ID: <460cf8ca$0$327$e4fe514c_at_news.xs4all.nl>
>> 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
>
> What if Table 2 was just two attributes of type person_id?
Date: Fri, 30 Mar 2007 13:49:40 +0200
Message-ID: <460cf8ca$0$327$e4fe514c_at_news.xs4all.nl>
Marshall wrote:
> mlef 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
>
> What if Table 2 was just two attributes of type person_id?
What would be their names? - or should they be anonymous? Received on Fri Mar 30 2007 - 13:49:40 CEST