RE: [Phpgroupware-developers] A question to the SQL gurus

[Kai Hofmann]

> I want to add threading to Felamimail. And i don't want to be 
> dependent on
> the imap server for that feature.
> 
> So i want to do it my self. 
> 
> What is the best solution, for doing this in sql??
> 
> What i need:
> 
> - a unique id for any thread(display only this thread)
> - easy way to find the next and previous message of a message 
> in a thread
> - fast search and insert
> 
> 
> Any ideas, how to solve this?


A self referencing table would do it.
Every message has a parent - so adding the parent id for each entry.
The previous msg could be found via the parent id - for the next message
do a select on the parent column searching for the id of the actual msg.
(one message can have more than one child so it must be done in this way).

Insert is no problem here - only use the correct parent id for the new msg.

Search would also work.

When using a large message base a cache system would be a good idea.
So in an explorer like structure I would only get those without an parent
(better use 0 than null)
and cache them. By clicking a + for the next level below a message - do the
next select and cache the result ... etc.


Greetings

   Kai