Re: In need of help with message fetching query

Hey, thanks for your quick response

On Wed, Feb 11, 2009 at 12:00 AM, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:
> 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?

You make an interesting point.

So, whenever a row is inserted into messages, I would want to do
something like `update or insert into heads for id =
coalesce(replied_to, own_id), set last_reply = own_timestamp.`
Correct?

Though I might've been crap at explaining it, I think there's an
essential part missing here: a segment of the tree could be up for
fetch because it has a reply from a certain account. That's why I
chose to denormalize reply_to_account_id.

So... to get an equivalent non-NF table, I would want one row in heads
per reply? :/ And update each row with last_reply. Seems to me that is
getting unwieldy pretty fast.

Hrm...

>
> --
> Sam http://samason.me.uk/
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>