Database game messaging schema

Question

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 uses 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.

Answer 1

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.