I have a need to model the following requirement for a web application (PHP/MySQL5):
A user can compete in a distance-based challenge which consists of entering progress along a defined round-the-world route
- A JS map displays an individual user’s progress in distance based on the distance they have covered in the challenge relative to the total target distance (a user updates distance covered periodically)
- A Challenge can have many lat/long points through which the challenge route travels
- More than one user can participate in the same challenge
I have roughly sketched how I would do this but would appreciate advice on the DB design. Initially there will actually only be one pre-defined challenge as this is for a prototype but I would like to model the data in such a way that it can cope with future development e.g. Users challenging other users to a 'race;' i.e participating in the same challenge and displaying both their paths on the same map.
Tables:
challenges
- id
- title
- total distance
challenge_coordinates
- id
- lat
- long
- challenge_id
challenges_users
- user_id
- challenge_id
- distance_covered
I already have a user-management system in place with a users table.