MySQL - splitting fields into separate tables

The algorithm is quite simple: each table is for storing individual entities. In your case, these entities are:

• prod_cat - categories
• prod_name - products
• prod_prop - properties

In one category there may be several products, one product may have several properties. The data is linked by keys; for this, a primary key is created in each table, and a foreign key is also created in the child ones (product and property tables):

Category table:

 id int, prod_cat text 

Product table:

 id int, id_cat int, prod_name text 

Property table:

 id int, id_prod int, prod_prop text 

Property1 and property2, if you start from your question, one entity. However, if we are talking about, for example, the expiration date and the bar code, then it makes sense to leave these data in the table of goods:

 id int, id_cat int, prod_name text, best_before date, bar_code text 

Rotate it using pure MySQL, you can. Create the tables you need, copy the source table (just in case) and create several fields for the primary keys from the optimized tables in it, and then copy the data you need into the optimized tables. ( The principle is this: you transferred data from the source table, in your new table, this generated data in the id (auto-increment) field, you copy these id into the original table. Repeat until all the data is spread over the desired tables. )

Pay attention to such requests:

• SELECT DISTINCT ... - removes duplicates from the selection,
• GROUP BY ... is also a powerful tool for combining identical data.
• INSERT INTO ... SELECT ... is a copy of the data from a table to a table.

Do not worry about the data: with such a transfer you will not lose them, because the whole process is copying from tables to tables. However, backup never hurts.

I disagree with the previous answer a little, there was a similar task to also split the base into structurally nested, but as it turned out, all text values ​​in the original table were driven in manually and who wrote as spelling errors, spaces and similar trifles. They came out of the situation in the following way - they formed a directory through the GROUP BY on the column, manually twisted the twins (it was necessary to break the brain in some cases) and then according to the proposed algorithm. Unfortunately, the system for which this was done lived only a year after our processing (which took 1 day of preparation and 2 weeks of manual editing of directories) due to the crisis of 2009, the client went bankrupt, and therefore it is a pity a bit :)