How do I normalize my table?

Question

I have a table that consists of 1440 columns in a MySQL database. The columns names are: id,name,email,phonenumber,and also datewithtime columns like 1'st january_2012_00:00, 1'st january_2012_00:30, 1'st january_2012_01:00, 1'st january_2012_01:00, 1'st january_2012_02:00,..., 1'st january_2012_24:00.

Can I maintain all of these columns in a single table or can I divide the single table into multiple tables and make an association between them? What is the best solution for me?

Answer 1

You should create a table containing (UserId, SomeDateColumnName, SomeValueColumnName)

Then, the value of each column in your table will go into a row in this new table. Ex:

instead of

Id  Name  Email  Phone  1stjanuary_2012_00:00  1stjanuary_2012_00:30 ...
1   A     A@A.A  123    Value 1                Value 2

You will have

Id  Name  Email  Phone
1   A     A@A.A  123

plus

UserId  DateColumn           ValueColumn
1       2012-01-01 00:00:00  Value 1
1       2012-01-01 00:30:00  Value 2
1       2012-01-01 01:00:00  Value 3
1       2012-01-01 01:30:00  Value 4
...
2       2012-01-01 00:00:00  Value 123
2       2012-01-01 00:30:00  Value 234
2       2012-01-01 01:00:00  Value 345
2       2012-01-01 01:30:00  Value 456
...