On Thursday 02 Oct 2003 09:13 in
<404a8308.0310020013.5294255@posting.google.com>, Chris
(mvppetlab@yahoo.com) wrote:
> Suppose you want to use an RDBMS to store messages for a threaded
> message forum like usenet and then display the messages. A toy table
> definition (that I've tried to make standards compliant) might look
> like:
>
> create table messages (
> message_id integer,
> in_reply_to integer,
> created date,
> author varchar(20),
> title varchar(30),
> message varchar(256),
> primary key (message_id)
> );
>
>
> The in_reply_to field, if not null, means that the message is a reply
> to the message with the message_id it has stored. Suppose now that we
> populate the database with a 5 message discussion.
You will need a second table, called a path enumeration table. Joe Celko
wrote up this technique in his book "SQL For Smarties".
I think I can dig up some sample SQL for you, as I used this technique
several times a few years ago -- although on DB2 rather than PostrgeSQL.
Since the SQL can be a bit intricate, I have set follow-ups to
comp.databases.postgresql.sql, as it would be more on-topic there.
However, I recommend Joe Celko's book, as it explains the technique as well
as demonstrates it.
--
Regards,
Dave [RLU#314465]
======================================================
dwnoon@spamtrap.ntlworld.com (David W Noon)
Remove spam trap to reply via e-mail.
======================================================
