mysql separating tables

Question

i made a mistake and i have to split 1 table into 2. i have a product table and i need category table. when i started i only had 1 category per product but not (with new business requirement) i need the ability to put a product in multiple category.

i have a product tables that has a category in it.

here's the table:

product (id, name, category, price etc...)

now, how can i efficiently migrate this without make my site offline?

i have lamp on centos

Answer 1

First make sure your categories are unique. Make sure you don't have something like:

productx and produtx

Otherwise, when you will inserts bad categories.

You will have to do it in step:

1) Creating the table category

CREATE TABLE `category` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(40) NOT NULL DEFAULT 'General',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Create the intersection table since a product can be in multiple category and a category can have multiple product.

CREATE TABLE `product_category` (
  `product_id` int(10) unsigned NOT NULL,
  `category_id` int(10) unsigned NOT NULL,
 PRIMARY KEY product_category (`product_id`,`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2) inserts the categories into the table.

INSERT IGNORE INTO category SELECT DISTINCT category from product;

This will insert non duplicate categories into the category table with a unique id.

Now, you have to inserts these records in the

INSERT IGNORE INTO `product_category` SELECT `product`.`id` AS `product_id`, `category`.`id` AS `category_id` FROM `category` LEFT JOIN `product` ON (`category`.`name` = `product`.`category`);

3) Now you have to modify your code in order to use the right query: Example:

SELECT 
 /* your fields */
FROM
product
INNER JOIN product_category ON (product.id = product_category.product_id)
INNER JOIN category ON (category.id = product_category.category_id)
WHERE ...

4) Now, when you feel comfortable with your code changes, you can drop the unused column:

ALTER TABLE product DROP COLUMN category;

Answer 2

Create a table category:

CREATE TABLE category(id int primary key not null auto_increment, category varchar(40))

Then select unique categories from product table:

INSERT INTO category (category) SELECT DISTINCT category FROM product;

Then create a table for relations:

CREATE TABLE product_to_category (product_id int, category_id int);

If you want, you can use foreign keys and constraints.

Then you can migrate your existing relations:

INSERT INTO product_to_category SELECT product.id, category.id FROM product JOIN category on category.category=product.category;

After that, adjust your code to use the new structure and drop the category column from product table:

ALTER TABLE product DROP COLUMN category;

Hope this helps.