0

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.

0

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
  • Different phone numbers should result in separate accounts. – MikeWu Jun 5 '14 at 21:42
  • Actually, name: Paul , phone: 2222 and name: Paulie , phone: 2222 should result in one account with either of the names. – MikeWu Jun 5 '14 at 22:22
  • You can still tweak the queries but I would check for duplicates and figure out which one you want to delete. For instance Paul 2222, Paulie 2222, Pual 2222. Once you have the base user_accounts setup then move to step 2. – SQLChao Jun 5 '14 at 22:33
  • This also brings up what happens if Paul 2222, Paulie 2222, and Paulie 5555 exist. I'm guessing the phone number make them unique? – SQLChao Jun 5 '14 at 22:36
  • Yes. The phone number makes them unique – MikeWu Jun 6 '14 at 1:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.