Re^2: perl mysql - INSERT INTO, 157 columns

<zero perl content> 157 columns in a table is not good database design - it makes maintenance / reporting / analysis very difficult no matter whether you are using perl or anything else. I would normally be concerned with any more than 30 columns in a table - then I would look to see if I could split into two or more tables. You may possibly lose a very small amount of performance speed having to join the tables when you retrieve the data but it does make things infinitely more maintainable

Comment on Re^2: perl mysql - INSERT INTO, 157 columns

Replies are listed 'Best First'.
Re^3: perl mysql - INSERT INTO, 157 columns by locked_user sundialsvc4 (Abbot) on May 02, 2014 at 11:42 UTC
Spreadsheets often have a very-ridiculous number of columns because it is easiest for a macro to work with one row of the sheet at a time, without resorting to `VLOOKUP()` and other such rot. Even though the sheet contents are often the product of a fairly substantial database `JOIN`, you often do not have access to the original database from when the spreadsheet ultimately came. What I usually suggest, in cases like this, is that you should try to avoid writing programs just to get your data into the thing, and that if possible you should do further manipulations by stored-procedure within the database environment. Something equivalent to `LOAD DATA INFILE` usually exists somewhere, and the table which this loads into ought to basically be just a “holding tank.” Then, additional queries can be run to, in effect, “un-join” the data and use it to update the permanent tables. You may or may not use Perl, as the case may be, to do this.

Replies are listed 'Best First'.

Re^3: perl mysql - INSERT INTO, 157 columns
by locked_user sundialsvc4 (Abbot) on May 02, 2014 at 11:42 UTC

Spreadsheets often have a very-ridiculous number of columns because it is easiest for a macro to work with one row of the sheet at a time, without resorting to VLOOKUP() and other such rot. Even though the sheet contents are often the product of a fairly substantial database JOIN, you often do not have access to the original database from when the spreadsheet ultimately came. What I usually suggest, in cases like this, is that you should try to avoid writing programs just to get your data into the thing, and that if possible you should do further manipulations by stored-procedure within the database environment. Something equivalent to LOAD DATA INFILE usually exists somewhere, and the table which this loads into ought to basically be just a “holding tank.” Then, additional queries can be run to, in effect, “un-join” the data and use it to update the permanent tables. You may or may not use Perl, as the case may be, to do this.