Normalizing a table, from one to the other

Question

Hi,

I'm trying to normalize a mysql database....

I currently have a table that contains 11 columns for "categories". The first column is a user_id and the other 10 are category_id_1 - category_id_10. Some rows may only contain a category_id up to category_id_1 and the rest might be NULL.

I then have a table that has 2 columns, user_id and category_id...

What is the best way to transfer all of the data into separate rows in table 2 without adding a row for columns that are NULL in table 1?

thanks!

Answer 1

One table for users:

users(id, name, username, etc)

One for categories:

categories(id, category_name)

One to link the two, including any extra information you might want on that join.

categories_users(user_id, category_id)

-- or with extra information --

categories_users(user_id, category_id, date_created, notes)

To transfer the data across to the link table would be a case of writing a series of SQL INSERT statements. There's probably some awesome way to do it in one go, but since there's only 11 categories, just copy-and-paste IMO:

INSERT INTO categories_users
SELECT user_id, 1
FROM old_categories
WHERE category_1 IS NOT NULL

Answer 2

You can create a single query to do all the work, it just takes a bit of copy and pasting, and adjusting the column name:

INSERT INTO table2
SELECT * FROM (
    SELECT user_id, category_id_1 AS category_id FROM table1
    UNION ALL
    SELECT user_id, category_id_2 FROM table1
    UNION ALL
    SELECT user_id, category_id_3 FROM table1
) AS T
WHERE category_id IS NOT NULL;

Since you only have to do this 10 times, and you can throw the code away when you are finished, I would think that this is the easiest way.