0

I have a similar problem that was previously solved here. However, there is one key difference. I need to keep the relationships to the original data.

For example, Imagine there is a table with information such as this:

user {id, first, last, address}

I'd like to move the address portion out of the 'user' table and into its own 'address' table.

user {id, first, last}
address {id, address}
user_address {user_id, address_id}

so the new 'address' table would contain the data that used to be stored in the 'user' table and the 'user_address' link table would maintain the connection to the original user.

Greg H
  • 23
  • 9

1 Answers1

0

In order to accomplish desired, you should apply three migration scripts.

INSERT INTO user(id, first, last)
SELECT
  u.id,
  u.first,
  u.last
FROM
  old_user u

 

INSERT INTO address(id, address)
SELECT DISTINCT
  /* I assume, that "id" is populated automatically (by increment etc.)  */
  u.address
FROM
  old_user u

 

INSERT INTO user_address(user_id, address_id)
SELECT
  u.id,
  a.id
FROM
  old_user u
JOIN
  address a ON a.address = u.address
Vadym Pechenoha
  • 574
  • 5
  • 17
  • So the first bit is used to make a copy and clean up the original table? – Greg H Jul 26 '17 at 18:30
  • Nope, first rename the table with old structure to something like "old_user", then create 3 new tables with appropriate structures, and then apply these scripts. – Vadym Pechenoha Jul 26 '17 at 18:33
  • ok, and my last question: what if there were more attributes than just address and not unique? could I still do a join on more than one column and just remove the "distinct" bits in the select statement? e.g. address = {address, address1, city, state, zip} – Greg H Jul 26 '17 at 18:34
  • If instead of one column "address" there will be a handful of columns - that's not a problem. But there shouldn't be multiple the same records in the "address" table. "user_address" is a many-to-many linking table. If you would like to have separate "address" records for each user even if it is duplicated, the address columns should be stored in the "user" table in that case. – Vadym Pechenoha Jul 26 '17 at 18:47
  • I previously had them stored in the user table but I'm looking for a one user to many addresses relationship. – Greg H Jul 26 '17 at 18:49
  • I have this so far: `INSERT INTO user SELECT * FROM old_user u; INSERT INTO address(id, address, address2, city, state, zip, last_modified_by, last_modified_on, created_by, created_on) SELECT distinct u.id, u.address, u.address2, u.city, u.state, u.zip, u.last_modified_by, u.last_modified_on, u.created_by, created_on FROM old_user u; INSERT INTO user_address(user_id, address_id) SELECT distinct u.id, a.id FROM old_user u JOIN address a ON a.address = u.address and a.address2 = u.address2 and a.city = u.city and a.state = u.state and a.zip = u.zip;` – Greg H Jul 26 '17 at 18:52
  • If the relationships between User and Address is many-to-many (user can have multiple addresses; address can host multiple users), desired structure that described in the question - is correct. But if you want to just handle one-to-many relationships between User and Address, the "user_address" table is redundant - link to the user can be directly stored in the "address" table in this case. – Vadym Pechenoha Jul 26 '17 at 18:55
  • That is a good point and I am going to change my approach now. However, for learning purposes it might be worthwhile to continue down this path to see how it would be done. – Greg H Jul 26 '17 at 18:58
  • I ended up using this as you recommended: `INSERT INTO address(id, address, address2, city, state, zip, user_id) SELECT distinct u.address, u.address2, u.city, u.state, u.zip, u.id FROM user u;` – Greg H Jul 26 '17 at 19:07
  • Great. Just modified the last query (for "user_address" population) - the "DISTINCT" was redundant there. – Vadym Pechenoha Jul 26 '17 at 19:13