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/
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
