Personally I would design my tables like this -

Table: Messages
message_idrecipient_idsender_idmessagedatestatus
1500501hey mike2003-11-02NR

Primary key: message_id
Foreign keys: recipient_id, sender_id


Table: User
idnicknamefullname
500bobBob
501janeJane

Primary key: id

So when user bob logs in, the SQL to check for new/unread messages would be like this -
SELECT *
FROM messages
WHERE recipient_id=? AND status='NR'

[download]
After Bob has seen the message (assuming that the message id is saved some where on your web page (hidden field perhaps, or a session variable) -
UPDATE messages
SET status='OK'
WHERE message_id = ?

[download]
And when Bob replies a message back to Jane -
$msg = "original message"; # assume this is from the prev message

$reply = $newmsg . "\nOriginal Message\n------------\n" .
         $msg;

# and then just insert the reply into messages table with SQL insert

INSERT INTO messages (message_id, recipient_id, sender_id,
                      message, date, status)
VALUES (?, ?, ?, ?, ?, 'NR')

[download]
And for the sequencing of message_id's, I normally use a database trigger on INSERT, that queries the table searching for the maximum message_id, add 1, and set as the new message_id. If you don't want to use triggers you could certainly do this easily with SQL and a bit of Perl.

Ok, what could be tricky is when multiple users are trying to send messages to others at the same time. For that you need to implement proper locking mechanism if you don't use DB triggers, and that's one occation where I think DB triggers are quite useful. And you certainly would need to handle insert exceptions and try again if necessary.

In reply to Re: Message Reply Sequencing by Roger
in thread Message Reply Sequencing by perleager

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.