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
Source: Tips4all
First make sure your categories are unique. Make sure you don't have something like:
ReplyDeleteproductx 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;
Create a table category:
ReplyDeleteCREATE 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.
INSERT INTO new_table_name ("product", "category")
ReplyDeleteSELECT id, category
FROM product;
Then add a foriegn key constraint on id in new_table_name which references the original product table. After this you can alter table product drop column column_name.
You would start by normalizing your product table - creating 2 new tables:
ReplyDeleteCREATE TABLE categories
(
cat_id int AUTO_INCREMENT NOT NULL PRIMARY KEY,
cat_label varchar(40)
);
CREATE TABLE product_by_category
(
pbc_prod_id int REFERENCES products(id),
pbc_cat_id int REFERENCES categories(cat_id),
#indexes for quicker lookup...
INDEX pbc(pbc_prod_id,pbc_cat_id),
INDEX cbp(pbc_cat_id, pbc_prod_id)
);
INSERT INTO categories(cat_label) SELECT DISTINCT category FROM product;
INSERT INTO product_by_category(pbc_prod_id, pbc_cat_id)
SELECT id, (SELECT cat_id FROM categories WHERE categories.cat_label = product.category)
FROM products;
ALTER TABLE products DROP COLUMNN category; #do this after you are satisfied with contents of new tables.
Then you would have to create joins for getting products by category.