I was looking around here for a while and couldn't get any answer that really covered it with explanation
So a little background, I am working on a room based FPS kind of game.
I need to find a way to manage the data:
Users - Details on the account
Characters - every user may have more than one
Inventory - For each character
(?)Store - Items and prices
(BTW : would you recommend store it in DB or hard code it in the game and update by patch? I will have to send a patch anyway for most of the new Items so they can work)
After some thought I decided as a security measure to not connect to the database directly from the client, but create a costume server that will handle requests and replays by socket connection.
No I am trying to wrap my head around the server logic, what will be the best way...
Should I use a MySQL server to handle everything? Isn't that a little unnecessary?
I could use a file to hold the data and poll it from there?
Or if I stay with MySQL, I could make the server app read all the data from the database and keep it on start up, since Store and things wont change after server start up
and then when user request something I don't need any database access, and when I need to update, I update locally on the server app and have a separated thread update the SQL database every minute, so if I crash I wont lose that much data
So these are my thoughts, what do you think?