Today's Posts Follow Us On Twitter! TFL Members on Twitter  
Forum search: Advanced Search  
  Members Login:
Lost password?
  Forum Statistics:
Forum Members: 24,255
Total Threads: 81,222
Total Posts: 566,958
There are 185 users currently browsing (tf).
  Our Partners:
  TalkFreelance     Design and Development     Programming     PHP and MySQL :

MySQL: Storing different types of data in 1 column

Thread title: MySQL: Storing different types of data in 1 column
    Thread tools Search this thread Display Modes  
10-07-2012, 01:57 AM
Dan is offline Dan
Dan's Avatar
Status: Request a custom title
Join date: Feb 2005
Posts: 3,164
iTrader: 15 / 86%

Dan is an unknown quantity at this point

  Old  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.

What would you do?

10-07-2012, 11:34 AM
Gaz is offline Gaz
Gaz's Avatar
Status: Request a custom title
Join date: Apr 2007
Location: UK
Expertise: Code & Programming
Software: Coda, TextMate, Sublime 2
Posts: 2,096
iTrader: 26 / 100%

Gaz will become famous soon enough Gaz will become famous soon enough

Send a message via Skype™ to Gaz


I'd split the columns..

10-07-2012, 12:47 PM
newkid is offline newkid
Status: Junior Member
Join date: Sep 2006
Expertise: PHP,MySQL, HTML(5), CSS
Software: Photoshop, Sublime Text2,MS VS
Posts: 63
iTrader: 1 / 100%

newkid is on a distinguished road

Send a message via Skype™ to newkid


Definitely split it.

Reply With Quote
10-07-2012, 04:28 PM
Village Genius is offline Village Genius
Village Genius's Avatar
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
Posts: 6,893
iTrader: 18 / 100%

Village Genius will become famous soon enough


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.


Thread Tools
Display Modes

  Posting Rules  
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump:
  Contains New Posts Forum Contains New Posts   Contains No New Posts Forum Contains No New Posts   A Closed Forum Forum is Closed