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.