Creating Facebook style messages

[zander1983]

Hi

Im created a market place website, sort of like eBay. I want to allow the shop owners to be able to message the user who has purchased an item from them and vice-versa. I want it to be like Facebook where a user sends a message, and if they reply, it becomes a thread which they reply to. But if a user send a message thats not in reply to a thread, a new thread is formed

 

What I'm looking for is the correct model to use. I set up these 2 table:

 

Message table

 

MessageID -> primary key

FromID -> id of sender

ToID -> id of receiver

Message -> the text of the messsage

Date -> date message was sent

 

Thread table

 

ThreadID -> ID linking messages together that aer in the same thread

MessageID -> foreign key to Message table

 

I want to know, am I on the right track with this? Its loooking like I'll need to use a cursor to get back the Username of the FromID person and ToID person in the same row. i dont like cursors!

 

Any tips on what i should do or does anybosy have links to a site with a tutorial on building a system like this?

 

Thanks in advance

[nethnet]

You could add a `replyto` field in your first table, and get rid of the second table altogether.  Just include the `id` of the first message in the `replyto` field if it's a direct reply, otherwise have it set to 0.

 

When you select your messages from the database, do a double ORDER BY.. first on the field `replyto` and second on the field `date`.  Any rows with a `replyto` value of 0 will be standalone messages (or the first message of a thread), and all others will be replies.

 

Then if a user is viewing a message with `id` of 45, any messages with `replyto` of value 45 should be printed as well in a thread style.

[zander1983]

good idea, ill use a replyto field.

 

i presume i need to use a cursor to get a row with:

 

- the message

- the date

- username of sender

- username of receiver

 

i need this all in one row. Message table joined to Member table, which contains usernames. i alwys try to avoid cursors, so complex. but seems unavoidable here..