arpowers Posted October 19, 2007 Share Posted October 19, 2007 Hi! I'm trying to create a messaging system for a project I'm working on. Theoretically I only need one table to do this, but I do need a unique auto increment primary key 'id' and a thread_id column. The thread_id column will contain a unique value for new messages, and will contain referential values for thread replies (children).. What is the best way of accomplishing this or should I just create a thread table with just one column? Thanks! Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted October 19, 2007 Share Posted October 19, 2007 when I did this I used three fields to track messages. A unique ID A root ID A parent ID unique is self explanatory.. Root was either 0 for a starting thread or the unique id of the starting thread.. Parent the id of the message the current is replying to. This allowed listing of post (SELECT... where root = 0) selecting all post in one thread (SELECT...where root = 'x' OR unique = 'x' ORDER BY root ASC) I then placed this data into an array which had a recursive loop to list the nodes... JOB Done Quote Link to comment Share on other sites More sharing options...
arpowers Posted October 19, 2007 Author Share Posted October 19, 2007 Fantastic solution Toon~ ! I think you may have solved my problem... So to set the root you: set root to default to 0 and set root to the unique id of the true root for replies? I didn't think of the parent_id thing, i was planning on sorting by the timestamp.. is there a problem with that approach? thanks again! Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted October 21, 2007 Share Posted October 21, 2007 not really apart from th fact it would require more diskspace to save the data Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.