Messaging System: Save and Send - when to do the INSERT of a new message?
In a private messaging system built with PHP and MySQL users can compose new messages and answer to messages. There are two buttons: "SEND" and "SAVE". The message status is stored in the database (0=not yet sent, 1=sent). Also, the message is being saved automatically every e.g. 3 minutes via an ajax-call.
Sent messages as well as drafts are stored in the same table in the database. Now, when would you do the INSERT INTO messages ...?
If the user clicks on compose new message or answer? How to clean out all the never sent messages, if the user does not send the message, closes the window, presses the back-button?
What other options would you suggest?
Aren't the automatically saved messages visible as a draft version? Then they will clean up most of them by themselves
As requested an answer ;)
I would insert a new message with status 'pending' when the user clicks 'Compose message'. You can delete all 'pending' messages that have been inactive for a day, for example.
When a user has been busy with that particular message for, say, 1 minute, you save the message as 'concept' and is available for the user to review later if he leaves the page. I would never delete concepts and let the user do this himself.
When the user clicks 'save' (not 'send') you give the message the 'concept' status as well (if it didn't have that before).
Once the message is sent, you give it the 'sent' status. But there's something you wanna take into consideration before developing the message system this way:
Ask yourself the question: what is a message? Does a message have a status of itself? And to whom does that status apply? A read message can have the status 'read', but what if you send the message to multiple recipients? Do you change the status when all recipients read it? Not very convenient.
I suggest you change your database model slightly, to enable multiple recipients. You save the title, body, etc. in one record, and add new records (in a different table) for each recipient holding the message's status. There's one for the sender and one for each recipient. This way you can manipulate the status of the message for each recipient individually, and you could calculate the overall message status by looking at all these records. (5 out of 10 recipients have read your message).