Messaging center.

[FrankBro]

I'm having a bit of a dillema here ..
I'm doing my message center but wondering if i'm doing ti the right way.
Right now, the table look like this:

messageid userid senderid (int)
title (varchar 50)
message (text)
replyid (int)

I'm thinking about loading the message it was replied from by the replyid when reading a message or writing a new one through a recursive function.
The thing is, it could easily be exploited and someone could just keep replying his message, which would cause a lot of useless queries.
My though was to limit the function that limit the number of message read through the replyid by a specific number but i don't really like the idea of limiting users like that.

How do you guys do your message center ?

[Marek]

Although optimizing is important, don't worry too much about the specific case of someone trying to abuse it. If someone wants to slow down your server, they will use a [D]DOS attack (which is much easier than trying to find an exploit), in which case the queries in your message center will not matter anyway.

Anyway, you could take a "threaded" approach: the first message of a reply-chain becomes the "replyid" reference for all subsequent replies. Then the replies are fetched in a single query which orders them by date. This is the approach that forum threads use, as well as GMail conversations. However, in order to list them in the inbox, you will need a join query that orders the thread by the time of the last reply.

[FrankBro]

when you mean reply chain, do you mean like i said? ex:
messageid userid senderid title message replyid
1              1         2        test     test      0
2              2         1        RE:test test      1

or more like replyid being "1,2,4,7" ?
because if i only have 1 reply idea, id have to fetch the rows 1 at a time.