Search Postgresql Archives

Re: In need of help with message fetching query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hey, thanks for your quick response

On Wed, Feb 11, 2009 at 12:00 AM, Sam Mason <sam@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux