Mysql – How should I model this data

database-designMySQL

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.

Best Answer

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?

Related Question