I am trying to utilize a database because the back-finish for any texting system during my game (kind of like im). I'm utilizing a local database to keep received messages along with a database on my small server to transmit them. Listed here are the tables that i'm using:

userName (varchar)
displayName (varchar)
currentGames (varchar)

sender (varchar)
receiver (varchar)
message (varchar)
timestamp (int)

My plan's that after a person transmits a note, When i first keep message within their local database after which send the content off and away to the server.

Whenever a user inspections to ascertain if you will find any new messages (polling), he first will get the most recent timestamp from his local database and uses this time around to question the internet database for those messages sent that point on. All messages received are then erased in the database.

Can there be a problem using the way I am carrying this out? I am attempting to get ready for the worst, and that i have no clue how this kind of plan will scale. I am not utilizing a unique id for that "Customers" table and that i believe that I ought to. Since my database experience is restricted I do not completely understand the value of the initial auto-increment id or the way it is needed me here. Any advice/critique could be appreciated.

Here's my look onto it. Case the way i would get it done....

Everything is dependent the way your system works. If allow every username for use once and never permit the user to alter it, then realistically you will not want to use an id. Personally, I personally use a auto_incremented id for my customers database. Personally, I'd make use of a ID, it might simplify everything.

Probally around the local database you will have to have something similar to this:

Friend's database:

Game's database:

Messages database:

Then when posting towards the online database you'll have anyone's id to transmit to in addition to the overall game information.

Again, precisely how I'd get it done. Apart from anything else, it appears like guess what happens you do.

Because most gamer tags are transient and also you most likely wish to differentiate from a gamer's ID (private) as well as their user title (public, a minimum of to buddies) then you'll need a local design such as this:

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

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

( 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 in your area and enables the content display to pay attention to gamer tags yet still moment simple to synchronize using the server. Incidentally, you could also consider altering the receiving_user_id to some sub-table that contains a listing of readers for those who have three or even more-way action.

Using unique IDs is essential for a number of reasons. The most crucial is it enables you to definitely modify your gamer tags and prevents you against needing to reveal your players' user IDs within the message shows. There's additionally a space-saving here because an integer, a bigint is more compact than the usual gamer tag. This really is better for scalability. Utilizing a GUID rather than an growing integer for that message ID implies that you will not come with an "place hot-place" in your server's message table, that will perform better as lengthy as the server message table has sufficient free space included in it. Also, the content IDs could be produced in the client finish and you will be pretty certain that there will not be any key collisions once the messages hit the server.