CODEWITHSUNDEEP
mysqldatabasemany-to-manyrelational-database
denoise
denoise

Reputation: 1097

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

Upvotes: 1

Views: 1257

Answers (2)

Strawberry
Strawberry

Reputation: 33935

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.

Upvotes: 4

Jakumi
Jakumi

Reputation: 8374

repeat for every hobby field (notice the hobby1 in the ON-clause):

INSERT IGNORE INTO person_hobby (person_id, hobby_id) 
     SELECT person.id, hobby.id
     FROM person
     INNER JOIN hobby ON (person.hobby1=hobby.name)

Upvotes: 0

Related Questions