It will be mostly click based, ie: There will be buttons for activities like Eat, Play, Study, etc, and in the game environment, one day will contain 3 phases, day, noon, night . And for each phase, users can pick between those activities and those activities will increase certain character parameters (Intelligence, Focus, Fun, etc) then the game will progress to the next phase. Once it's night, the game will progress to the next day.
So I need feedbacks on how to saving the game progress. Here's what I had in mind :
There will be a table for Users (obviously) that will contain users' data including the parameters/stats, money, etc. Do you think the stats etc must be separated to another table so there will be a history of stat increases?
For the saving, I'm thinking of using a Logs table that record every activity is by users. So if in day 1 phase 1 (phase day) the user picks Eat, I will insert to this logs table more or less like : user_id = 1, day = 1, phase = 1, activity = eat. So for example when the user stops playing and exits the game after day 1 phase night, the database will have :
user_id = 1, day = 1, phase = 1, activity = eat user_id = 1, day = 1, phase = 2, activity = play user_id = 1, day = 1, phase = 3, activity = sleep
And when they continue the game, I will read from this table and get the latest row of this user_id, and after checking that the latest row is day 1 phase 3, the game interface will display day 2 phase 1. However, this means that the phases for a day must be fixed from the start, so I can't include, for example, random additional time phases outside these phases (think of it as an extra opportunity to increase the stats).
So what do you think? I hope the explanation is clear enough.