noSQL implementation: threaded messaging system

I'm trying to build a threaded messaging system for my website. Basically the system provides following features开发者_StackOverflow中文版 :

• allow users to send messages between each other
• arbitrary levels of replies (threads)

I already tried using mysql+php and has built a skeleton. However in the whole thread retrieving part is kinda recursive, which I believe is not the best thing a relational schema can do. So now I'm researching for a non-SQL implementation. Hopefully to avoid such problem making the data retrieval more natural.

Anybody has such experience give me a hint please.

Update: My client app is written in PHP and will probably remain so.

---

A document database, or object database, will accomplish what you're looking for quite well. There are ways that you can also do this with an RDBMS that will work just as well. An example schema is:

CREATE TABLE messages (
  message_id INT,
  original_message_id INT,
  parent_message_id INT,
  message VARCHAR(4000)
);

You can then perform selects from this table like

SELECT *
FROM messages
WHERE message_id = ? OR original_message_id = ?
ORDER BY parent_message_id;

This would probably solve your problem. The advantage to doing this with an OODB or document database is that you'll be able to pull back all of the data for a 'conversation' using only one query, since it's all stored together. On the downside, you sacrifice some ad hoc querying flexibility for the convenience of reading all of your data at once.

---

What about using the thread instead of the comment as the main entity? Let's try first with SQL and a two people thread: when the first user send the first message to the second user, you create a new thread, and link this thread with the two users (User has many threads). The message itself you can store it as JSON. Then, each response will be in the same JSON (same varchar or similar field). This solution works well if you don't need to search (or list) individual messages.

Example:

Users:
Id-Name
1-User1
2-User2

UsersByThread:
UId-TId
1-1
2-1

Threads
Id-JSON
1-'{"messages":[{"user":"User1","text":"blablabla"}]}'

Then, when the second user respond, you simple take the json, and add a new message:

Threads
Id-JSON
1-'{"messages":[{"user":"User1","text":"blablabla"},{"user":"User2","text":"blablabla"}]}'

If a third person join the thread, you simply add the relationship in UsersByThread, and add the message in the JSON.

BTW, you can use the same idea in a nosql store like couch, or even in a key-value store like redis. Also you can create a more natural system in neo4j, but I think this could work just great in a regular sql database