By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- old table and data
CREATE TABLE oldtable (id INT, -- unique ID
item INT, -- separate items
cat INT); -- item category
INSERT INTO oldtable VALUES
(11,1,555),
(12,2,555),
(13,3,777),
(14,11,555),
(15,12,555),
(16,13,555),
(17,111,777),
(18,112,777),
(19,113,888);
SELECT * FROM oldtable ORDER BY id;
Records: 9 Duplicates: 0 Warnings: 0
| id | item | cat |
|---|---|---|
| 11 | 1 | 555 |
| 12 | 2 | 555 |
| 13 | 3 | 777 |
| 14 | 11 | 555 |
| 15 | 12 | 555 |
| 16 | 13 | 555 |
| 17 | 111 | 777 |
| 18 | 112 | 777 |
| 19 | 113 | 888 |
-- new normalized structure
CREATE TABLE newcat (cat_id INT AUTO_INCREMENT PRIMARY KEY,
cat_value INT);
CREATE TABLE newtable (item_id INT AUTO_INCREMENT PRIMARY KEY,
item_value INT,
cat_id INT,
FOREIGN KEY (cat_id) REFERENCES newcat (cat_id));
-- copy dictionary data
INSERT INTO newcat (cat_value)
SELECT DISTINCT cat
FROM oldtable;
-- copy main data, fill reference to dictionary
INSERT INTO newtable (item_id, item_value, cat_id)
SELECT oldtable.id, oldtable.item, newcat.cat_id
FROM oldtable
JOIN newcat ON oldtable.cat = newcat.cat_value;
Records: 3 Duplicates: 0 Warnings: 0
Records: 9 Duplicates: 0 Warnings: 0
-- new data state
SELECT * FROM newtable;
SELECT * FROM newcat;
| item_id | item_value | cat_id |
|---|---|---|
| 11 | 1 | 1 |
| 12 | 2 | 1 |
| 13 | 3 | 2 |
| 14 | 11 | 1 |
| 15 | 12 | 1 |
| 16 | 13 | 1 |
| 17 | 111 | 2 |
| 18 | 112 | 2 |
| 19 | 113 | 3 |
| cat_id | cat_value |
|---|---|
| 1 | 555 |
| 2 | 777 |
| 3 | 888 |
-- select old data representation from new structure
SELECT newtable.item_id AS id, newtable.item_value AS item, newcat.cat_value AS cat
FROM newtable
JOIN newcat USING (cat_id)
ORDER BY item_id;
| id | item | cat |
|---|---|---|
| 11 | 1 | 555 |
| 12 | 2 | 555 |
| 13 | 3 | 777 |
| 14 | 11 | 555 |
| 15 | 12 | 555 |
| 16 | 13 | 555 |
| 17 | 111 | 777 |
| 18 | 112 | 777 |
| 19 | 113 | 888 |