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/