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