How should I model this data?

Question

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.

Answer 1

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?