Geek Answers Handbook

Database messaging schema

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

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 save the message in my local database and then send the message 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 this time. All received messages are then deleted from the database.

Is there something wrong with the way I do it? I am trying to prepare for the worst, and I have no idea how this kind of plan will scale. I do not use a unique identifier for the Users table, and I feel that I should. Since my experience with databases is limited, I don’t fully understand the meaning of the unique identifier auto-increment or how it will help me. Any advice / criticism would be appreciated.

+5
2

, , , (private) (, , ), :

FRIEND  -- The local user 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
)

, , , . , _user_id , , .

. , . , , , , gamer. . GUID , " " , , , , , , , , .

+2

. , ....

, . , . aut_incremented id . , , .

, - :

Friend database:
USER_ID  USERNAME

Game database:
GAME_ID   USER_PLAYING_ID

Messages database:
TO_USER  TIMESTAMP  GAME_ID

- , .

, . , , , .

+2

More articles:


All Articles