Take the 2-minute tour ×

We're restructuring our entire database. Said that, the database structure has changed drastically. Some table fields will be translated to table rows; there will be a lot of validations; some tables are broken up into multiple tables and other are combined. Basically we're changing the legacy database to 3NF.

New schema is ready and I am tasked to do ETL. How should I proceed? I am unfamiliar with any ETL tools so there is a learning curve there. I was thinking to write stored procedures, are they capable of dealing with all these complex stuff I am trying to achieve? I can write PHP scripts so I have better control over data validations but not sure if that is a way to go because of timeout and long scripts. The database size is about 6 GB and it has about 450 tables.

Suggestions please.

share|improve this question
I am not entirely sure about your situation but looks like a hybrid solution would be helpful in your case. So you can use php scripts/mysql stored procedures for some parts and use an ETL tool like Pentaho PDI(Kettle) for some other parts. –  Maximus2012 Sep 11 at 14:48
In any case, learning an ETL tool would be helpful in the long run as that would help you with db migration and backups etc. I would highly recommend Kettle/Pentaho PDI for that purpose. –  Maximus2012 Sep 11 at 14:49
add comment

1 Answer

I would recommend creating a PHP migration script, which is run from the command line. PHP scripts that are run from the command line do not time out.

You could also add "set_time_limit(0)" at the top of your script:

//rest of the code goes here

I'm not sure stored procedures or commercial ETL tools are necessary. Just make sure to break the migration script code into appropriate functions / methods, and you should be fine.

share|improve this answer
add comment

Your Answer


