Ben Sgro (ProjectSkyLine) wrote: > Hello all, > > After the XML vs SQL conversation, I started to rethink a product I'm working > on. > I had been having a lot of trouble modeling the data into SQL. It was > starting to > look very complicated. > > So, I decided I would try to store it in XML. The data I am storing is > messages w/in a thread, > timestamp, body and subject. On the SQL side, we create a table that has the > user id, > and a row per message/thread which contains a path to the xml file. > > The reason XML seemed better is that I store the data top down and that is > the true > order of the thread. Messages within the thread can come from email or html > page response, > so its important to insert items into the thread via the timestamp. Using XML > just made it > simpler for me to wrap my head around, than trying to model it in SQL.
This sounds awfully messy to me, and will most likely be very slow. I'm all for XML, but this kind of thing is what databases are meant for. All you need are 2 tables, one for each thread and one for messages. threads: thread_id,subject,ts,activity messages: msg_id,thread_id,user_id,ts,subject,body SQL: SELECT * FROM threads ORDER BY activity DESC SELECT * FROM messages WHERE thread_id=? ORDER BY ts ASC Couldn't be easier, if you want to store extra data about each thread or message just add fields to the relevant table. I just wrapped up a complete rewrite of a system that used one table for threads and messages, and I would strongly suggest advise against it. If you're looking for ideas, here is a proof-of-concept I put together back in 2004 for a system using a modified preorder traversal tree for threaded messages. http://clew.phpwerx.net/ Dan _______________________________________________ New York PHP Community Talk Mailing List http://lists.nyphp.org/mailman/listinfo/talk NYPHPCon 2006 Presentations Online http://www.nyphpcon.com Show Your Participation in New York PHP http://www.nyphp.org/show_participation.php
