Mysql – Split MySql table into two tables with One to many Relationship

database-designMySQLrelational-theoryschema

I have an old database table in MySql. It is used to store the orders from online ecommerce. Simplified diagram looks like this: 

+----+------+-----------------------+-------+--------------+
| id | name | address               | phone | order_number |
+----+------+-----------------------+-------+--------------+
|  1 | Joe  | Joes Address          | 1111  | 1390842      |
|  2 | Paul | Pauls Address         | 2222  | 9082309      |
|  3 | Greg | Gregs Address         | 3333  | 0928340      |
|  4 | Lucy | Lucys Address         | 4444  | 9028340      |
|  5 | Paul | Pauls Address         | 2222  | 8958399      |
|  6 | Tom  | Toms Address          | 5555  | 9084024      |
|  7 | Lucy | Lucys Another Address | 4444  | 9801983      |
|  8 | Paul | Pauls Another Address | 2222  | 0982304      |
+----+------+-----------------------+-------+--------------+

For each order there is a separate name address and phone number. Some customers placed the order 2 or more times ( Lucy and Paul). Paul placed two orders when he lived at Pauls Address and one order when he moved to Pauls Another Address. Lucy placed an order and than another order once she moved.

What I need to do is split this table into two tables. One table will be user_account table that will hold only the name and the phone number, while another table will hold the addresses and will be related to users_account by FK.

user_accounts should look like this: 

+----+------+-------+    
| id | name | phone |
+----+------+-------+
|  1 | Joe  | 1111  |
|  2 | Paul | 2222  |
|  3 | Greg | 3333  |
|  4 | Lucy | 4444  |
|  5 | Tom  | 5555  |
+----+------+-------+

while addresses should look like this: 

+----+-----------------------+-----------------+
| id | address               | user_account_id |
+----+-----------------------+-----------------+
|  1 | Joes Address          | 1               |
|  2 | Pauls Address         | 2               |
|  3 | Gregs Address         | 3               |
|  4 | Lucys Address         | 4               |
|  5 | Toms Address          | 5               |
|  6 | Lucys Another Address | 4               |
|  7 | Pauls Another Address | 2               |
+----+-----------------------+-----------------+

How would I go about generating those 2 tables from my old single table. Any help appreciated. I'm completely stuck with this, and not even sure what query should I look for in google to point me into right direction. There is about 100k rows in the original table.

PS.

I would like to create the user_accounts table based on distinct phone numbers in regular table.

Best Answer

Not enough time to test or write out all of the table creation but this is a start...

First create the table to hold the users. You can get data from the following query. One thing I don't know how you want to handle is if a user exists with a different phone number.

SELECT DISTINCT name
  , phone
FROM old_table

After you have the user_accounts table join it back to the old_table to get the address along with the new user id.

SELECT DISTINCT a.address
  , b.id
FROM old_table a
INNER JOIN user_accounts b on a.name = b.name and a.phone = b.phone
Related Question