How should I model this data? 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.
What you've modelled is a snapshot of the user's total progress.

I think you might want to track the user progress a little differently.  Instead of letting them enter their distance covered directly, why not track their way-points?  If you make a kind of audit trail of where they went (lat/lon) and when (date) then you can easily calculate their distance covered using Vincenty or another numerical method.

By tracking the progress on a map (i.e. by lat/lon) by date you can have more interesting reporting of the data.  For example, what if two "contestants" want to race but can't start in the same place at the same time?  How do you compare their progress?