import data from csv to mysql - good database structure

Question

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?

Thanks.

Michal

Answer 1

You will want to have two database tables for this.

One table with id int/bigint autoincremenmt, site, time and prod. id is the primaty key.

And one table with id (same as in previous table but no autoincrement, it refers to the id in the first table) the number of the EST, and EST . The primary key of this table would span two columns, id and number of the EST (this may require some fiddling with the database table creation tool you are using).

As for date columns, no mysql doesn't understand that format, it has its own date and datetime formats for columns. At our place here we are using unix timestamps and store these in the database as integers or bigints.