Moving away from STI - SQL to break single table into new multi-table structure

Question

I am moving old project that used single table inheritance in to a new database, which is more structured. How would I write a SQL script to port this?

Old structure

I've simplified the SQL for legibility.

CREATE TABLE customers (
  id int(11),
  ...
  firstName varchar(50),
  surname varchar(50),

  address1 varchar(50),
  address2 varchar(50),
  town varchar(50),
  county varchar(50),
  postcode varchar(50),
  country varchar(50),

  delAddress1 varchar(50),
  delAddress2 varchar(50),
  delTown varchar(50),
  delCounty varchar(50),
  delPostcode varchar(50),
  delCountry varchar(50),

  tel varchar(50),
  mobile varchar(50),
  workTel varchar(50),
);

New structure

CREATE TABLE users (
  id int(11),
  firstName varchar(50),
  surname varchar(50),
  ...
);

CREATE TABLE addresses (
  id int(11),

  ForeignKey(user),
  street1 varchar(50),
  street2 varchar(50),
  town varchar(50),
  county varchar(50),
  postcode varchar(50),
  country varchar(50),
  type ...,
);

CREATE TABLE phone_numbers (
  id int(11),
  ForeignKey(user),
  number varchar(50),
  type ...,
);

Answer 1

With appropriate cross-database notations for table references if appropriate:

INSERT INTO Users(id, firstname, surname, ...)
    SELECT id, firstname, surname, ...
        FROM Customers;
INSERT INTO Addresses(id, street1, street2, ...)
    SELECT id, street1, street2, ...
        FROM Customers;
INSERT INTO Phone_Numbers(id, number, type, ...)
    SELECT id, phone, type, ...
        FROM Customers;

If you want both the new and the old address (del* version), then repeat the address operation on the two sets of source columns with appropriate tagging. Similarly, for the three phone numbers, repeat the phone number operation. Or use a UNION in each case.

Answer 2

First make sure to backup your existing data!

The process is differnt if you are going to use the original id field or generate a new one.

Assuming you are going to use the orginal, make sure that you have the ability to insert id fields into the table before you start (the SQL Server equivalent if you are autogenrating the number is Set identity Insert on, not sure what mysql would use). Wirte an insert from the old table to the parent table:

insert newparenttable (idfield, field1, field2) 
select idfield, field1, field2 from old parent table

then write similar inserts for all the child tables depending on what fields you need. Where you have multiple phone numbers in differnt fields, for instance, you would use a union all stament as your insert select.

Insert newphone (phonenumber, userid, phonetype)
select home_phone, id, 100 from oldparenttable
union all
select work_phone, id, 101 from oldparenttable
Union all
select cell_phone, id, 102 from oldparenttable

If you are going to have a new id generated, then create the table with a field for the old id. You can drop this at the end (although I'd keep it for about six months). Then you can join from the new parent table to the old parent table on the oldid and grab the new id from the new parent table when you do you inserts to child tables. Something like:

Insert newphone (phonenumber, userid, phonetype)
select home_phone, n.id, 100 from oldparenttable o
 join newparenttable n on n.oldid = o.id
union all
select work_phone, n.id, 101 fromoldparenttable o
 join newparenttable n on n.oldid = o.id
Union all
select cell_phone, n.id, 102 from oldparenttable o
 join newparenttable n on n.oldid = o.id