Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I have a de-normalized table as

Original De-Normalized Table

id  text
23  first,second|third,fourth,fifth|sixth

I want to normalize the database to create the following tables

Table 1: (Split by "|")

id  parent_id   value
1   23          first,second
2   23          third,fourth,fifth
3   23          sixth

and then,

Table 2 (Split by ",")

id  parent_id   value
1   1           first
2   1           second
3   2           third
4   2           fourth
5   2           fifth
6   3           sixth

The easiest and straightforward method is to use sub-query (SELECT), but my concern is the performance, as this process should be conducted for a very long table and regularly (new data coming).

Is there an efficient way to normalize this table with high speed?

share|improve this question

1 Answer

up vote 3 down vote accepted

I created a Stored Procedure solution to perform the following:

  • Convert the txt field of t1 into an extended INSERT list delimiting by '|'
  • Execute the extended INSERTs
  • Convert the value field of t2 into an extended INSERT list delimiting by ','
  • Execute the extended INSERTs

Here is the sample data

DROP DATABASE IF EXISTS all_denorm;
CREATE DATABASE all_denorm;
USE all_denorm
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
CREATE TABLE t1
(
    id INT NOT NULL,
    txt TEXT
) ENGINE=InnoDB;
INSERT INTO  t1 (id,txt) VALUES
(23,'first,second|third,fourth,fifth|sixth'),
(24,'seventh,eighth|ninth|tenth,eleven|twelfth'),
(25,'thirteenth|fourteenth|fifteenth|sixteen|seventeenth'),
(26,'eighteenth,nineteenth|twentieth');
CREATE TABLE t2
(
    id INT NOT NULL AUTO_INCREMENT,
    parent_id INT NOT NULL,
    VALUE VARCHAR(255),
    PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE TABLE t3 LIKE t2;

Here is the Stored Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `all_denorm`.`process_denorm` $$
CREATE PROCEDURE `all_denorm`.`process_denorm` ()
BEGIN

    DECLARE given_id,current_id,last_id,processing INT;
    DECLARE delimiter_char CHAR(1);

    SET current_id = 0;
    SET processing = 1;
    SET delimiter_char = '|';
    WHILE processing = 1 DO
        SELECT MIN(id) INTO given_id FROM t1 WHERE id > current_id;
        IF ISNULL(given_id) THEN
            SET processing = 0;
        ELSE
            SET current_id = given_id;
            SELECT
                REPLACE(REPLACE(CONCAT('(''',REPLACE(txt,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
                INTO @insert_list FROM t1 WHERE id = given_id;
            SELECT CONCAT('insert into t2 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
            PREPARE stmt FROM @insert_SQL;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    END WHILE;

    SET current_id = 0;
    SET processing = 1;
    SET delimiter_char = ',';
    WHILE processing = 1 DO
        SELECT MIN(id) INTO given_id FROM t2 WHERE id > current_id;
        IF ISNULL(given_id) THEN
            SET processing = 0;
        ELSE
            SET current_id = given_id;
            SELECT
                REPLACE(REPLACE(CONCAT('(''',REPLACE(value,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
                INTO @insert_list FROM t2 WHERE id = given_id;
            SELECT CONCAT('insert into t3 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
            PREPARE stmt FROM @insert_SQL;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    END WHILE;

    SELECT * FROM t1;
    SELECT * FROM t2;
    SELECT * FROM t3;

END $$

DELIMITER ;

Here it is executed

mysql> DROP DATABASE IF EXISTS all_denorm;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE all_denorm;
Query OK, 1 row affected (0.00 sec)

mysql> USE all_denorm
Database changed
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS t3;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1
    -> (
    ->     id INT NOT NULL,
    ->     txt TEXT
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO  t1 (id,txt) VALUES
    -> (23,'first,second|third,fourth,fifth|sixth'),
    -> (24,'seventh,eighth|ninth|tenth,eleven|twelfth'),
    -> (25,'thirteenth|fourteenth|fifteenth|sixteen|seventeenth'),
    -> (26,'eighteenth,nineteenth|twentieth');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     parent_id INT NOT NULL,
    ->     VALUE VARCHAR(255),
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t3 LIKE t2;
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `all_denorm`.`process_denorm` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `all_denorm`.`process_denorm` ()
    -> BEGIN
    ->
    ->     DECLARE given_id,current_id,last_id,processing INT;
    ->     DECLARE delimiter_char CHAR(1);
    ->
    ->     SET current_id = 0;
    ->     SET processing = 1;
    ->     SET delimiter_char = '|';
    ->     WHILE processing = 1 DO
    ->         SELECT MIN(id) INTO given_id FROM t1 WHERE id > current_id;
    ->         IF ISNULL(given_id) THEN
    ->             SET processing = 0;
    ->         ELSE
    ->             SET current_id = given_id;
    ->             SELECT
    ->                 REPLACE(REPLACE(CONCAT('(''',REPLACE(txt,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
    ->                 INTO @insert_list FROM t1 WHERE id = given_id;
    ->             SELECT CONCAT('insert into t2 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
    ->             PREPARE stmt FROM @insert_SQL;
    ->             EXECUTE stmt;
    ->             DEALLOCATE PREPARE stmt;
    ->         END IF;
    ->     END WHILE;
    ->
    ->     SET current_id = 0;
    ->     SET processing = 1;
    ->     SET delimiter_char = ',';
    ->     WHILE processing = 1 DO
    ->         SELECT MIN(id) INTO given_id FROM t2 WHERE id > current_id;
    ->         IF ISNULL(given_id) THEN
    ->             SET processing = 0;
    ->         ELSE
    ->             SET current_id = given_id;
    ->             SELECT
    ->                 REPLACE(REPLACE(CONCAT('(''',REPLACE(value,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
    ->                 INTO @insert_list FROM t2 WHERE id = given_id;
    ->             SELECT CONCAT('insert into t3 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
    ->             PREPARE stmt FROM @insert_SQL;
    ->             EXECUTE stmt;
    ->             DEALLOCATE PREPARE stmt;
    ->         END IF;
    ->     END WHILE;
    ->
    ->     SELECT * FROM t1;
    ->     SELECT * FROM t2;
    ->     SELECT * FROM t3;
    ->
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;

Here is the output:

mysql> CALL process_denorm();
+----+-----------------------------------------------------+
| id | txt                                                 |
+----+-----------------------------------------------------+
| 23 | first,second|third,fourth,fifth|sixth               |
| 24 | seventh,eighth|ninth|tenth,eleven|twelfth           |
| 25 | thirteenth|fourteenth|fifteenth|sixteen|seventeenth |
| 26 | eighteenth,nineteenth|twentieth                     |
+----+-----------------------------------------------------+
4 rows in set (0.01 sec)

+----+-----------+-----------------------+
| id | parent_id | VALUE                 |
+----+-----------+-----------------------+
|  1 |        23 | first,second          |
|  2 |        23 | third,fourth,fifth    |
|  3 |        23 | sixth                 |
|  4 |        24 | seventh,eighth        |
|  5 |        24 | ninth                 |
|  6 |        24 | tenth,eleven          |
|  7 |        24 | twelfth               |
|  8 |        25 | thirteenth            |
|  9 |        25 | fourteenth            |
| 10 |        25 | fifteenth             |
| 11 |        25 | sixteen               |
| 12 |        25 | seventeenth           |
| 13 |        26 | eighteenth,nineteenth |
| 14 |        26 | twentieth             |
+----+-----------+-----------------------+
14 rows in set (0.01 sec)

+----+-----------+-------------+
| id | parent_id | VALUE       |
+----+-----------+-------------+
|  1 |         1 | first       |
|  2 |         1 | second      |
|  3 |         2 | third       |
|  4 |         2 | fourth      |
|  5 |         2 | fifth       |
|  6 |         3 | sixth       |
|  7 |         4 | seventh     |
|  8 |         4 | eighth      |
|  9 |         5 | ninth       |
| 10 |         6 | tenth       |
| 11 |         6 | eleven      |
| 12 |         7 | twelfth     |
| 13 |         8 | thirteenth  |
| 14 |         9 | fourteenth  |
| 15 |        10 | fifteenth   |
| 16 |        11 | sixteen     |
| 17 |        12 | seventeenth |
| 18 |        13 | eighteenth  |
| 19 |        13 | nineteenth  |
| 20 |        14 | twentieth   |
+----+-----------+-------------+
20 rows in set (0.04 sec)

Query OK, 0 rows affected (0.10 sec)

mysql>

Give it a Try !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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