From: Stefan Weiss <spaceman@foo.at>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: Links between rows in a table
Date: Sun, 06 Mar 2005 19:27:38 +0100
Message-ID: <422B4B9A.8020606@foo.at> (raw)
In-Reply-To: <20050306174201.GA19019@wolff.to>
References: <422A8A26.1070704@foo.at>
<20050306174201.GA19019@wolff.to>
On 2005-03-06 18:42, Bruno Wolff III wrote:
>> We are currently designing a web-based application in which users can
>> add other users as "friends". These links are bi-directional, meaning
>> that when A adds B to his friends, he is automatically one of B's
>> friends. Eventually we will have to add a feature that shows how A is
>
> This doesn't seem like a good idea unless the person getting linked to
> gets to confirm he wants the link creator as a friend.
Yes, we have an invitation/pending/confirm process, and users are also
able to block other users. I haven't mentioned this because I did not
think it relevant to the storage question. There is a different system
for unilateral friendships ("favorites/fans").
>> SELECT friend_id FROM friends WHERE user_id = X
>> UNION SELECT user_id FROM friends WHERE friend_id = X;
>
> It would probably be better to always have either both or neither of
> the symmetric relationships in the table. You could make a set of triggers
> to enforce this.
We have also considered this, but since "friendship" in this application
is mutual by definition, wouldn't that just lead to data duplication? We
might still insert two rows instead of one, if we find that the union
slows things down more than the larger table, or if the "connection
finder" feature will be easier to implement that way.
By the way, according to the MySQL documentation, "Rudimentary support
for triggers is included beginning with MySQL 5.0.2". The MySQL
compatibility requirement is none of my doing, I have given up trying to
educate my customers about the benefits of a real database...
regards,
stefan weiss
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spaceman@foo.at, bruno@wolff.to
Subject: Re: Links between rows in a table
In-Reply-To: <422B4B9A.8020606@foo.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox