Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've got legacy ecommerce database, which is awfully designed and I need to move all products from it to new solution. But I stuck with how product properties stored. So it basically has tens of tables named category_properties_<category_id> that have first column named product_id and then variable number of columns that contain properties_id.

I. e. there can be table category_properties_42 with records like


and table category_properties_43 with records like


Is it possible using MySQL to export this data in more reasonable format? Because variable number of columns and variable table names makes querying by product almost impossible. Thanks in advance.

share|improve this question
Are those tables really CSV files that you want converted to a MySQL table ??? – RolandoMySQLDBA Jan 13 '14 at 17:36
I have a MySQL database with all those tables. And I want to convert them one normalized MySQL table. – Shark Jan 13 '14 at 17:41

I would lean toward a single Category_Properties table with product_id, category_id, and optionally (if you need to retain the position of the property values in the original design) a sequence number of some sort. You are going to have to do some programming to convert this, but it can be done with some 'brute force' SQL if you are trying to avoid scripting something.

CREATE TABLE Category_Properties
( product_id
, category_id
, sequence_value  /* this is optional */
, property_id

INSERT INTO Category_Properties
  SELECT product_id, 43 AS cat, 1 AS seq, <first_property_col> AS pval FROM category_properties_43
  SELECT product_id, 43, 2, <second_property_col> FROM category_properties_43
  SELECT product_id, 43, 3, <third_property_col> FROM category_properties_43

-- From your example this would be <first_property_col> = 17, then 21, then 56.

You can do this provided you know the max number of property columns for each category, the outer query will eliminate the missing values for products which don't use all the property columns. You can see why you might want to script this, rather than changing it for each new category.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.