Migrating from STI-SQL to split one table into a new structure with multiple tables
I am moving an old project that uses unidirectional inheritance to a new database that is more structured. How can I write a SQL script to port it?
Old structure
I have simplified the SQL for readability.
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 ...,
);
a source to share
If necessary, use the appropriate cross-referenced databases for table references:
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), repeat the address operation on the two sets of original columns with the appropriate label. Likewise, for three phone numbers, repeat the operation with the phone number. Or use UNION in every case.
a source to share
Back up your existing data first!
The process is different if you are going to use the original id field or create a new one.
Assuming you are going to use the original, make sure you have the ability to insert identity fields into the table before starting (SQL Server equivalent if you autogenerate a number. Set identity Insert on, not sure what mysql will use). Create 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 child tables depending on what fields you need. For example, if you have multiple phone numbers in different fields, you should use the concatenation of all items as the paste selection.
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 create a new ID, create a table with a field for the old ID. You can opt out of this at the end (although I would keep it for about six months). Then you can join the new parent table to the old parent table in oldid and grab the new ID from the new parent table when you insert the child tables. Sort of:
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
a source to share