Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
If it's a virtual race, why use waypoints? Just track the distance. –  Noam Kremen Sep 20 '11 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 '11 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 '11 at 15:18

1 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?

share|improve this answer
    
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 '11 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 '11 at 15:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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