NYCPHP Meetup

[Dan Cech]

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