On Thursday 02 Oct 2003 09:13 in <[EMAIL PROTECTED]>, Chris ([EMAIL PROTECTED]) 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] ====================================================== [EMAIL PROTECTED] (David W Noon) Remove spam trap to reply via e-mail. ====================================================== ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
