My problem is to create normal and fast-processing private messages system. Maybe someone knows some articles about implementation (database structure, algorithms). Because I know only several types: 1. ICQ style. Messages stored on client and only new ones are received. A lot of interactions on new unknown sender (not in list) - send message to client, client asks "WHOIS 282728", server replies "ITS JAMES BOND SR." And another problem is no portability - move to another PC so move message logs by yourself or forget about message history. 2. Messages must be stored in style SENDER_ID, SENDER_NAME, RECEIVER_ID, RECEIVER_NAME, TEXT to get easily "SELECT * FROM MESSAGES WHERE SENDER_ID=... OR RECEIVER=..." But too much data is stored. And using JOIN (to get nicks immediately) wastes a lot of time. 3. HTML-style. Want to see messages? Go to inbox... loading inbox page 1. Want to see message... loading message. Want to see page 2... loading page 2.

Getting messages for user (incomming and outgoing) by ids and then getting nicks from another table means slow. So if there's a way to get 100 sent/received messages less than 500 ms means fast - for example.

Messaging engine must be simple (no clusters), just a MySQL server to store messages in some tables, interface by some language (now java). Means that one player sends a message to server, server sends to another player. But all other problems are not solved:
1. How to manage moving from one computer to another? Because in my engine it can be every day issue. You went to friend's home computer and get a offline message "So yeah? Or it's a mistake?" What did you discuss at your home computer?
2. Try to decrease needless data (store sender nick, sender id, receiver nick, id).
3. If minimize needless data then if client receives message, it would be "Sender: 2817281723". So another request is needed "WHOIS 2817281723".

So I need optimal solution. Maybe just popular solution - for example you say "WoW uses this! LineAge uses this! Perfect World uses this! So everybody uses it, so I advise you to use it."

Take a look at ejabberd.

If (and only if) that doesn't serve your needs, start worrying about custom solutions. Which, by the way, can probably be an in-core system with disk backup, instead of going straight to a database, if you worry about performance.

Does it save outgoing messages? Can I get the same messages when moving from my computer to another (go to work and see dialogs that were at home). Because I don't need the server itself - I need a solution to implement it. And downloading ejabberd, walt thru code is hard instead of your quick description...

ejabberd is IM (ICQ style) and presence, not persistent messaging (mail like).

If all you want is persistent messages that display until deleted, putting them all into a database and running a query (with proper user authentication) is all you need. Postgres, MySQL, DB/2, SQL Server or Oracle can all do this.

Scaling databases is simple -- either just buy a bigger machine, which works surprisingly well, or start federating data (messages for users with user id X go to a database named "messages-%d" for %d == X mod (num nodes)).

The join for nicks isn't a problem -- either use the nick as the user id, or put the nick table in a hash table, or just pay for the join. The nick table will likely live all in RAM anyway, because it will be frequently accessed.

Or use IMAP :-)