Server / Database design for Strategy/Simulation type game (mobile)

Hey there,

i think my problem is rather complex (or at least it seems complicated to explain), so I will try to break it down as much as I can, without missing anything that might be needed to solve my problem. If I should forget anything important just ask me.

So I am currently developing some kind of (semi-) multiplayer strategy/simulation game for Android. It's best described as something like Fallout: Shelter. People are not really playing together in real time but they can interact with each other (trading, attacking, clans, highscore etc.).

So for now I have setup a rather rudimentary user registration and user login, sending their user credentials to my server which runs an API connected to a MySQL database and which manages the POST/GET request sent by the clients (i.e. the game).
My database so far contains only the "User" table in which the users name, email and password (hashed of course) are saved.

Now I want the players to be able to build a goldmine (just as an example for the sake of this thread, to get a basic idea on how to build things) which produces more gold the higher its level is.

Level 1: 100 gold / hour
Level 2: 200 gold / hour

and so on.

My question is: do I need another table in my database (in this case called "goldmine") which holds things like gold / level etc. and is connected to the user via one-to-many relationship or is it better to simply create another column in the user table containing the goldmines level (set to 0 if the player has not yet built a goldmine)?

Also I think the calculation of the gold amount which is collactable after a certain period of time should be made on the server because of a) avoid client side manipulation and b) that the calculation goes on even when the game is closed on the phone.
Should this be achieved by something like (on the server of course):

// Pseudo Code

goldmineLevel = User.getGoldmineLevel();

goldPerHour = goldmineLevel * 100;

Server.sentDataToPlayer(goldperHour);

Or is there any better way?

Sorry for the wall of text but I am really just starting out and I am enjoying it so far!

Any help is highly appreciated.

Thanks in advance!

 

((opinion.
It is probably better to make another table, and connect it to user table through user id. This way you wont' need to mess with user table in any way.
You don't need one-to-many here, and you can have one-to-one relationship, where playerId would map to a row in "player assets" table, where player assets would have a list of money generating resources. If types of resources are known in advance, yo ucan simply have "how many X resources player has" as a column.

((Opinion.
This seems to be the right idea.

It depends on how secure you want the data to be.

* Ideally the player should never send any kind of "remaining balance" back to the server, because this will allow user to hack the database and cheat.
* Instead, the player should send back "I buy X" and "I sell Y" commands, the server would verify their validity and then send back existing player stat (with assets and gold).
* The profit calculation should probably be done on the server, but to prevent it from running in background while the player is offline (draining resources), you could delay calculation of balance till the next time the player logs in.

Basically delayed profit calculation means that the last time profit was calculated (while player was online) is recorded to the database, and the next time the player logs in, the server catches up by calculating time difference and profit.

Obviously, if you want some sort of offline notifications, then the server will have to run periodically to update the stats.

Something like that.