Create and Fill xTable for n:m relationship (normalize)

Presuppositions: Table B (werk) contains column textsorte (VARCHAR) with a limited set of values to be normalized. Mulitple values are set as concatenated strings like 'value1/value2/value3'  All

Presuppositions:

1. Table B (werk) contains column textsorte (VARCHAR) with a limited set of values to be normalized.

2. Mulitple values are set as concatenated strings like 'value1/value2/value3' 

3. All distinct values from column b.textsorte are already imported to column textsorte.name (table A) which has only columns id and name.

4. Now Table c  (werk_textsorte) should link table a with table b by column textsorte (will be removed afterwards)

5. Table c will represent a n:m-relation between table a and b.

6. columns c.werknr and c.textsorte_id can then be declared as foreign_keys with constraints.

7. Combined index on c.werknr, c.textsorte will be unique (could be set primary_key as well).

1. Create CrossTable

DROP TABLE werk_textsorte;
CREATE TABLE werk_textsorte (
'id' int(10) unsigned NOT NULL AUTO_INCREMENT,
'werknr' int(10) unsigned NOT NULL,
'textsorte_id' int(10) unsigned NOT NULL,
PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 

2. FillCrossTable by Procedure

-- vor Neuausführung xTable leeren und auto-increment auf 1 setzen!
-- TRUNCATE TABLE werk_textsorte;
-- ALTER TABLE werk_textsorte AUTO_INCREMENT=1;

DROP PROCEDURE IF EXISTS fillXtable();

DELIMITER ;;
CREATE PROCEDURE fillXtable()
BEGIN

DECLARE tname varchar(255);
DECLARE tid INT DEFAULT FALSE;
DECLARE done INT DEFAULT FALSE;
-- hier die table (a) für die loop werte
DECLARE cursor_i CURSOR FOR SELECT 'id','name' FROM textsorte;
-- wenn am ende des rowsets -> done auf true setzen
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cursor_i;

read_loop: LOOP
-- werte aus table a in variablen laden (cursor wird automat. um 1 weitergesetzt)
FETCH cursor_i INTO tid,tname;
-- bedingung für loop ende (cursor gibt NOT FOUND zurück wenn am ende des Rowsets, dann wird done auf true gesetzt, s.o.)
IF done THEN
LEAVE read_loop;
END IF;
-- Kreuztabelle füllen mit wert aus table b und aktuellem wert aus table a (in variable tid gespeichert)
-- Wenn die Werte eindeutig sind, kann auch '=' ohne '%' gesetzt werden, in diesem Fall waren multiple Werte als String vorhanden <value1/value2/....> deshalb mit LIKE ...
INSERT INTO werk_textsorte (textsorte_id, werknr) SELECT tid, w.werknr FROM werk w WHERE w.textsorte LIKE CONCAT('%', tname, '%');
-- SELECT sname;

END LOOP;
CLOSE cursor_i;

End;;
DELIMITER ;

-- Execute PROCEDURE
CALL fillXtable();

-- Procedure NICHT 2x ausführen, daher am Ende am besten wieder löschen
-- vor Neuausführung xTable leeren und auto-increment auf 0 setzen!
-- DROP PROCEDURE fillXtable();