most likely a clueless question but I would like to start off on the good foot: Despite trying my best, I have actually never really learned to program and I'm kind of "learning as I go" so please excuse me if this seems very obvious to you... It's more of a suggestion and feedback kind of question rather than pure programming.
My situation is the following: I'm building a racing game that would receive various inputs from a number of users (through a php website), I am storing that information in a MySQL database, and once a week I would like to process all that information to generate "lap times", which will then create a race (my "output"). Not taking into account the various methods of calculating that output, I need to do two important things which I'm not sure how to begin with at all :
1) Storing the race information for every user (lap time per lap, fastest lap, race position per lap, race position at end of race, award points depending on the position). Where and how should I optimally store those informations ? I have created a race DB with a unique identifier that auto increments, I'm thinking I will generate 1 set of data for each race, so should I store all the information pertaining to that race in there ? Would I then create a data row (with type time?) for the lap time informations (1 row for lap1, 1 row for fastest, etc... ?)? But how would I know which user (I have a unique userID for each) did which lap (how would I assign the userID to the lap time)?
2) At the end of the race I need to award points depending on race position at the end, should I just compare total lap times (additional row?) and sort by lowest first ? The points data would be stored in the user DB ?
I appreciate any input you might have for the modeling of this project !
Drop every lap_round, lap_time and position in the DB and add a user_id and a race_id. Afterwards query the laps. That way you can tell which is fastest overall, fastest per user, time per lap and much more.
To get the position query the db for the last lap. It holds its position.
Points are user based, so put them in the user table. Just add. But if you want to tell how many points were added per race than make a seperate table points (user_id, race_id, points)