denoise denoise - 3 months ago 9
MySQL Question

Normalize several columns to one many to many table in MySQL

I want to normalize a table with data spread in multiple columns to a many to many table.

In my case, I have a

person
table with
hobby1
,
hobby2
,
hobby3
,
hobby4
string columns containing the names found in a
hobby
table. Some are empty some are not.

Currently the db design looks like this:

original database design

Currently
person_hobby
is empty.

Can you help me with the SQL query for populating the
person_hobby
table with all the strings found in the
hobby
columns related to the
hobby.name
so I can get rid of them later?

Thanks

Answer

E.g.:

DROP TABLE IF EXISTS person;

CREATE TABLE person
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name VARCHAR(12) NOT NULL
,hobby1 VARCHAR(15) NULL
,hobby2 VARCHAR(15) NULL
,hobby3 VARCHAR(15) NULL
,hobby4 VARCHAR(15) NULL
);

DROP TABLE IF EXISTS person_hobby;

CREATE TABLE person_hobby
(person_id INT NOT NULL 
,hobby_id INT NOT NULL
,PRIMARY KEY(person_id,hobby_id)
);

DROP TABLE IF EXISTS hobby;

CREATE TABLE hobby
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,name VARCHAR(15) NOT NULL UNIQUE
);

INSERT INTO person VALUES
(1,'John'  ,'Reading'   ,'Cycling','Swimming',NULL),
(2,'Paul'  ,'Travelling','TV','Bird watching','Cinema'),
(3,'George','Fishing'   ,'Swimming',NULL,NULL),
(4,'Ringo',NULL,NULL,NULL,NULL);

INSERT INTO hobby (name) 
SELECT hobby1 FROM person WHERE hobby1 IS NOT NULL UNION 
SELECT hobby2 FROM person WHERE hobby2 IS NOT NULL UNION 
SELECT hobby3 FROM person WHERE hobby3 IS NOT NULL UNION 
SELECT hobby4 FROM person WHERE hobby4 IS NOT NULL;

INSERT INTO person_hobby
SELECT p.id 
     , h.id 
  FROM
     (
SELECT id
     , hobby1 hobby
  FROM person
 UNION
SELECT id 
     , hobby2 
  FROM person
 UNION
SELECT id 
     , hobby3
  FROM person
 UNION
SELECT id 
     , hobby4
  FROM person
     ) p
  JOIN hobby h
    ON h.name = p.hobby;

Query OK, 9 rows affected (0.05 sec)
Records: 9  Duplicates: 0  Warnings: 0

SELECT * FROM person_hobby;
+-----------+----------+
| person_id | hobby_id |
+-----------+----------+
|         1 |        1 |
|         1 |        4 |
|         1 |        6 |
|         2 |        2 |
|         2 |        5 |
|         2 |        7 |
|         2 |        8 |
|         3 |        3 |
|         3 |        6 |
+-----------+----------+

Now drop the columns from the person table.