CODEWITHSUNDEEP
phpmysqlnormalize
VHanded
VHanded

Reputation: 2089

What is the procedure to normalize a database with PHP?

I just took over a pretty terrible database design job, which heavily use comma separated value to store data. I know I know, it is hell.

The db is mysql, currently accessing it using MySql Workbench.

I already had idea in mind what to remove, and what new relations table needed.

So, my question is, how shall I proceed by migrating comma separated data to the new table? Any tools specialize for normalizing database?

Edit: The server code is in PHP.

Upvotes: 0

Views: 367

Answers (2)

zedfoxus
zedfoxus

Reputation: 37119

It appears you are looking for standard practices. There are varying degree of denormalized databases out there. The ones I have come across have been normalized with custom code and tools.

SQL Server Integration Services (SSIS) can be used for some case. In your case, I'd build a script for the migration that involves:

  • creation of normalized tables
  • creating stored procedure or PHP script(s) to read data from denormalized table, transform it and load it into normalized table
  • creating a log table or log file
  • performing the migration in sandbox; write logs while doing so
  • version control the script
  • correct the proc/script as needed
  • create another sandbox
  • run the full script on sandbox
  • if successful, run the full script on prod (with logging)

SSIS is used for ETL in many organizations; it's standard tool for Microsoft BI stack and can also be used to migrate data between non-Microsoft DBs also.

Open Source ETL tool called Talend might also help in transforming your data. I personally believe that a PHP script will be the fastest and easiest to manipulate data.

Upvotes: 1

panofish
panofish

Reputation: 7889

Define you new tables and attributes first.

Then, use PHP or Python or your favorite language with MySQL calls and write a 1 time converter which loops and reads the old table(s) and records and inserts the proper records into the new tables.

Upvotes: 1

Related Questions