0

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.

Improve this question
3
  • If it's a virtual race, why use waypoints? Just track the distance.
    – Noam Kremen
    Sep 20, 2011 at 15:02
  • Have I asked my question incorrectly? I am interested in whether the data model is correct rather than whether I should/shouldn't be using waypoints.
    – codecowboy
    Sep 21, 2011 at 13:40
  • Ok. In the current model, you only know the distance covered. In SQL, how would you calculate the last waypoint passed given only distance? Same question, but now with waypoint id-s not consecutive. What if it's a circular path? What if there are alternative paths?
    – Noam Kremen
    Sep 21, 2011 at 15:18

1 Answer 1

Reset to default
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?

Improve this answer
2
  • Thanks for your reply. The reason for this is that it is a virtual race based on distance travelled on a machine at a gym which the user has to enter - this is in the spec and won't be changed.
    – codecowboy
    Sep 20, 2011 at 13:37
  • 1
    Still, don't you think that an audit trail of incremental distances by date would be more interesting than a total distance per user and race? Even if the user were to enter their total distance at any given time, wouldn't it be potentially useful to have a dated record of that which would allow you to show progress over time?
    – Joel Brown
    Sep 20, 2011 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.