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) ?
2 Answers
- Create
keyTable Fill keys from
mytable:INSERT INTO keyTable (`key`) SELECT DISTINCT mytable.key FROM mytable;add
keyIDcolumn tomytableAssign keyIDs:
UPDATE mytable SET keyID = (SELECT keyTable.keyID FROM keyTable WHERE keyTable.key = mytable.key);Remove
keycolumn frommytable
2 Comments
Karthik
Good Solution. Regarding key are keyword so use for differentiation like this
key. So check my posting below for more information.
ceteras
this is the correct solution, imho. you have to use DISTINCT when extracting the keys and values from the original table, and you don't need the id column from it.
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` ;