Database game messaging schema

I'm trying to use a database as the back-end for a messaging system in my game (sort of like instant messaging). I am using a local database to store received messages and a database on my server to send them. Here are the tables that I am using:

Users:

userName (varchar)

displayName (varchar)

currentGames (varchar)

Messages:

sender (varchar)

receiver (varchar)

message (varchar)

timestamp (int)

My plan is that when a user sends a message, I first store the message in their local database and then send the message off to the server.

When a user checks to see if there are any new messages (polling), he first gets the latest timestamp from his local database and u开发者_如何学编程ses this time to query the online database for all messages sent after that time. All messages received are then deleted from the database.

Is there something wrong with the way I'm doing this? I'm trying to prepare for the worst, and I have no idea how this sort of plan will scale. I'm not using a unique id for the "Users" table and I feel that I should. Since my database experience is limited I don't fully understand the significance of the unique auto-increment id or how it would help me here. Any advice/criticism would be appreciated.

Since most gamer tags are transient and you probably want to differentiate between a gamer's ID (private) and their user name (public, at least to friends) then you want a local design like this:

FRIEND  -- The local user's own tag should go in here too.
( user_id
, current_gamer_tag
, last_update_timestamp
)

GAME
( game_id
, game_name  -- No timestamp here because the name doesn't change?
)

MESSAGE
( message_id  -- If you make this a GUID you can use it for synching with the server.
, sending_user_id -- FK to FRIEND
, receiving_user_id -- Also FK to FRIEND
, timestamp 
, content
)

This holds both outgoing and incoming messages locally and allows the message display to focus on gamer tags while at the same time being easy to synchronize with the server. By the way, you might also consider changing the receiving_user_id to a sub-table containing a list of recipients if you have three or more-way game play.

Using unique IDs is important for a lot of reasons. The most important is that it allows you to modify your gamer tags and prevents you from having to reveal your players' user IDs in the message displays. There is also a space saving here because an integer, even a bigint is smaller than a gamer tag. This is better for scalability. Using a GUID instead of an increasing integer for the message ID means that you won't have an "insert hot-spot" on your server's message table, which will perform better as long as your server message table has adequate free space built into it. Also, the message IDs can be generated at the client end and you can be pretty confident that there won't be any key collisions when the messages hit the server.

Here's my look on it. This is just how I would do it....

It all depends how your system works. If allow every username to be used once and not allow the user to change it, then logically you won't need to use an id. Personally, I use a auto_incremented id for my users database. If I were you, I would use a ID, it would simplify everything.

Probally on the local database you will need to have something like this:

Friend's database:
USER_ID  USERNAME

Game's database:
GAME_ID   USER_PLAYING_ID

Messages database:
TO_USER  TIMESTAMP  GAME_ID

So when submitting to the online database you will have the user's id to send to as well as the game information.

Again, just how I would do it. Other than everything else, it seems like you know what you are doing.