I have a collection of csv files with measurements with the following 54 columns:
SITE (string) TIME (integer as yyyymmddhh) PROD (double) EST.0 (double) ... EST.50 (double)
I want to read this to a mysql database. I would prefer a structure that would allow me to compute average and variance of values EST.0 .. EST.50, for each row.
What would be a good database structure for this data? Can I keep the columns as they are, or should I only have one "value" column? And if the former, how can I then get the averages and variances that I want?
From reading related questions, it looked like having so many value columns is not a good idea, so I was thinking about having a "value" column for all the double values, plus an "est_ID" column for an identifier, using -1 or NULL to denote the "PROD" value - does it make sense?
And what about the date-time column - will mysql understand it, or do I need to provide some information about date-time format?
As for handling multiple files, I guess that a small bash script calling mysqlimport should do the job nicely?