Using query to change table mapping

Question

I have a table mytable( id, key, value). I realize that key is generating a lot of data redundancy since my key is a string. (my keys are really long, but repetititve) How do I build a separate table out that has (key, keyID) and then alternate my table to be mytable( id, keyID, value) and keyTable(keyID, key) ?

Answer 1

1. Create keyTable

2. Fill keys from mytable:

   INSERT INTO keyTable (`key`) SELECT DISTINCT mytable.key FROM mytable;
   

3. add keyID column to mytable

4. Assign keyIDs:

   UPDATE mytable SET keyID = (SELECT keyTable.keyID FROM keyTable WHERE keyTable.key = mytable.key);
   

5. Remove key column from mytable

Answer 2

Hi crapbag, i just posted my workout for your problem. Just check this step by step:

CREATE TABLE `keytable` (
`keyID` INT( 11 ) NOT NULL auto_increment,
`key` VARCHAR( 100 ) NOT NULL,
`id` INT( 11 ) NOT NULL
) ;

insert into `keytable` (`key`,`id`) select `key`,`id` from mytable;

ALTER TABLE `mytable` CHANGE `key` `keyID` INT( 11 ) NOT NULL ;


update `mytable` set `keyID`= (select `keyID` from keytable where keytable.id=mytable.id)

ALTER TABLE `keytable` DROP `id` ;