MySQL - normalize table

How can I take current table and normalize it so that it has one keyword_tag per user_id?

Thanks in advance for your feedback.


CREATE TABLE IF NOT EXISTS `keywords_tags` (
  `user_id` int(11) default NULL,
  `updated` int(1) default '0',
  `keyword_tags` text,
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `keywords_tags`
--

INSERT INTO `keywords_tags` (`user_id`, `updated`, keyword_tags`) VALUES
(10, 0, '2087'),
(11, 1, '2003 2006 2018 2099'),
(12, 1, '2102 2030'),
(13, 1, '2087 2085 2089 2099 2100 2444'),
(14, 0, NULL);
sabecsAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
sabecs,

This may not have the best performance in the world, but it is a method that can be used especially if this is a one time thing to fix design issue and then you will be inserting into the normalized table moving forward.

First, grab my util.numbers table script, unless you already have a numbers utility table -- in which case, substitute util.numbers for your table name.

Code to parse keyword_tags:
SELECT user_id, updated
     , SUBSTRING(`keyword_tags`, n, LOCATE(' ', CONCAT(`keyword_tags`, ' '), n)-n) `keyword_tag`
FROM `keywords_tags`
JOIN util.numbers 
   ON SUBSTRING(CONCAT(' ', `keyword_tags`), n, 2) REGEXP '^ [0-9]$'

Open in new window


Hope that helps!
0
 
sabecsAuthor Commented:
Thanks mwvisa1.
0
All Courses

From novice to tech pro — start learning today.