General Database Strategy - Efficient storage of fitness data
Posted on 2006-06-04
Very new to SQL, and would very much appreciate a couple of pointers on proper database strategy for my application.
I'm desiging a mySQL database to store details on athletes and workout data
- Athlete info such as name, email, etc...
- Workout data
* Top level summary - cumulative time, average heart rate
* Detailed workout data includes sample time and instantaneous heart rate data (e.g. every 5 seconds or 10 seconds). Some workouts may include up to 4-5 different data types at this sampling rate, whereas other workouts will only have 1-2.
The database must be scalable, and robust enough to store thousands of athletes, each who may have hundreds of workouts, and each workout may have hundreds of datapoints. One obvious required table stores athlete particulars. What I'm less sure about is how to best and most efficiently store workout data.
For workout data, one table could include a top level summary of workouts: athleteID, cumulate time, average heart rate, etc... and other summary data.
My biggest question is how/where should I store the large amount of detailed workout data (e.g. hundreds of datapoints of sample time and heart rate).
If I create a single table to store ALL workout data points for all athletes and all workouts, it would seem to be an unmanageable table with millions of rows. It would require a dedicated column for workoutID, which seems a bit wasteful. Even more wasteful might be the fact that many workouts will only include 1-2 data types (fields), but others will have 4-5 - so lots of empty cells.
If I create a table for each workout, I could have thousands of workout tables. This seems complicated to create and recall so many tables, each with a different name (perhaps each named with a unique workoutID). I suppose in this case I would also need a place to store a growing list of workoutIDs for each athlete - and so my athlete table would become inefficient if I kept that a single table, since certain athletes would have many more workoutID columns than others.
Thanks so much for any general strategy tips.