MySQL: Storing different types of data in 1 column
The measurement column's data varies. Some measurements will be strict numbers (time, distance, reps, weight - converted to seconds, cm's, lbs.)
I'm at a crossroad with how I store an exercise that's measurement is both weight and reps. I could do something like "60 20" for 60 lb 20 reps. However querying for that data might become a burden if I cannot keep the column as int (would have to change for varchar I figure.)
Would it be best to add more columns to each exercise:
id | userid | exerciseid | date | notes | time | weight | distance | reps
Or am I good with having just the single column to contain all the data? If it were separated into many I'd have loads of NULL values.
I would eventually be pulling the numbers to create graphs. If I could query them correctly I can take the burden off PHP.
Never store multiple data pieces in a single row, it is always bad database design. It has no real advantages and can create any number of complications down the road. Good luck writing an efficient query to find all workouts of 20 reps to track increase when storing them in multiple rows.