Re: In need of help with message fetching query

On Tue, Feb 10, 2009 at 11:19:13PM +0100, Max Strrrmberg wrote:
> [...] messages are to be ordered in a very standard fashion of single-depth
> threads. That is, a message can be a reply, and a so-called "head".
>
> create table messages (
>   id serial not null primary key,
>   author_id integer null references account,
>   text varchar(200) not null,
>   timestamp timestamp not null,
>   reply_to_id integer null,
>   reply_to_account_id integer not null references account,
>     unique (account_id, reply_to_id)
> );

I'd almost advocate having a having another table, and thus breaking
various normalization rules, to make your life easier:

  CREATE TABLE heads (
    head_message_id INTEGER PRIMARY KEY REFERENCES messages,
    last_reply  TIMESTAMP NOT NULL
  );

You can have a trigger to maintain this table, and maybe even one
to insert things as well.  Not sure about the other rules you want,
but if a "thread" can only involve two people (obviously for email
conversions this isn't true) then you could move the author_id and
reply_to_account_id into this table as well.  If you did this you could
move other attributes around and get things normalized again.

Does that help with ideas?

--
  Sam  http://samason.me.uk/