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
